Oracle CLOB不接受超过32767的数据

ngynwnxp  于 12个月前  发布在  Oracle
关注(0)|答案(2)|浏览(149)

我有一个.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

先知程序如下...

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;
oracle

2条答案

按热度按时间
41ik7eoe

41ik7eoe1#

查看SPPrmsLOB
这是旧的COM，对于.NET，看起来你必须将SPPrmsLOB=TRUE添加到连接字符串中。
或者尝试设置OleDbParameter.DbType（而不是OleDbParameter.OleDbType），为两者设置不同的值。
另一种解决方案可能是使用“Oracle Data Provider for .NET”（Oracle.DataAccess.Client或Oracle.ManagedDataAccess.Client）。Oracle Provider for OLE DB（OraOLEDB）现在有点“老派”-除非您需要独立于后端数据库。

赞(0）分享  回复(0）举报  12个月前
f45qwnt8

f45qwnt82#

最近在一个 legacy Visual Basic项目中遇到了类似的问题，
1.使用.NetFramework 4 VB应用程序
1.具有保存JSON数据的字符串，
1.使用 OracleClient（Imports System.Data.OracleClient）和
1.尝试将其作为CLOB IN参数传递给 *Oracle 19 c * 存储过程。
1.获取错误：ORA-01460：（
发现如果JSON小于约48 K大小，则一切正常，但较大的JSON会产生异常。

：）如何修复：
将调用SP的命令放在事务中。

示例（Visual Basic - .Net Framework 4）：

Using conn As new OracleConnection(connString)
conn.Open()
Using command as NewOracleCommand("SOMESCHEMA.SOMESP", conn)
    Dim tran As OracleTransaction = conn.BeginTransaction()
    Try
        command.Transaction = trancommand.CommandType = CommandType.StoredProcedure
        Dim spParameter As New OracleParameter("p_name", Oracle.Clob)            
        spParameter.Value = aBigJSONString
        spParameter.Direction = ParameterDirection.Input
        command.Parameters.Add(spParameter)
        command.ExecuteNonQuery()
        tran.Commit()
    Catch ex As Exception
        tran.Rollback()
        Throw
    End Try
End Using

End Using
感谢OnsideSilver在Stack Overflow - ORA-01460: unimplemented or unreasonable conversion (after conversion of a docx to pdf and uploading to db) VisualBasic (VB上解决了同样的问题并分享了解决方案

赞(0）分享  回复(0）举报  12个月前
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com