我有一個 .NET MVC 應用程式，它從 excel 檔案中讀取資料并轉換為 JSON 物件并作為 CLOB 引數傳遞給 oracle 程式。如果資料長度低于 32767 它作業正常但是當資料長度超過 32767 時它給我錯誤
ORA-01460: 請求的轉換未實作或不合理
如何解決問題？.NET 代碼如下
Function UploadData(UploadASN As UploadASN) As ActionResult
Try
If IsNothing(Session("UserId")) Or Session("UserId") = "" Then
Return RedirectToAction("Index", "Login")
End If
Dim PackingListdt As DataTable = New DataTable("PackingList")
Dim dcstorerkey = New DataColumn("storerkey", GetType(String))
Dim dcloadingport = New DataColumn("loadingport", GetType(String))
Dim dcdischargeport = New DataColumn("dischargeport", GetType(String))
Dim dcorigincountry = New DataColumn("origincountry", GetType(String))
Dim dcdestinationcountry = New DataColumn("destinationcountry", GetType(String))
Dim dccustomerpono = New DataColumn("customerpono", GetType(String))
Dim dcbolnumber = New DataColumn("bolnumber", GetType(String))
Dim dccontainerno = New DataColumn("containerno", GetType(String))
Dim dccustomeridno = New DataColumn("customeridno", GetType(String))
Dim dcitemcode = New DataColumn("itemcode", GetType(String))
Dim dccustomno = New DataColumn("customno", GetType(String))
Dim dcmodelno = New DataColumn("modelno", GetType(String))
Dim dcproductdesc = New DataColumn("productdesc", GetType(String))
Dim dctotalcartons = New DataColumn("totalcartons", GetType(String))
Dim dcqtypercarton = New DataColumn("qtypercarton", GetType(String))
Dim dctotalqty = New DataColumn("totalqty", GetType(String))
Dim dccartonnetweight = New DataColumn("cartonnetweight", GetType(String))
Dim dccartongrossweight = New DataColumn("cartongrossweight", GetType(String))
Dim dctotalnetweight = New DataColumn("totalnetweight", GetType(String))
Dim dctotalgrossweight = New DataColumn("totalgrossweight", GetType(String))
Dim dccartonlength = New DataColumn("cartonlength", GetType(String))
Dim dccartonwidth = New DataColumn("cartonwidth", GetType(String))
Dim dccartonheight = New DataColumn("cartonheight", GetType(String))
Dim dcshipmentvalue = New DataColumn("shipmentvalue", GetType(String))
Dim dccurrency = New DataColumn("currency", GetType(String))
Dim dcuserid = New DataColumn("userid", GetType(String))
PackingListdt.Columns.Add(dcstorerkey)
PackingListdt.Columns.Add(dcloadingport)
PackingListdt.Columns.Add(dcdischargeport)
PackingListdt.Columns.Add(dcorigincountry)
PackingListdt.Columns.Add(dcdestinationcountry)
PackingListdt.Columns.Add(dccustomerpono)
PackingListdt.Columns.Add(dcbolnumber)
PackingListdt.Columns.Add(dccontainerno)
PackingListdt.Columns.Add(dccustomeridno)
PackingListdt.Columns.Add(dcitemcode)
PackingListdt.Columns.Add(dccustomno)
PackingListdt.Columns.Add(dcmodelno)
PackingListdt.Columns.Add(dcproductdesc)
PackingListdt.Columns.Add(dctotalcartons)
PackingListdt.Columns.Add(dcqtypercarton)
PackingListdt.Columns.Add(dctotalqty)
PackingListdt.Columns.Add(dccartonnetweight)
PackingListdt.Columns.Add(dccartongrossweight)
PackingListdt.Columns.Add(dctotalnetweight)
PackingListdt.Columns.Add(dctotalgrossweight)
PackingListdt.Columns.Add(dccartonlength)
PackingListdt.Columns.Add(dccartonwidth)
PackingListdt.Columns.Add(dccartonheight)
PackingListdt.Columns.Add(dcshipmentvalue)
PackingListdt.Columns.Add(dccurrency)
PackingListdt.Columns.Add(dcuserid)
UploadASN.ExcelData = Session("ExcelData")
For Each row As DataRow In UploadASN.ExcelData.Rows
PackingListdt.Rows.Add(Session("Storerkey"), row.Item(0), row.Item(1), row.Item(2), row.Item(3),
row.Item(4), row.Item(5), row.Item(6), row.Item(7), row.Item(8), row.Item(9),
row.Item(10), row.Item(11), row.Item(12), row.Item(13), row.Item(14),
row.Item(15), row.Item(16), row.Item(17), row.Item(18), row.Item(19),
row.Item(20), row.Item(21), row.Item(22), row.Item(23), Session("UserId"))
Next
Dim PackingListdata As String = JsonConvert.SerializeObject(PackingListdt)
InsertPackingList(PackingListdata)
Session("UploadASNSuccessFlag") = "Y"
Session("UploadASNSuccessMsg") = "Packing List uploaded successfully!"
Session("ExcelData") = Nothing
Return RedirectToAction("Index", "UploadASN")
Catch ex As Exception
Session("SystemError") = ex.Message
Return RedirectToAction("Index", "ErrorHandler")
End Try
Private Sub InsertPackingList(PackingListData)
Dim DBCmd As OleDbCommand
MsgBox(Len(PackingListData).ToString)
Globals.OpenDBConnection()
DBCmd = Globals.oraConnection.CreateCommand
DBCmd.Parameters.Add("PACKING_LIST_DATA", OleDbType.LongVarChar, 600000).Direction = ParameterDirection.Input
DBCmd.Parameters("PACKING_LIST_DATA").Value = PackingListData
DBCmd.CommandText = "WMSPROCESS.INSERT_PACKING_LIST"
DBCmd.CommandType = CommandType.StoredProcedure
DBCmd.ExecuteNonQuery()
Globals.CloseDBConnection()
End Sub
Oracle程式如下...
PROCEDURE INSERT_PACKING_LIST (PACKING_LIST_DATA IN CLOB) IS
L_ARRAY JSON_ARRAY_T;
L_OBJECT JSON_OBJECT_T;
L_KEYS JSON_KEY_LIST;
P_DATA CLOB;-- VARCHAR2;--(32767);
P_PLM_NUMBER NUMBER(10);
P_STORERKEY VARCHAR2(15);
P_CONTAINER_NO VARCHAR2(30);
P_BOLNUMBER VARCHAR2(30);
P_LOADINGPORT VARCHAR2(100);
P_DISCHARGEPORT VARCHAR2(100);
P_ORIGINCOUNTRY VARCHAR2(100);
P_DESTINATIONCOUNTRY VARCHAR2(100);
P_CUSTOMER_PONUMBER VARCHAR2(30);
P_PLD_NUMBER NUMBER(10);
P_CUSTOMERIDNO VARCHAR2(30);
P_ITEMCODE VARCHAR2(30);
P_CUSTOMNO VARCHAR2(30);
P_MODELNO VARCHAR2(30);
P_PRODUCTDESC VARCHAR2(100);
P_TOTALCARTONS NUMBER;
P_QTYPERCARTON NUMBER;
P_TOTALQTY NUMBER;
P_CARTONNETWEIGHT NUMBER(28,6);
P_CARTONGROSSWEIGHT NUMBER(28,6);
P_TOTALNETWEIGHT NUMBER(28,6);
P_TOTALGROSSWEIGHT NUMBER(28,6);
P_CARTONLENGTH NUMBER(28,6);
P_CARTONWIDTH NUMBER(28,6);
P_CARTONHEIGHT NUMBER(28,6);
P_SHIPMENTVALUE NUMBER(28,6);
P_CURRENCY VARCHAR2(20);
P_USER_ID VARCHAR2(20);
P_OLD_PLM_NUMBER NUMBER(10);
P_OLD_PLD_NUMBER NUMBER(10);
P_CM_ID NUMBER(10);
CURSOR C1 IS SELECT PLM_NUMBER
FROM PACKING_LIST_MASTER
WHERE PLM_STORERKEY=P_STORERKEY
AND PLM_CONTAINER_NO=P_CONTAINER_NO
AND PLM_BOLNUMBER=P_BOLNUMBER
AND PLM_CUSTOMER_PONO=P_CUSTOMER_PONUMBER;
CURSOR C2 IS SELECT NVL(MAX(PLM_NUMBER),0) 1
FROM PACKING_LIST_MASTER;
CURSOR C3 IS SELECT NVL(MAX(PLD_NUMBER),0) 1
FROM PACKING_LIST_DETAIL
WHERE PLD_PLM_NUMBER=P_PLM_NUMBER;
CURSOR C4 IS SELECT PLD_NUMBER
FROM PACKING_LIST_DETAIL
WHERE PLD_PLM_NUMBER=P_PLM_NUMBER
AND PLD_CUSTOMER_ID_NO=P_CUSTOMERIDNO
AND PLD_ITEM_CODE=P_ITEMCODE;
CURSOR C5 IS SELECT NVL(MAX(CM_ID),0) 1
FROM CARTON_MASTER;
BEGIN
P_DATA:=CONVERT(PACKING_LIST_DATA,'US7ASCII','AR8MSWIN1256');
--DBMS_OUTPUT.PUT_LINE('a');
L_ARRAY := JSON_ARRAY_T (P_DATA);
FOR i IN 0 .. L_ARRAY.GET_SIZE - 1
LOOP
L_OBJECT := TREAT (L_ARRAY.GET (i) AS JSON_OBJECT_T);
L_KEYS := L_OBJECT.GET_KEYS;
FOR j IN 1 .. L_KEYS.COUNT
LOOP
IF L_KEYS(j) ='storerkey' THEN
P_STORERKEY :=L_OBJECT.GET_STRING(L_KEYS(j));
ELSIF L_KEYS(j) ='customerpono' THEN
IF L_OBJECT.GET_STRING(L_KEYS(j)) IS NOT NULL THEN
P_CUSTOMER_PONUMBER :=REPLACE(L_OBJECT.GET_STRING(L_KEYS(j)),' ','');
END IF;
ELSIF L_KEYS(j) ='containerno' THEN
IF L_OBJECT.GET_STRING(L_KEYS(j)) IS NOT NULL THEN
P_CONTAINER_NO :=REPLACE(L_OBJECT.GET_STRING(L_KEYS(j)),' ','');
END IF;
ELSIF L_KEYS(j) ='bolnumber' THEN
IF L_OBJECT.GET_STRING(L_KEYS(j)) IS NOT NULL THEN
P_BOLNUMBER :=REPLACE(L_OBJECT.GET_STRING(L_KEYS(j)),' ','');
END IF;
ELSIF L_KEYS(j) ='loadingport' THEN
IF L_OBJECT.GET_STRING(L_KEYS(j)) IS NOT NULL THEN
P_LOADINGPORT :=L_OBJECT.GET_STRING(L_KEYS(j));
END IF;
ELSIF L_KEYS(j) ='dischargeport' THEN
IF L_OBJECT.GET_STRING(L_KEYS(j)) IS NOT NULL THEN
P_DISCHARGEPORT :=L_OBJECT.GET_STRING(L_KEYS(j));
END IF;
ELSIF L_KEYS(j) ='origincountry' THEN
IF L_OBJECT.GET_STRING(L_KEYS(j)) IS NOT NULL THEN
P_ORIGINCOUNTRY :=L_OBJECT.GET_STRING(L_KEYS(j));
END IF;
ELSIF L_KEYS(j) ='destinationcountry' THEN
IF L_OBJECT.GET_STRING(L_KEYS(j)) IS NOT NULL THEN
P_DESTINATIONCOUNTRY :=L_OBJECT.GET_STRING(L_KEYS(j));
END IF;
ELSIF L_KEYS(j) ='customeridno' THEN
P_CUSTOMERIDNO :=REPLACE(L_OBJECT.GET_STRING(L_KEYS(j)),' ','');
ELSIF L_KEYS(j) ='itemcode' THEN
P_ITEMCODE :=REPLACE(L_OBJECT.GET_STRING(L_KEYS(j)),' ','');
ELSIF L_KEYS(j) ='customno' THEN
P_CUSTOMNO :=REPLACE(L_OBJECT.GET_STRING(L_KEYS(j)),' ','');
ELSIF L_KEYS(j) ='modelno' THEN
P_MODELNO :=L_OBJECT.GET_STRING(L_KEYS(j));
ELSIF L_KEYS(j) ='productdesc' THEN
P_PRODUCTDESC :=L_OBJECT.GET_STRING(L_KEYS(j));
ELSIF L_KEYS(j) ='totalcartons' THEN
IF NVL(L_OBJECT.GET_STRING(L_KEYS(j)),0)>0 THEN
P_TOTALCARTONS :=L_OBJECT.GET_STRING(L_KEYS(j));
ELSE
P_TOTALCARTONS :=0;
END IF;
ELSIF L_KEYS(j) ='qtypercarton' THEN
P_QTYPERCARTON :=L_OBJECT.GET_STRING(L_KEYS(j));
ELSIF L_KEYS(j) ='totalqty' THEN
IF NVL(L_OBJECT.GET_STRING(L_KEYS(j)),0)>0 THEN
P_TOTALQTY :=L_OBJECT.GET_STRING(L_KEYS(j));
ELSE
P_TOTALQTY :=0;
END IF;
ELSIF L_KEYS(j) ='cartonnetweight' THEN
P_CARTONNETWEIGHT :=L_OBJECT.GET_STRING(L_KEYS(j));
ELSIF L_KEYS(j) ='cartongrossweight' THEN
P_CARTONGROSSWEIGHT :=L_OBJECT.GET_STRING(L_KEYS(j));
ELSIF L_KEYS(j) ='totalnetweight' THEN
P_TOTALNETWEIGHT :=L_OBJECT.GET_STRING(L_KEYS(j));
ELSIF L_KEYS(j) ='totalgrossweight' THEN
P_TOTALGROSSWEIGHT :=L_OBJECT.GET_STRING(L_KEYS(j));
ELSIF L_KEYS(j) ='cartonlength' THEN
P_CARTONLENGTH :=L_OBJECT.GET_STRING(L_KEYS(j));
ELSIF L_KEYS(j) ='cartonwidth' THEN
P_CARTONWIDTH :=L_OBJECT.GET_STRING(L_KEYS(j));
ELSIF L_KEYS(j) ='cartonheight' THEN
P_CARTONHEIGHT :=L_OBJECT.GET_STRING(L_KEYS(j));
ELSIF L_KEYS(j) ='shipmentvalue' THEN
P_SHIPMENTVALUE :=L_OBJECT.GET_STRING(L_KEYS(j));
ELSIF L_KEYS(j) ='currency' THEN
P_CURRENCY :=L_OBJECT.GET_STRING(L_KEYS(j));
ELSIF L_KEYS(j) ='userid' THEN
P_USER_ID :=L_OBJECT.GET_STRING(L_KEYS(j));
END IF;
END LOOP;
OPEN C1;
FETCH C1 INTO P_OLD_PLM_NUMBER;
IF C1%NOTFOUND THEN
OPEN C2;
FETCH C2 INTO P_PLM_NUMBER;
CLOSE C2;
INSERT INTO PACKING_LIST_MASTER
(PLM_NUMBER,PLM_STORERKEY,PLM_CONTAINER_NO,PLM_BOLNUMBER,PLM_LOADING_PORT,PLM_DISCHARGE_PORT,PLM_ORIGIN_COUNTRY,
PLM_DESTINATION_COUNTRY,PLM_STATUS,PLM_CUSTOM_NO,PLM_CUSTOMER_PONO,PLM_ADDDATE,PLM_ADDWHO,
PLM_EDITDATE,PLM_EDITWHO,PLM_MBOLNUMBER)
VALUES (P_PLM_NUMBER,P_STORERKEY,P_CONTAINER_NO,P_BOLNUMBER,P_LOADINGPORT,P_DISCHARGEPORT,P_ORIGINCOUNTRY,
P_DESTINATIONCOUNTRY,0,P_CUSTOMNO,P_CUSTOMER_PONUMBER,SYSDATE,P_USER_ID,SYSDATE,P_USER_ID,NULL);
IF NVL(P_TOTALCARTONS,0)>0 THEN
OPEN C5;
FETCH C5 INTO P_CM_ID;
CLOSE C5;
INSERT INTO CARTON_MASTER
(CM_ID,CM_TOTAL_CARTON,CM_RECVD_CARTON,CM_TOTAL_QTY,CM_ADDDATE,CM_ADDWHO)
VALUES (P_CM_ID,P_TOTALCARTONS,0,P_TOTALQTY,SYSDATE,P_USER_ID);
END IF;
OPEN C3;
FETCH C3 INTO P_PLD_NUMBER;
CLOSE C3;
INSERT INTO PACKING_LIST_DETAIL
(PLD_PLM_NUMBER,PLD_NUMBER,PLD_CUSTOMER_ID_NO,PLD_ITEM_CODE,PLD_MODEL_NO,PLD_COMMODITY,
PLD_TOTAL_CARTON,PLD_QTY_PER_CARTON,PLD_TOTAL_QTY,PLD_CARTON_NET_WEIGHTKGS,PLD_CARTON_GROSS_WEIGHTKGS,
PLD_TOTAL_NET_WEIGHTKGS,PLD_TOTAL_GROSS_WEIGHTKGS,PLD_CARTON_LENGTH_CM,PLD_CARTON_WIDTH_CM,
PLD_CARTON_HEIGHT_CM,PLD_SHIPMENT_VALUE,PLD_SHIPMENT_CURRENCY,PLD_STATUS,PLD_RECVD_CARTONS,
PLD_RECVD_LOC,PLD_ADDDATE,PLD_ADDWHO,PLD_EDITDATE,PLD_EDITWHO,PLD_CM_ID)
VALUES (P_PLM_NUMBER,P_PLD_NUMBER,P_CUSTOMERIDNO,P_ITEMCODE,P_MODELNO,P_PRODUCTDESC,P_TOTALCARTONS,
P_QTYPERCARTON,P_TOTALQTY,P_CARTONNETWEIGHT,P_CARTONGROSSWEIGHT,P_TOTALNETWEIGHT,P_TOTALGROSSWEIGHT,
P_CARTONLENGTH,P_CARTONWIDTH,P_CARTONHEIGHT,P_SHIPMENTVALUE,P_CURRENCY,0,0,NULL,SYSDATE,P_USER_ID,
SYSDATE,P_USER_ID,P_CM_ID);
ELSIF C1%FOUND THEN
P_PLM_NUMBER :=P_OLD_PLM_NUMBER;
OPEN C4;
FETCH C4 INTO P_OLD_PLD_NUMBER;
IF C4%NOTFOUND THEN
OPEN C3;
FETCH C3 INTO P_PLD_NUMBER;
CLOSE C3;
INSERT INTO PACKING_LIST_DETAIL
(PLD_PLM_NUMBER,PLD_NUMBER,PLD_CUSTOMER_ID_NO,PLD_ITEM_CODE,PLD_MODEL_NO,PLD_COMMODITY,
PLD_TOTAL_CARTON,PLD_QTY_PER_CARTON,PLD_TOTAL_QTY,PLD_CARTON_NET_WEIGHTKGS,PLD_CARTON_GROSS_WEIGHTKGS,
PLD_TOTAL_NET_WEIGHTKGS,PLD_TOTAL_GROSS_WEIGHTKGS,PLD_CARTON_LENGTH_CM,PLD_CARTON_WIDTH_CM,
PLD_CARTON_HEIGHT_CM,PLD_SHIPMENT_VALUE,PLD_SHIPMENT_CURRENCY,PLD_STATUS,PLD_RECVD_CARTONS,
PLD_RECVD_LOC,PLD_ADDDATE,PLD_ADDWHO,PLD_EDITDATE,PLD_EDITWHO)
VALUES (P_PLM_NUMBER,P_PLD_NUMBER,P_CUSTOMERIDNO,P_ITEMCODE,P_MODELNO,P_PRODUCTDESC,P_TOTALCARTONS,
P_QTYPERCARTON,P_TOTALQTY,P_CARTONNETWEIGHT,P_CARTONGROSSWEIGHT,P_TOTALNETWEIGHT,P_TOTALGROSSWEIGHT,
P_CARTONLENGTH,P_CARTONWIDTH,P_CARTONHEIGHT,P_SHIPMENTVALUE,P_CURRENCY,0,0,NULL,SYSDATE,P_USER_ID,SYSDATE,
P_USER_ID);
END IF;
CLOSE C4;
END IF;
CLOSE C1;
END LOOP;
COMMIT;
END;
uj5u.com熱心網友回復：
看看SPPrmsLOB
這是舊的 COM，對于 .NET，看起來您必須添加SPPrmsLOB=TRUE到連接字串中。
或者嘗試設定OleDbParameter.DbType（不是OleDbParameter.OleDbType），為兩者使用不同的值。
另一種解決方案可能是使用“Oracle Data Provider for .NET”（Oracle.DataAccess.Client或Oracle.ManagedDataAccess.Client）。如今，OLE DB 的 Oracle Provider (OraOLEDB) 有點“老派”——除非您需要獨立于后端資料庫。
uj5u.com熱心網友回復：
我解決了這個問題，非常感謝大家。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/407689.html
標籤：
上一篇：使用多個樞軸將行轉置為列