XML file to oracle via Java

We have written a command line program that receives orders in a number of different formats by email and parses them to a common xml structure. We want to pass this xml document to a PL/SQL procedure that breaks it apart and inserts the elements into different tables. Does anyone know of a straightforward way to pass an xml document to a pl/sql procedure without actually inserting it into a table first? The documents are bigger than a varchar2 so they cannot be passed that way. I've seen references to using a CLOB but they seem (and I could be wrong) to need it saved to a table first.

Any responses are greatly appreciated!!

Carol

[658 byte] By [serroul] at [2007-9-30 20:30:09]

«« getDay()?
»» How does compiler determine 'missing return statement'

# 1

Compress the XML stream using GZIP first and then put the byte array in a CLOB. Clients who query to get it just have to uncompress it when they do a query.%

duffymo at 2007-7-7 1:14:18 >

top of Java-index,Database Connectivity,Java Database Connectivity (JDBC)...

# 2

You could send the xml as an blob or clob paramenter with that you dont have to save it in the database, if you want an example let me know at thekiwie at gmail dot com

thekiwie at 2007-7-7 1:14:18 >

# 3

This is the code to pass a clob as a parameter to a stored procedure in java

The cs object y the callabelStatement used to call de procedure

byte[] myByte is the bytearray with the xml you want to pass to the database.

Hope it helps

CLOB tempClob=CLOB.createTemporary(this.oConn,true,CLOB.DURATION_CALL);

tempClob.open(CLOB.MODE_READWRITE);

OutputStream outClob =tempClob.getAsciiOutputStream();

byte[] myByte = null;//(the byte stremof your xml)

outClob.write(myByte);

outClob.flush();

outClob.close();

cs.setObject(index,tempClob);

thekiwie at 2007-7-7 1:14:18 >

# 4

Did you think about using XSL to transform XML into SQL statement, then execute SQL.

masuda1967 at 2007-7-7 1:14:18 >

# 5

Thanks so much!! :)It's taken me a while to make this work as there were problems with my pl/sql procedure but it's working great now, even when the xml file is more than 4000 characters. Your dollars are on the way ;)Carol

serroul at 2007-7-7 1:14:18 >

Java Database Connectivity (JDBC)

Java programming resources: FAQs, tutorials, compiler and browser download sites, documentation, books lists, IDEs, etc.

XML file to oracle via Java

Database Connectivity New Topic

Database Connectivity Hot Topic

Database Connectivity

All Classified