SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

best way to do a bulk insert from c# to Oracle?

User_RNT7ZFeb 21 2019 — edited Feb 21 2019

As per customer requirement, I need to insert min500 to maximum 10,000 record into oracle database from .net c# application. I'm trying passing it as a xml data (more than 4000 characters) for a bulk insert i get an error (see below). I understand that xmltype doesn't hold more than 4000 characters if i pass it as a string. So I use CLOB datatype to hold 2gb records. but again I can't insert more than 4000 characters into clob

Error report -

ORA-06550: line 4, column 10:

PLS-00172: string literal too long

06550. 00000 - "line %s, column %s:\n%s"

*Cause: Usually a PL/SQL compilation error.

*Action:

My Stored Procedure

PROCEDURE UDPATE_NEIGHBOUR_BY_EXTENT(P_XML IN CLOB) AS

v_today date :=SYSDATE;
BEGIN
FOR item IN (
SELECT xt.* FROM XMLTABLE('/neighbours/row'
     PASSING XMLTYPE(P_XML)
COLUMNS
    PA_REFERENCE NUMBER(12) PATH 'reference',
    ADDRESSABLE_OBJECT_CODE VARCHAR2(12) PATH 'addressableobjectcode',
    CREATE_BY VARCHAR2(25) PATH 'createdby',
    ADDRESS_TEXT VARCHAR2(2000) PATH 'addresstext',
    NON_POSTAL_ADDRESS CHAR(1) PATH 'nonpostaladdress'
)xt)
LOOP
               INSERT INTO PA_NEIGHBOUR(PA_REFERENCE, ADDRESSABLE_OBJECT_CODE, EFFECT_DATE, CREATE_DATE, CREATE_BY, ADDRESS_TEXT, NON_POSTAL_ADDRESS)
                VALUES( item.PA_REFERENCE, item.ADDRESSABLE_OBJECT_CODE, v_today, v_today, item.CREATE_BY, item.ADDRESS_TEXT, item.NON_POSTAL_ADDRESS);
END LOOP;
END UDPATE_NEIGHBOUR_BY_EXTENT;

so, What’s the best way to do bulk insert in oracle either using xml or any other different technique?

Added on Feb 21 2019

3 comments

4,923 views