Oracle clob is not accepting data more than 32767

Question

I have a .NET MVC application which is reading the data from excel file and converting to JSON object and passing to oracle procedure as CLOB parameter. if the data length is below 32767 it is working fine but when the data length is more than 32767 then it is giving me error

ORA-01460: unimplemented or unreasonable conversion requested

How to resolve the issue? .NET code is as follows

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 is as follows...

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;

Answer 1

Recently had similar issue working on a legacy Visual Basic project,

1. Working with .NetFramework 4 VB application
2. Having string holding JSON data and
3. Using OracleClient (Imports System.Data.OracleClient) and
4. trying to pass it as an CLOB IN parameter to Oracle 19c Stored Procedure.
5. Getting error: ORA-01460 :(

Found that everything worked fine if JSON was less of about 48K size, but bigger JSONs gave the Exception.

:) How to FIX:

Place command invoking the SP within a transaction.

Example (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

Credit (And thanks) to OnsideSilver which solved same issue and share solution on Stack Overflow - ORA-01460: unimplemented or unreasonable conversion (after conversion of a docx to pdf and uploading to db) VisualBasic (VB

Answer 2

Have a look at SPPrmsLOB

This is old COM, for .NET it looks like you have to add SPPrmsLOB=TRUE to your connection string.

Or try to set OleDbParameter.DbType (not OleDbParameter.OleDbType), play around with different values for both.

Another solution could be to use the "Oracle Data Provider for .NET" (Oracle.DataAccess.Client or Oracle.ManagedDataAccess.Client). The Oracle Provider for OLE DB (OraOLEDB) is a bit "old-school" nowadays - unless you need to be independently from backend database.