Tags: command, common, connectivity, database, email, file, formats, java, jdbc, line, number, oracle, orders, parses, program, receives, structure, via, written, xml

XML file to oracle via Java

On Java Studio » Java Database Connectivity (JDBC)

2,044 words with 5 Comments; publish: Sat, 22 Sep 2007 20:30:00 GMT; (15063.00, « »)

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

All Comments

Leave a comment...

  • 5 Comments
    • 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.%
      #1; Sat, 07 Jul 2007 01:14:00 GMT
    • 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
      #2; Sat, 07 Jul 2007 01:14:00 GMT

    • 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);

      #3; Sat, 07 Jul 2007 01:14:00 GMT
    • Did you think about using XSL to transform XML into SQL statement, then execute SQL.
      #4; Sat, 07 Jul 2007 01:14:00 GMT
    • 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
      #5; Sat, 07 Jul 2007 01:14:00 GMT