Bulk insert from C# into Oracle Database

[BJoy]

Hi,
What is the best way to do bulk insert from c# UI into Oracle database. I tried it passing XML string into oracle (see the below code) but xmltype doesn't accept more than 4000 characters.

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?

 

[johnherman]

There is (or at least was) a product called Oracle Pro*C, which is used to interface the C programming interface and library with Oracle. Does it work for C#? <dunno> Does the product still exist? <dunno> Is there a way to do it for FREE (since Pro*C costs money)? <dunno> Maybe check the Oracle sites?

Oracle community (where their mission is not necessarily to make dollars for Oracle Corporation)

https://community.oracle.com/thread/644369

Oracle documentation

https://docs.oracle.com/en/

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[johnherman]

Another option might be to make a call to Oracle sql*plus and track the return code, or manually supply your own status from the sql*plus procedure. Unfortunatly, sql*plus also costs money, albeit probably not as much as the Pro*C precompiler.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[Beilstwh]

sql*plus comes with the database as one of it's binaries. If you own oracle, you own sql*plus

Bill
Lead Application Developer
New York State, USA

 

[karluk]

Sql*plus is completely free, regardless of whether you have an Oracle database license or not. That's because it's bundled in Oracle client software. For example, it is included in Oracle Instant Client, which is a free download.

Of course, sql*plus isn't useful unless you have an Oracle database to connect to, so someone somewhere is paying for a database license. But don't avoid sql*plus under the mistaken impression that it costs money. It never has, and in all probability never will.

https://www.oracle.com/technetwork/database/database-technologies/instant-client/overview/index.html