Loading XML file (with data from various tables ) into Database USING PLSQL
A reader, October 12, 2004 - 11:26 am UTC
Tom,
The link you provided me says -
Choose the right XML parsing technique for your JAVA APPLICATIONS
We do not have java enviromment here and need to do this using PLSQL. Is it not possible / recommended to do it using PL/SQL?
Thanks!
October 12, 2004 - 11:31 am UTC
there are dom and sax parsers in plsql as well. it was just a reference to "how you can do this -- there are two ways -- you decide based on these thoughts..."
java is just a language remember. so is plsql.
Loading XML file (with data from various tables ) into Database
A reader, October 12, 2004 - 11:50 am UTC
Can you give any links/ examples of how to use plsql parsers.
Is it not possiblem to use new PLSQL packages like DBMS_XMLSAVE to aviod explicit parsing at programing level.
Are you suggesting, parsing to have my file broken into separate files and then use dbms_XMLSAVE to insert it into tables.
Can you please explain what is DBMS_XMLSAVE.SETXSLT for?
The documentation says -
Registers a XSL transform to be applied to the XML to be saved.
After registering when is it used? how to use XSLT to apply to XML? Example of use of setxslt will be really a great help.
Thanks for your prompt response.
October 12, 2004 - 1:05 pm UTC
</code>
http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96621/toc.htm <code>
yes, you can use dbms_xlmsave and the like to skip the "Do it yourself" step -- but you have to use object types -- not just any set of arbitrary tables.
you can transform "foreign" XML into XML that is suitable for you -- and have us save it to tables (object types) we've created. You setup a schema to support XML documents of type "B", you were given a type "A" document instance, you use XSL to convert "A" into "B" so you can save it.
Loading XML file (with data from various tables ) into Database
A reader, October 12, 2004 - 3:34 pm UTC
Thanks Tom for your reply.
But I feel this is very general answer.
I was hoping that you will give some exmaple of specific use. The link that you have given is good, But I have been going through it for last few days and have not found any exmaple of use of DBMS_XMLSAVE.SETXSLT
I was hoping that you might help me with it.
Thanks again for your time!
October 12, 2004 - 3:55 pm UTC
you have to
a) parse your sql (gave you the docs on that clearly)
b) to use the parsed data as inputs to your SQL (that is straightforward)
you cannot use that api you keep talking about -- so why would I even bother?!??
I not understanding that at all.
Loading XML file (with data from various tables ) into Database
A reader, October 13, 2004 - 3:45 pm UTC
Tom,
I created a procedure to parse the document and transaform it using xslprocessor.processXSL with help of examples on the link provided by you.
The program did trasform my document as per XSLT that I provided. But this worked only till the size of file was small.
When I ran the same program with file of size 5MB (this is size of file that I need to process) the procedure failed with --
The following error has occurred:
ORA-29554: unhandled Java out of memory condition
Is this the limitation of PLSQL parser?
This is what my procedure look like --
CREATE OR REPLACE procedure break_XML_XSL(dir varchar2, xmlfile varchar2,
xslfile varchar2, resfile varchar2,
errfile varchar2) is
xpar xmlparser.PARSER;
xmldoc xmldom.DOMdocument;
xsldoc xmldom.domdocument;
ss xslprocessor.Stylesheet;
proc xslprocessor.Processor;
docfrag xmldom.DOMDocumentFragment;
docfragnode xmldom.DOMNode;
begin
xpar := xmlparser.newParser;
--xmlparser.setValidationMode(xpar, FALSE);
--xmlparser.setBaseDir(xpar, dir);
xmlparser.PARSE(xpar, dir || '/' || xmlfile);
xmldoc :=xmlparser.GETDOCUMENT(xpar);
xmlparser.PARSE(xpar, dir || '/' || xslfile);
xsldoc :=xmlparser.GETDOCUMENT(xpar);
ss := xslprocessor.newStylesheet(xsldoc, dir || '/' || xslfile);
proc := xslprocessor.newProcessor;
docfrag := xslprocessor.processXSL(proc, ss, xmldoc);
docfragnode := xmldom.makeNode(docfrag);
dbms_output.put_line('Writing transformed document');
xmldom.writeToFile(docfragnode, dir || '/' || resfile);
exception
when xmldom.INDEX_SIZE_ERR then
raise_application_error(-20120, 'Index Size error');
when xmldom.DOMSTRING_SIZE_ERR then
raise_application_error(-20120, 'String Size error');
when xmldom.HIERARCHY_REQUEST_ERR then
raise_application_error(-20120, 'Hierarchy request error');
when xmldom.WRONG_DOCUMENT_ERR then
raise_application_error(-20120, 'Wrong doc error');
when xmldom.INVALID_CHARACTER_ERR then
raise_application_error(-20120, 'Invalid Char error');
when xmldom.NO_DATA_ALLOWED_ERR then
raise_application_error(-20120, 'Nod data allowed error');
when xmldom.NO_MODIFICATION_ALLOWED_ERR then
raise_application_error(-20120, 'No mod allowed error');
when xmldom.NOT_FOUND_ERR then
raise_application_error(-20120, 'Not found error');
when xmldom.NOT_SUPPORTED_ERR then
raise_application_error(-20120, 'Not supported error');
when xmldom.INUSE_ATTRIBUTE_ERR then
raise_application_error(-20120, 'In use attr error');
when others then
raise_application_error(-20120, 'error error');
end;
/
Please help!
October 13, 2004 - 5:41 pm UTC
This is Sean Dillon, Tom's XML technologist...
Not to an answer a question with a question (or three) but why are you transforming a 5MB XML file with a stylesheet when Tom provided links to examples that would let you parse the XML document ONCE and insert rows into tables based on the contents of your XML?
Let me comment on some of the conversation.
1. DBMS_XMLSAVE is a great technology, because it takes an XML document in a canonical format (see below for an example) and loads it into a table automagically for you. The rub is it MUST be in a particular format. This canonical format looks like so....
<ROWSET>
<ROW>
<EMPNO>122</EMPNO>
<ENAME>TKYTE</ENAME>
<SAL>X</SAL>
</ROW>
<ROW>
<EMPNO>123</EMPNO>
<ENAME>SDILLON</ENAME>
<SAL>X-Y</SAL>
</ROW>
</ROWSET>
If your XML doesn't look like this, DBMS_XMLSAVE won't work. As an enhancement to the PL/SQL package DBMS_XMLSAVE, the programmers added SETXSLT. In this routine, you provide a stylesheet that would take YOUR XML format (whatever it might be) and convert it to the canonical format. Not any old stylesheet, it must be one of your creation that once again, does all the work to take your XML format and convert it to something that the DBMS_XMLSAVE package can understand. Once that's done, the package can insert the rows (or update, delete, whatever) into ONE and ONLY ONE table at a time. If your XML has multiple tables' worth of data, you would have to call DBMS_XMLSAVE multiple times, once for each table and table operation you wanted to perform. This means you would also have to have a different XSL stylesheet for each and every table you needed to load data into.
The reason Tom pointed you to an XML parser was so that you could have efficient code. If you parse the XML document yourself, you can write your own insert/update/delete statements based on the contents of the XML document. This is going to be much faster not only to write the code, but it's going to run a lot faster once it's done. DBMS_XMLSAVE is the looooooooooong way around the mountain for what you're trying to do. Use the parser.
Now, since your documents are large, and you're using the DOM parser, you're hitting a memory problem. There's most likely a memory limit for the size of documents you can parse in the DOM parser, but (1) it's going to be based on the amount of memory you can dedicate to your Java pool in the SGA, and (2) for 5mb files you probably wouldn't want to use DOM anyway. You need to use SAX. The SAX parser was designed specifically for larger sized XML documents. The only issue you have to use SAX, however, is that there's no native PL/SQL SAX parser. The only way to use one from PL/SQL is to write your SAX parsing routines in Java and then call them from PL/SQL (which if you don't have a JVM in your database, is out of the question for you).
You might consider using Oracle Application Server or a Java process outside the database to facilitate the SAX parsing. DOM is going to slow you down terribly no matter how efficient your code is. Give this SAX thing a chance.
For code samples on how to use SAX, simply go to www.google.com and search for "SAX Simple API for XML". You'll write the code unbeknownst of the database, and then perform your inserts into the database through JDBC.
It's complex, but you have a pretty complex requirement. Hope that helps.
A reader, October 14, 2004 - 9:24 am UTC
Sean,
Thanks for a very good resposnse. Now I have many things cleared up.
Thank you very much!
Transx Utility - is is alternative solution for this type of situation?
A reader, April 21, 2005 - 5:06 pm UTC
Can TransX Utility be useful in this kind of scenario.
I was surprise not to find single hit on this site for TransX Utility!!
Thanks!
April 22, 2005 - 10:15 am UTC
not a surprise to me, never heard of it.
A reader, April 22, 2005 - 11:26 am UTC
April 22, 2005 - 12:23 pm UTC
Neat, see -- every day.... something new... learned
thanks.
Java stored procedure
A reader, May 23, 2005 - 10:24 am UTC
Hello Tom / Sean,
I have similar requirement of loading XML file (huge one from various tables) into Oracle DB.
As per Seans suggestion in this thread, I have written SAX parser in Jave to load the file to DB.
The program is working fine although it takes around 2 hours to laod file of size 8MB.
I was wondering if it is worth making in Java stored procedure so that the time required for JDBC calls can be reduced to make the program faster.
I am not able to decide out if this is right cadidate for making it Java stored procedure. Having so many non-Static methods is it at all possible to make Java stored procedure for this class? We are using Oracle 9i.
Here is my JAVA SAX parser --
import org.xml.sax.helpers.DefaultHandler;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import org.xml.sax.XMLReader;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.Attributes;
import java.io.*;
public class test extends DefaultHandler
{
String thisElement="";
String table_name="";
String sql="";
String value_clause="";
StringBuffer value_clauseBuffer;
String Insert_sql="";
String columnNames="";
public test() {
}
public void startElement(String namespaceURI, String localName,
String qName, Attributes atts) throws SAXException {
thisElement = qName;
if (thisElement!=table_name){
columnNames = columnNames + ", " + qName;
}
}
public void characters(char[] ch, int start, int length)
throws SAXException {
if (thisElement !="root"){
//I had added all this code as the character event was firing even for tag like <EMP>
if ((length == 0) && (thisElement !="") ){
table_name = thisElement;
sql = " Insert into "+ table_name +"(";
value_clause="";
value_clauseBuffer =null;
columnNames = "";
}
if ((length != 0) && (thisElement!="") && (thisElement!=table_name)){
String s = new String(ch, start, length);
String newString = s.replaceAll("'", "''");
if (value_clauseBuffer== null){
value_clauseBuffer = new StringBuffer(newString);
}
else{
value_clauseBuffer.append(newString);
}
}
}
}
public void endElement(String namespaceURI, String localName, String qName)
throws SAXException {
if (thisElement !="root"){
if (!(value_clauseBuffer == null)) {
try{
value_clauseBuffer.append("', '");
}catch(Exception e){
System.err.println(e);
System.out.print("value_clause " + value_clauseBuffer);
System.exit(2);
}
}
if (qName == table_name){
if (!(value_clauseBuffer == null)){
value_clause = "'"+value_clauseBuffer;
}
value_clause=value_clause.substring(0, (value_clause.length()-7));
columnNames =columnNames.substring(1, columnNames.length());
sql = sql + columnNames + " ) values (" + value_clause + "); ";
Insert_sql=Insert_sql + sql;
System.out.println("sql "+sql);
try{
// InsertData is the class handling JDBC code for this insert.
InsertData ID = new InsertData("username", "password", sql);
}
catch (Exception e) {
System.err.println(e);
System.out.print("sql " + sql);
System.exit(1);
}
}
thisElement = "";
}
}
public static void main (String args[]) {
XMLReader xmlReader = null;
try {
SAXParserFactory spfactory = SAXParserFactory.newInstance();
spfactory.setValidating(false);
SAXParser saxParser = spfactory.newSAXParser();
xmlReader = saxParser.getXMLReader();
xmlReader.setContentHandler(new SurveyReader());
xmlReader.setErrorHandler(new SurveyReader());
InputSource source = new InputSource(new FileInputStream("short.xml"));
xmlReader.parse(source);
} catch (Exception e) {
System.err.println(e);
System.exit(1);
}
}
}
JDBC call class -
import java.io.*;
import java.sql.*;
import oracle.jdbc.driver.*;
import oracle.xml.sql.*;
import oracle.sql.*;
public class InsertData {
static String conStr = "jdbc:oracle:thin:@hchp4002:1521:rxsmt401";
//static String conStr = "jdbc:oracle:thin:@bdhp4368:1521:rxast401";
public InsertData(String username, String password, String sqlString)
throws SQLException, FileNotFoundException, IOException {
/* if (args.length != 3) {
System.out.println ("Usage:java InsertData username password filename");
System.exit(0);
}
*/
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
// Connection conn = DriverManager.getConnection(conStr, args[0], args[1]);
Connection conn = DriverManager.getConnection(conStr, username, password);
conn.setAutoCommit(false);
String sql = "begin "+ sqlString + " end;";
OracleCallableStatement ocs =
(OracleCallableStatement)conn.prepareCall(sql);
ocs.executeUpdate();
ocs.close();
conn.commit();
conn.close();
}
}
May 23, 2005 - 2:30 pm UTC
before you do that, please rewrite the program to use BIND VARIABLES
you are probably spending 5/6ths of your runtime in the database PARSING sql, not executing it!
A reader, May 24, 2005 - 1:49 pm UTC
Thanks Tom for your input.
I have made required changes and now the file is getting loaded in almost half the time.
1. Please tell me if I should make java stored procedure for this program ?
2. Will it improve performance?
3. Can I make Java stored procedure for this Java program.( since it has non static methods from defualtHandler.)
Here is my new code-
import org.xml.sax.helpers.DefaultHandler;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import org.xml.sax.XMLReader;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.Attributes;
import java.io.*;
import java.sql.*;
import oracle.jdbc.driver.*;
import oracle.xml.sql.*;
import oracle.sql.*;
public class SurveyReader extends DefaultHandler
{
String thisElement="";
String table_name="";
String table_name_2="";
String sql="";
String value_clause="";
StringBuffer value_clauseBuffer;
String Insert_sql="";
int flag;
String columnNames="";
String questionmarks="";
static String conStr = "jdbc:oracle:thin:@****:1521:***";
static Connection conn;
String arrayValues[] = new String[30];
int j = 0;
int emptyElementFlag = 0;
public SurveyReader() throws SQLException, FileNotFoundException, IOException{
DBConnect("username", "password");
}
public static void DBConnect(String username, String password)
throws SQLException, FileNotFoundException, IOException {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
conn = DriverManager.getConnection(conStr, username, password);
conn.setAutoCommit(true);
}
public void startElement(String namespaceURI, String localName,
String qName, Attributes atts) throws SAXException {
thisElement = qName;
if (thisElement!=table_name){
columnNames = columnNames + ", " + qName;
questionmarks = questionmarks +", " + "?";
emptyElementFlag =0;
}
}
public void characters(char[] ch, int start, int length)
throws SAXException {
if (thisElement !="root"){
if ((length == 0) && (thisElement !="") ){
table_name = thisElement;
sql = " Insert into "+ table_name +"(";
value_clause="";
value_clauseBuffer =null;
columnNames = "";
questionmarks ="";
j =0;
}
if ((length != 0) && (thisElement!="") && (thisElement!=table_name)){
emptyElementFlag = 1;
String s = new String(ch, start, length);
String newString = s.replaceAll("'", "''");
// String newString = s;
if (value_clauseBuffer== null){
value_clauseBuffer = new StringBuffer(newString);
}
else{
value_clauseBuffer.append(newString);
}
}
}
}
public void endElement(String namespaceURI, String localName, String qName)
throws SAXException {
if (thisElement !="root"){
if ((!(value_clauseBuffer == null))||((emptyElementFlag ==0) && (qName !=table_name))) {
try{
//value_clauseBuffer.append("', '");
//System.out.println("value_clauseBuffer " + value_clauseBuffer);
if (value_clauseBuffer == null){
arrayValues[j]="";
}
else{
arrayValues[j]=""+value_clauseBuffer;
}
j = j+1;
value_clauseBuffer = null;
emptyElementFlag =0;
}catch(Exception e){
System.err.println(e);
// System.out.print("value_clause " + value_clauseBuffer);
System.exit(2);
}
}
if (qName == table_name){
if (!(value_clauseBuffer == null)){
value_clause = "'"+value_clauseBuffer;
}
// value_clause=value_clause.substring(0, (value_clause.length()-7));
// System.out.println("value clause "+value_clause);
//System.out.println("value clause substring "+ value_clause.substring(0, value_clause.
columnNames =columnNames.substring(1, columnNames.length());
//System.out.println("questionmarks length "+questionmarks.length()/3);
int paramNumber = j;
// System.out.println("paramNumber "+paramNumber);
questionmarks =questionmarks.substring(1, questionmarks.length());
//System.out.println("colunnames "+columnNames);
// System.out.println("questionmarks "+questionmarks);
// sql = sql + columnNames + " ) values (" + value_clause + "); ";
sql = sql + columnNames + " ) values (" + questionmarks +"); ";
// System.out.println("sql " +sql);
Insert_sql=Insert_sql + sql;
sql = "Begin "+sql + " End; ";
try{
PreparedStatement pstat = conn.prepareStatement(sql);
for (int i=0; i<=j-1; i++ ){
int k = i+1;
pstat.setObject(k, arrayValues[i]);
// System.out.println("k "+k);
// System.out.println("arrayValues[i] "+ arrayValues[i]);
}
ResultSet rset = pstat.executeQuery();
rset.close();
pstat.close();
}
catch (Exception e) {
System.err.println(e);
System.out.print("sql " + sql);
System.exit(1);
}
}
table_name_2 = table_name;
thisElement = "";
}
}
public static void main (String args[]) {
XMLReader xmlReader = null;
try {
SAXParserFactory spfactory = SAXParserFactory.newInstance();
spfactory.setValidating(false);
SAXParser saxParser = spfactory.newSAXParser();
xmlReader = saxParser.getXMLReader();
xmlReader.setContentHandler(new SurveyReader());
xmlReader.setErrorHandler(new SurveyReader());
InputSource source = new InputSource("newtonHalf.xml");
//InputSource source = new InputSource("short.xml");
xmlReader.parse(source);
conn.close();
} catch (Exception e) {
System.err.println(e);
System.exit(1);
}
}
}
Thanks!
May 24, 2005 - 3:36 pm UTC
1) nope
2) doubtful
3) probably, you just need a static entry point
I would once again figure out "where is my time spent"
start with a sql_trace, see if you see any low hanging fruit.
can you not prepare ONCE and just bind/execute bind/execute over and over?
And batch them up -- using batching.
A reader, May 24, 2005 - 3:52 pm UTC
Thanks for your answers.
I thought of preparing once and useing bind.
But I feel that will be diffcult (if not impossible ) as
the XML file format may vary for all the rows (if particular column is not in there then the corresponding tags wont be there.
Am i missing something by which prepare once bind multiple times will be possible.
I see that speed of loading of file reduces as it reaches towards the end. May be I need to talk to DBAs to see what is causing this.
Thanks again for taking time to look at my code.
A reader, May 25, 2005 - 4:07 pm UTC
Tom,
I have been trying to implement statment Caching from the link you provided.
Here is my modified code --
import org.xml.sax.helpers.DefaultHandler;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import org.xml.sax.XMLReader;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.Attributes;
import java.io.*;
import java.sql.*;
import oracle.jdbc.driver.*;
import oracle.xml.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;
public class SurveyReader extends DefaultHandler
{
String thisElement="";
String table_name="";
String table_name_2="";
String sql="";
String value_clause="";
StringBuffer value_clauseBuffer;
String Insert_sql="";
int flag;
String columnNames="";
String questionmarks="";
static String conStr = "jdbc:oracle:thin:@abcd1234:1521:rrrrr101";
static Connection conn;
String arrayValues[] = new String[30];
int j = 0;
int emptyElementFlag = 0;
public SurveyReader() throws SQLException, FileNotFoundException, IOException{
// DBConnect("username", "password");
OracleConnect("username", "password");
}
public static void DBConnect(String username, String password)
throws SQLException, FileNotFoundException, IOException {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
conn = DriverManager.getConnection(conStr, username, password);
conn.setAutoCommit(true);
}
public static void OracleConnect(String username, String password) throws SQLException{
OracleDataSource ods = new OracleDataSource();
//ods.setMaxStatements(0);
ods.setExplicitCachingEnabled(true);
// Set the user name, password, driver type and network protocol
ods.setUser(username);
ods.setPassword(password);
ods.setURL(conStr);
Connection conn = ods.getConnection();
}
public void startElement(String namespaceURI, String localName,
String qName, Attributes atts) throws SAXException {
thisElement = qName;
if (thisElement!=table_name){
columnNames = columnNames + ", " + qName;
questionmarks = questionmarks +", " + "?";
emptyElementFlag =0;
}
}
public void characters(char[] ch, int start, int length)
throws SAXException {
if (thisElement !="root"){
if ((length == 0) && (thisElement !="") ){
table_name = thisElement;
sql = " Insert into "+ table_name +"(";
value_clause="";
value_clauseBuffer =null;
columnNames = "";
questionmarks ="";
j =0;
}
if ((length != 0) && (thisElement!="") && (thisElement!=table_name)){
emptyElementFlag = 1;
String s = new String(ch, start, length);
String newString = s.replaceAll("'", "''");
// String newString = s;
if (value_clauseBuffer== null){
value_clauseBuffer = new StringBuffer(newString);
}
else{
value_clauseBuffer.append(newString);
}
}
}
}
public void endElement(String namespaceURI, String localName, String qName)
throws SAXException {
if (thisElement !="root"){
if ((!(value_clauseBuffer == null))||((emptyElementFlag ==0) && (qName !=table_name))) {
try{
//value_clauseBuffer.append("', '");
if (value_clauseBuffer == null){
arrayValues[j]="";
}
else{
arrayValues[j]=""+value_clauseBuffer;
}
j = j+1;
value_clauseBuffer = null;
emptyElementFlag =0;
}catch(Exception e){
System.err.println(e);
System.exit(2);
}
}
if (qName == table_name){
if (!(value_clauseBuffer == null)){
value_clause = "'"+value_clauseBuffer;
}
// value_clause=value_clause.substring(0, (value_clause.length()-7));
// System.out.println("value clause "+value_clause);
columnNames =columnNames.substring(1, columnNames.length());
//System.out.println("questionmarks length "+questionmarks.length()/3);
int paramNumber = j;
questionmarks =questionmarks.substring(1, questionmarks.length());
// sql = sql + columnNames + " ) values (" + value_clause + "); ";
sql = sql + columnNames + " ) values (" + questionmarks +"); ";
Insert_sql=Insert_sql + sql;
sql = "Begin "+sql + " End; ";
System.out.println("before opening prepareStatement ");
try{
PreparedStatement pstat = conn.prepareStatement(sql);
System.out.println("1. Stmt is " + pstat);
/* for (int i=0; i<=j-1; i++ ){
int k = i+1;
//pstat.setObject(k, arrayValues[i]);
System.out.println("k "+k);
System.out.println("arrayValues[i] "+ arrayValues[i]);
}
ResultSet rset = pstat.executeQuery();
rset.close();
*/
pstat.close();
}
catch (Exception e) {
System.err.println(e);
System.out.print("sql " + sql);
System.exit(1);
}
}
table_name_2 = table_name;
thisElement = "";
}
}
public static void main (String args[]) {
XMLReader xmlReader = null;
try {
SAXParserFactory spfactory = SAXParserFactory.newInstance();
spfactory.setValidating(false);
SAXParser saxParser = spfactory.newSAXParser();
xmlReader = saxParser.getXMLReader();
xmlReader.setContentHandler(new SurveyReader());
xmlReader.setErrorHandler(new SurveyReader());
InputSource source = new InputSource("short.xml");
xmlReader.parse(source);
conn.close();
} catch (Exception e) {
System.err.println(e);
System.exit(1);
}
}
}
I am getting run time null pointer exception at
PreparedStatement pstat = conn.prepareStatement(sql);
C:\vvv>java SurveyReader
before opening prepareStatement
java.lang.NullPointerException
sql Begin Insert into emp( emp_ID, name ) values
( ?, ?); End;
Can you please tell me what am I doing wrong?
Thanks!
A reader, May 27, 2005 - 8:50 am UTC
Tom,
I am awaiting for your help / guidance in this matter.
Please respond.
Thanks!
May 27, 2005 - 9:31 am UTC
I asked Mark Piermarini to peek at this and he says:
My first thought on this is that the statement being prepared is a plain INSERT but it's wrapped within a BEGIN and END block. Shouldn't they be using conn.prepareCall() since it'd now a block of code instead of a SQL statement?
1. Their method OracleConnect() creates a connection but it saves it in a local variable that immediately goes out of scope.
2. The System.err.println(e) statements should be e.printStackTrace() because it's WAY more informative for debugging.
3. Unless they REALLY know their XML documents well, the array defined as String arrayValues[] = new String[30]; will probably get blown out at some point with an out of bounds exception.
4. The following code
InputSource source = new InputSource("short.xml");
should be
InputSource source = new InputSource(new FileReader("short.xml"));
I don't know how that even worked...
The original error is because of issue #1 but they might want to think about rewriting some of that code Not sure the prepareStatement/callableStatement theory still applies but I'd have them fix the items above and test it.
A reader, May 27, 2005 - 11:25 am UTC
Thank you so much for your response.
I will take care of all the points put forward.
The code is working fine with Connection object. The only problem is it is very slow.
I am also trying to see if the format of XML file can be modified by sender system. So that it has empty tags in case particular column is not having values in it.
This way I can prepare statement only once when the table changes. and for rest of the time just do the binding as you pointed out. Hoping to increase speed dramatically with this approach.
Thanks you so much. I was feeling so lost and now I know where I can discuss such problems to get such valuable inputs from experts.
Thanks once again.
Java code behaving differently on Windows and Unix ....
A reader, June 06, 2005 - 3:50 pm UTC
I have made the changes in code as per your and Marks suggestion.
After trying different commit frequncy I have found that 9MB of file is taking minimum of 2 hours to load using my program while running from my local machine.
One of the suggestion that our team had was to run it on Unix box where the DB resides. (Since this is where finally we are going to get the files.)
We were expecting to see performace gain with this as sql net time will be reduced with this approach.
However when I tried running the program which is working fine on my local box on the Unix box the results are very surprising.
I see that
public void characters( ... )
method is fired for table name Tags (Tags that have only other tags as contents and no text content in it) in windows.
Where as on Unix this method is not fired.
Why is this descripancy?
Is there any other solution that for me to change the code to work in Unix.
Is this related to any environment setting?
Please help.
Here is my modified code --
import org.xml.sax.helpers.DefaultHandler;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import org.xml.sax.XMLReader;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.Attributes;
import java.io.*;
import java.sql.*;
import oracle.jdbc.driver.*;
import oracle.xml.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;
import java.util.*;
public class SurveyReader extends DefaultHandler
{
String thisElement="";
String table_name="";
String table_name_2="";
String sql="";
String value_clause="";
StringBuffer value_clauseBuffer;
String Insert_sql="";
int flag;
String columnNames="";
String questionmarks="";
static String conStr = "jdbc:oracle:thin:@abc1234:1521:servicename";
static Connection conn;
String arrayValues[] = new String[30];
int j = 0;
int emptyElementFlag = 0;
// PreparedStatement pstat;
oracle.jdbc.OraclePreparedStatement opstmt;
ResultSet rset;
static int rowsInserted =0;
public SurveyReader() throws SQLException, FileNotFoundException, IOException{
OracleConnect("username", "password");
opstmt = (oracle.jdbc.OraclePreparedStatement) conn.prepareStatement("select * from Emp");
}
public static void DBConnect(String username, String password)
throws SQLException, FileNotFoundException, IOException {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
conn = DriverManager.getConnection(conStr, username, password);
conn.setAutoCommit(true);
}
public static void OracleConnect(String username, String password) throws SQLException{
OracleDataSource ods = new OracleDataSource();
//ods.setMaxStatements(0);
// ods.setStatementCacheSize(60);
// Set the user name, password, driver type and network protocol
ods.setUser(username);
ods.setPassword(password);
ods.setURL(conStr);
conn = ods.getConnection();
conn.setAutoCommit(false);
((oracle.jdbc.OracleConnection) conn).setExplicitCachingEnabled(true);
((oracle.jdbc.OracleConnection) conn).setStatementCacheSize(5);
System.out.println("Connection done");
}
public void startElement(String namespaceURI, String localName,
String qName, Attributes atts) throws SAXException {
thisElement = qName;
if (thisElement!=table_name){
columnNames = columnNames + ", " + qName;
questionmarks = questionmarks +", " + "?";
emptyElementFlag =0;
}
System.out.println("qName "+qName);
}
public void characters(char[] ch, int start, int length)
throws SAXException {
// this statement is NOT printed for EMP, DEPT etc tags.
System.out.println("thisElement "+thisElement);
if (thisElement !="root"){
if ((length == 0) && (thisElement !="") ){
table_name = thisElement;
sql = " Insert into "+ table_name +"(";
value_clause="";
value_clauseBuffer =null;
columnNames = "";
questionmarks ="";
j =0;
}
if ((length != 0) && (thisElement!="") && (thisElement!=table_name)){
emptyElementFlag = 1;
String s = new String(ch, start, length);
String newString = s.replaceAll("'", "''");
// String newString = s;
if (value_clauseBuffer== null){
value_clauseBuffer = new StringBuffer(newString);
}
else{
value_clauseBuffer.append(newString);
}
}
}
}
public void endElement(String namespaceURI, String localName, String qName)
throws SAXException {
if (thisElement !="root"){
if ((!(value_clauseBuffer == null))||((emptyElementFlag ==0) && (qName !=table_name))) {
try{
//value_clauseBuffer.append("', '");
if (value_clauseBuffer == null){
arrayValues[j]="";
}
else{
arrayValues[j]=""+value_clauseBuffer;
}
j = j+1;
value_clauseBuffer = null;
emptyElementFlag =0;
}catch(Exception e){
// System.err.println(e);
e.printStackTrace();
System.exit(2);
}
}
if (qName == table_name){
if (!(value_clauseBuffer == null)){
value_clause = "'"+value_clauseBuffer;
}
// value_clause=value_clause.substring(0, (value_clause.length()-7));
// System.out.println("value clause "+value_clause);
columnNames =columnNames.substring(1, columnNames.length());
//System.out.println("questionmarks length "+questionmarks.length()/3);
int paramNumber = j;
questionmarks =questionmarks.substring(1, questionmarks.length());
// sql = sql + columnNames + " ) values (" + value_clause + "); ";
sql = sql + columnNames + " ) values (" + questionmarks +"); ";
Insert_sql=Insert_sql + sql;
sql = "Begin "+sql + " End; ";
System.out.println("table_name_2 "+table_name_2);
System.out.println("sql created time "+ new java.util.Date());
try{
if (table_name_2 !=table_name) {
//pstat.close();
//pstat = conn.prepareStatement(sql);
// opstmt.close();
// opstmt = (oracle.jdbc.OraclePreparedStatement) conn.prepareStatement(sql);
opstmt = (oracle.jdbc.OraclePreparedStatement) (((oracle.jdbc.OracleConnection)conn).prepareStatement(sql));
}
else{
opstmt = (oracle.jdbc.OraclePreparedStatement)(((oracle.jdbc.OracleConnection)conn).getStatementWithKey("searchTable"));
}
// System.out.println("1. Stmt is " + pstat);
for (int i=0; i<=j-1; i++ ){
int k = i+1;
//pstat.setObject(k, arrayValues[i]);
opstmt.setObject(k, arrayValues[i]);
//System.out.println("k "+k);
//System.out.println("arrayValues[i] "+ arrayValues[i]);
}
//rset = pstat.executeQuery();
rset = opstmt.executeQuery();
rowsInserted = rowsInserted +1;
if (rowsInserted == 200){
rowsInserted =0;
conn.commit();
}
rset.close();
opstmt.closeWithKey("searchTable");
System.out.println("sql executed time "+ new java.util.Date());
}
catch (Exception e) {
// System.err.println(e);
e.printStackTrace();
System.out.print("sql " + sql);
System.exit(1);
}
//System.out.println("after running sql "+new java.util.Date()) ;
table_name_2 = table_name;
}
thisElement = "";
}
}
public static void main (String args[]) {
XMLReader xmlReader = null;
System.out.println("Time " + new java.util.Date());
try {
SAXParserFactory spfactory = SAXParserFactory.newInstance();
spfactory.setValidating(false);
SAXParser saxParser = spfactory.newSAXParser();
xmlReader = saxParser.getXMLReader();
xmlReader.setContentHandler(new SurveyReader());
xmlReader.setErrorHandler(new SurveyReader());
InputSource source = new InputSource(new FileReader("short.xml"));
xmlReader.parse(source);
conn.commit();
conn.close();
System.out.println("Time " + new java.util.Date());
} catch (Exception e) {
// System.err.println(e);
e.printStackTrace();
System.exit(1);
}
}
}
This is sample test file --
<?xml version="1.0" encoding="UTF-8"?>
<root>
<EMP>
<EMP_ID>201188</EMP_ID>
<CREATED_DT>2005-04-14T07:05:09.257</CREATED_DT>
<LAST_MOD_DT>2005-04-14T07:05:09.257</LAST_MOD_DT>
</EMP>
<EMP>
<EMP_ID>201199</EMP_ID>
<CREATED_DT>2005-04-14T07:05:09.257</CREATED_DT>
<LAST_MOD_DT>2005-04-14T07:05:09.257</LAST_MOD_DT>
</EMP>
<DEPT>
<DEPT_ID>1</DEPT_ID>
<DEPT_NAME>xxxx</DEPT_NAME>
</DEPT>
</root>
A reader, June 07, 2005 - 9:53 am UTC
Tom,
I understand the last question I have asked is Java question than Oracle one. But I have tried all other resources for the vierd problem that I am getting.
And am really hoping to get some good explanation for my problem from you.
(Event firing differently in case of Windows and Unix.)
If I make Java Stored procedure for the java class will that be work around for my problem?
I am working on developing a new code for Unix platform. Not feeling comforatble needing different code for different O/S for "platform independent " Java though...
Thanks for all your help!
June 07, 2005 - 12:50 pm UTC
i sent it to mark, if he gets a chance, he'll peek at it.
Other than "making up something", I'll not be extremely useful for this one.
A reader, June 07, 2005 - 12:57 pm UTC
Thanks Tom for trying to help me with Java issue as well.
I hope Mark can find some time to look at this issue.
Thanks once again!
A reader, June 09, 2005 - 11:46 am UTC
Tom,
I wrote new code to work on Unix.
And ran it from Unix box where our DB resides.
And now 9MB file is takeing 2 minutes to load into DB instead of 2 hours !!
(I am parsing each statement and no binding used.)
I had not thought SQL-Net connection s taking most of the time in running this code!!
I am glad my problem is solved. Wanted to share it with you as you , Sean and MArk has contributed largely towrds it.
Thanks for the wonderful site. Since I kept getting inputs from you I kept going.
Although I am still not clear why events firing is different on Unix and WIndow.
Any way everything is good that ends good. :-)
Unix Code vs Windows Code
CG, June 10, 2005 - 11:57 am UTC
Reader,
What did you have to rewrite specifically for Unix?
Dont see any file paths, registry references?
Can you post the difference?
Did you do a sql_trace as Tom suggested?
And if so is THAT where you got the conclusion that it was SQL*NET?
Just curious. I am going to use a SAX parser myself so my problem is similiar.
Only thing is that XML document will no have to load multiple tables.... at least not yet.
A reader, June 13, 2005 - 9:17 am UTC
I had to write new code since event firing was different on Unix and Windows.
Yes TKPROF showed me that there was nothing on the Oracle side which was taking longer time to execute the Inserts submitted to Oracle. SO realised that main time is getting spend in sending stataments to Oracle. SO I debugged my java code tos ee how long it is taking to create new SQL. since tere was not much time lapse between creating of two statements we came up with conclusion that it must be network time that can be reduced.
Here is my new code.
As you can see here I am parsing every single statement.
import org.xml.sax.helpers.DefaultHandler;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import org.xml.sax.XMLReader;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.Attributes;
import java.io.*;
import java.sql.*;
import oracle.jdbc.driver.*;
import oracle.xml.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;
import java.util.*;
public class MySaxParser extends DefaultHandler
{
static String conStr = "jdbc:oracle:thin:@abc1234:1111:dbname";
static Connection conn;
oracle.jdbc.OraclePreparedStatement opstmt;
ResultSet rset;
String tableName;
String tableName2;
int StartElementCount =0;
StringBuffer value_clauseBuffer;
String columnNames="";
String questionmarks="";
int j =0;
String arrayValues[] = new String[30];
String sql="";
int rowsInserted =0;
int output;
String startElement;
public NewtonSaxParser() throws SQLException, FileNotFoundException, IOException{
}
public static void OracleConnect(String username, String password) throws SQLException{
OracleDataSource ods = new OracleDataSource();
ods.setUser(username);
ods.setPassword(password);
ods.setURL(conStr);
conn = ods.getConnection();
conn.setAutoCommit(true);
((oracle.jdbc.OracleConnection) conn).setExplicitCachingEnabled(true);
//((oracle.jdbc.OracleConnection) conn).setStatementCacheSize(5);
}
public void startDocument() throws SAXException {
StartElementCount = 1;
j =0;
try{
OracleConnect("username", "password");
opstmt = (oracle.jdbc.OraclePreparedStatement) conn.prepareStatement("select * from Emp");
}
catch (Exception e) {
System.err.println(e);
System.exit(1);
}
}
public void startElement(String namespaceURI, String localName, String qName, Attributes atts) throws SAXException {
startElement = qName;
if (qName != "root"){
StartElementCount = StartElementCount+1;
}
}
public void characters(char[] ch, int start, int length) throws SAXException {
StartElementCount = StartElementCount+1;
String s = new String(ch, start, length);
String newString = s.replaceAll("'", "''");
if (value_clauseBuffer== null){
value_clauseBuffer = new StringBuffer(newString);
}
else{
value_clauseBuffer.append(newString);
}
}
public void endElement(String namespaceURI, String localName, String qName)
throws SAXException {
if ((qName!="root")&&(StartElementCount <2) &&(startElement!=qName))
{
tableName = qName;
questionmarks =questionmarks.substring(1, questionmarks.length());
columnNames =columnNames.substring(1, columnNames.length());
sql = "Insert into "+ tableName + "( " + columnNames + " ) values (" + questionmarks +"); ";
sql = "Begin " + sql + "End; ";
// System.out.println("sql "+sql);
// Oracle Connection to run SQL
try
{
//if (tableName2 !=tableName)
// {
opstmt = (oracle.jdbc.OraclePreparedStatement) (((oracle.jdbc.OracleConnection)conn).prepareStatement(sql));
/* }
else
{
// opstmt = (oracle.jdbc.OraclePreparedStatement)(((oracle.jdbc.OracleConnection)conn).getStatementWithKey("searchTable"));
}
*/
for (int i=0; i<=j-1; i++ )
{
int k = i+1;
opstmt.setObject(k, arrayValues[i]);
}
rset = opstmt.executeQuery();
rowsInserted = rowsInserted +1;
/* if (rowsInserted == 200){
rowsInserted =0;
conn.commit();
}
*/
conn.commit();
rset.close();
opstmt.closeWithKey("searchTable");
}catch (Exception e) {
System.err.println(e);
System.out.println(sql);
System.out.println("table name "+tableName);
System.exit(1);
}
tableName2 = tableName;
j = 0;
columnNames = "";
questionmarks ="";
}
else
{
StartElementCount = 0;
columnNames = columnNames + ", " + qName;
questionmarks = questionmarks +", " + "?";
if ((value_clauseBuffer == null) && (startElement ==qName)) {
value_clauseBuffer =new StringBuffer("");
}
if (!(value_clauseBuffer == null))
{
arrayValues[j]=""+value_clauseBuffer;
j++;
value_clauseBuffer = null;
}
}
}
public static void main (String args[]) {
XMLReader xmlReader = null;
System.out.println("Time " + new java.util.Date());
try {
SAXParserFactory spfactory = SAXParserFactory.newInstance();
spfactory.setValidating(false);
SAXParser saxParser = spfactory.newSAXParser();
xmlReader = saxParser.getXMLReader();
xmlReader.setContentHandler(new NewtonSaxParser());
xmlReader.setErrorHandler(new NewtonSaxParser());
InputSource source = new InputSource(new FileReader("short.xml"));
xmlReader.parse(source);
conn.commit();
conn.close();
System.out.println("Time " + new java.util.Date());
} catch (Exception e) {
System.err.println(e);
System.exit(1);
}
}
}
All the Best for you parser.
If you do not have huge file then have you considered use of DBMS_XML package in Pl/SQL instead of SAx parser approach?
Thanks reader
A reader, June 17, 2005 - 3:28 pm UTC
My distinct situation is that I am NOT a Java programmer.
I could "put something together" and learn from trial and error. But thats not time sensitive way.
My stronger suit would be PL/SQL.
My other issue is that I would not want to have to rewrite this in Java if I start to have memory issues.
I think I follow your code pretty well. I just dont know what some of your methods were not used ( StartElement, EndElement, characters etc )
I most likely will use the DOM but only because I dont want to get into trying to write Java code that is no good or buggy.
Thanks for your post.
It would be nice if Oracle would put a SAX parser in there PL/SQL API's.
Fastest way to load data
Mary W., July 05, 2005 - 9:48 pm UTC
Tom,
Would it be faster to load data into a table from text file via SQL Loader or to write a script to load data from XML?
July 06, 2005 - 7:37 am UTC
I'm going to guess sqlldr.
real time data load
Mary W, July 06, 2005 - 9:17 am UTC
Thanks, Tom.
I prefer SQL Loader as well, just wanted to make sure that i am not missing something on xml.
Can you suggest a best way of doing real-time data inserts?
I have servers that are constantly writing data to a java package that inserts it to the database. Is there a way to skip java in genreal and load straight to Oracle?
and thanks again for all of your valuable help!
July 06, 2005 - 9:29 am UTC
well, how do you currently "write data to a java package"? what is doing that?
current info
A reader, July 08, 2005 - 12:51 pm UTC
Currently we have built an application that accepts all of the posts via http and puts them into a hash map. there is timer that check to see for changes in the map and pushes those to the database.
July 08, 2005 - 3:01 pm UTC
so, how much data do you lose ever day this way?
what happens when your middle tier crashes?
results
Mary W, July 11, 2005 - 4:00 pm UTC
we dont loose data yet and the middle-tier has not crashed. but "Yet" is the key word. isn't there an easier way of accomplishing this by going directly to Oracle? data streaming of some sort?
July 11, 2005 - 5:53 pm UTC
just insert it, databases were born to...
join
be written to
dbms_xmlsave.setDateFormat
reader, July 27, 2005 - 3:20 pm UTC
I have the following code on a 9.2.0.6 database
CREATE OR REPLACE procedure insert_xml( p_directory in varchar2,
p_filename in varchar2,
vtableName in varchar2 )
AS
v_filelocator bfile;
v_cloblocator clob;
l_ctx DBMS_XMLSAVE.ctxType;
l_rows number;
BEGIN
DBMS_LOB.createtemporary( v_cloblocator, true );
v_filelocator := bfilename( p_directory, p_filename );
DBMS_LOB.open( v_filelocator, dbms_lob.file_readonly );
DBMS_LOB.loadfromfile( v_cloblocator, v_filelocator, dbms_lob.getlength( v_filelocator ) );
l_ctx := DBMS_XMLSAVE.newContext( vTableName );
DBMS_XMLSave.setDateFormat( l_ctx, 'MM-dd-yyyy HH:mm:ss' );
l_rows := DBMS_XMLSAVE.insertxml( l_ctx, v_cloblocator );
DBMS_XMLSAVE.closeContext( l_ctx );
DBMS_OUTPUT.put_line( l_rows || ' rows inserted...');
DBMS_LOB.close( v_filelocator );
DBMS_LOB.FREETEMPORARY( v_cloblocator );
EXCEPTION
WHEN OTHERS THEN
DBMS_LOB.filecloseall;
raise;
END insert_xml;
/
show errors
.... and this xml doc
<?xml version = '1.0'?>
<UT_PROVIDER>
<ROW num="1">
<ENTITY_SEQ>855</ENTITY_SEQ>
<FACILITY_UIC>UNKNOWN</FACILITY_UIC>
<FNAME>UNKNOWN</FNAME>
<LNAME>UNKNOWN</LNAME>
<PAYGRADE_ID>CIV</PAYGRADE_ID>
<PROV_SEQ>8078</PROV_SEQ>
<RATE>CIV</RATE>
<SERVICE_ID>CIV</SERVICE_ID>
<SSN>000000000</SSN>
<START_DATE>01-01-1901 08:12:33</START_DATE>
<VIEW_SENS_IND>N</VIEW_SENS_IND>
<VIEW_STD_IND>N</VIEW_STD_IND>
<VIEW_SUPPLY_SETUP_IND>N</VIEW_SUPPLY_SETUP_IND>
</ROW>
</UT_PROVIDER>
the table looks like
Name Null? Type
----------------------------------------- -------- -----------------
CMD_SEQ NUMBER
CORPS_ID VARCHAR2(3)
DEA_ID VARCHAR2(9)
ENTITY_SEQ NOT NULL NUMBER
FACILITY_UIC NOT NULL VARCHAR2(8)
FNAME NOT NULL VARCHAR2(20)
LNAME NOT NULL VARCHAR2(20)
MNAME VARCHAR2(20)
PAYGRADE_ID NOT NULL VARCHAR2(4)
PROV_SEQ NOT NULL NUMBER
RATE NOT NULL VARCHAR2(6)
SERVICE_ID NOT NULL VARCHAR2(6)
SSN NOT NULL VARCHAR2(9)
START_DATE NOT NULL DATE
STOP_DATE DATE
SUFFIX VARCHAR2(4)
USERNAME VARCHAR2(40)
VIEW_SENS_IND NOT NULL VARCHAR2(1)
VIEW_STD_IND NOT NULL VARCHAR2(1)
VIEW_SUPPLY_SETUP_IND NOT NULL VARCHAR2(1)
Metalink note 243100.1 gives an example of changing
the default date format which is ( MM/dd/yyyy HH:mm:ss )
to ( yyyy-MM-dd hh:mm:ss )SimpleDateFormat.
But when I do it here I get an error why is that?
here is the error
BEGIN insert_xml('WIP','sampledoc.xml','UT_PROVIDER'); END;
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
oracle.xml.sql.OracleXMLSQLException: 'java.text.ParseException: Unparseable
date: "01-01-1901 08:12:33"' encountered during processing ROW element 0. All
prior XML row changes were rolled back. in the XML document.
ORA-06512: at "SYS.DBMS_XMLSAVE", line 65
ORA-06512: at "SAMS.INSERT_XML", line 15
ORA-06512: at line 1
July 27, 2005 - 3:49 pm UTC
mm is month
mi is minute
you have mm in the time component
thank you
reader, July 27, 2005 - 4:08 pm UTC
also can you point me to a thread where you discussed why java takes WAY longer using a SAXparser to load XML then just using PL/SQL?
I loaded an XML doc with 115 rows with Java Stored Proc and it took 15 minutes and it was only 64kb in size.
An xml document of the same table with 253 rows loaded in less then 2 seconds.
Im wondering if using the internal JDBC driver is a cause
and forming column names for the inserts using the java.lang.String type is a BIG issue.
Thanks for the answer it worked perfectly.
strange error with dbms_xmlsave.insertxml
András Csicsári, September 20, 2005 - 10:02 am UTC
Dear Tom!
I am just dealing with an error related to dbms_xmlsave.insertxml.
I have a loader routine which loads large amount of data to 5 tables.
There was no problem with the solution in our development DBs (5 different), but yesterday I tried to load the "regular" xml file to 2 new DBs unsuccessfully.
These DBs even makes my simple test script (below) fails.
The DB configurations are almost the same (9.2.0.6.21 EE on IBM z/OS), the DBs where the import fails have more memory/tablespaces assigned, but not less.
The client is the same, I simply do not know what the problem is.
The error message I am getting on the "bad" DB IS (sorry, Hungarian):
ORA-29532: a Java hÝvßs nem kezelt Java kivÚtellel Úrt vÚget:
\u0599\u0043\u04C5K\u01D4\u04CB\u0098\u04CB\u0599\u0043\u04C5\u7513\u2613\u0167\u00C5\u05E3\u0256\u057A@}\u0599\u0043\u04C5K\u0444\u0083K\u0119\u0265\u0159K\u0599\u0043\u04C5\u2613\u0167\u00C5\u05E3\u0256\u057A@\uCBE5yoooo\u02E9|.\u0596\u0096.\u027C\u062Fo\u062Fjooooo|oooooo\u4BF6=}@\u00CC\u0663Q\u0563@\u004D\u025D@\u12C0@\u0509\u0544\u0155@\u0496\u0645\u0082\u0240\u7513`\u0096\u0654\u0384\u05A2U\u00C5\u0080\u0149\u00A2\u0241\u01CC\u0647\u0163\u0145K@\u0656\u6005\u04C5\u0500\u0185\u04C4\u0593\u01D6\u0245\u0081@\u048C\u0242\u0155@\u0069@\u7513`\u0116\u04A4\u0505\u0563\u0114\u0081\u054Bßsa k÷zben az XML-dokumentumban.
ORA-06512: a(z) "SYS.DBMS_XMLSAVE", helyen a(z) 65. sornßl
ORA-06512: a(z) helyen a(z) 10. sornßl
English text: ORA-29532 Java call terminated by uncaught Java exception: string
The referenced line in dbms_xmlsave: p_setpreservewhitespace(ctxhdl, 0);
The other problem as you can see is that the error message could not be read (seems to be unicode?), because of some settings but I could not solve this too.
Strange, never happened with other APIs - Java vs Oracle code page conversion error?
Do you have any idea about the reason(s)?
Thanks in advance!
Test script - runs on the "good" DBs, but not on the "bad" ones:
DECLARE
gv_dummy_char VARCHAR2(2000);
v_xmlsave_ctx dbms_xmlsave.ctxtype; -- XML context
BEGIN
-- create test table
EXECUTE IMMEDIATE 'CREATE TABLE dbms_xmlsave_test( test NUMBER )';
-- create XML context
v_xmlsave_ctx := dbms_xmlsave.newcontext( targettable => 'DBMS_XMLSAVE_TEST' );
-- import data to table
gv_dummy_char := dbms_xmlsave.insertxml( ctxhdl => v_xmlsave_ctx
,xdoc => '<DBMS_XMLSAVE_TEST>
<ROW>
<TEST>1</TEST>
</ROW>
</DBMS_XMLSAVE_TEST>' );
-- close XML context
dbms_xmlsave.closecontext( ctxhdl => v_xmlsave_ctx );
-- drop test table
EXECUTE IMMEDIATE 'DROP TABLE dbms_xmlsave_test';
EXCEPTION
WHEN OTHERS THEN
-- drop test table
EXECUTE IMMEDIATE 'DROP TABLE dbms_xmlsave_test';
RAISE;
END;
September 21, 2005 - 1:22 pm UTC
sorry, no i do not.
thanks
András Csicsári, September 22, 2005 - 8:53 am UTC
We have created a TAR entry as we also couldn't find the reason.
Thanks, and sorry for disturbing you with such a "support" type question!
Date Format
Prasanth Kumar, May 24, 2006 - 3:14 am UTC
Hey Tom,
I am trying to Insert Records using a Stored Procedure.
The XML i generate has Date Fields in the following format dd-mon-yy. When i try inserting this it throws an error that Date is Unparsable. I know that if i Change the date format it works perfectly. My question is can I make the Procedure insert Data using InsertXML using the above mentioned date Format
Regards
Prasanth
converting diffrent file formats into XML
sarvan, May 09, 2008 - 12:36 am UTC
hi,
we plan to push different file formats(.xls,.txt,.csv and xml files) into Oracle tables. Is there any PLSQL utility is available in Oracle 10g?.As we have to load diffrent files formats into Oracle tables.
kindly give your suggestions how to proceed to acheive this.
May 12, 2008 - 10:09 am UTC
If you want to put these into structured rows and columns - you can use xmldb for xml (there are hundreds of xml bits of functionality - all well documented, check out the docs)
for txt - there is nothing to "convert", just load into a clob I guess.
for csv - external tables.
for xls, you'll be processing that in a client somewhere probably (you can query them with the heterogeneous gateway and odbc if you like, sort of like an external table, but you'd need a windows server somewhere to host them all)
Tables to XML
A reader, May 24, 2008 - 1:05 am UTC
I have a reverse problem. I'd like to write and convert the contents of tables to an XML file using JDBC.
Suppose I have a dept (department) and an employee (emp) table. emp has a CLOB column, say resume (text resume). The JDBC program reads from dept and emp tables, does some processing on the CLOB column, and writes the result to a file in XML format.
What is the most efficient way to do this if the emp table is a huge table and the CLOB column can be quite large in size as well? Should the JDBC program reads the data one record at a time or is there some sort of bulk operation that I can use to achieve the maximum performance?
May 24, 2008 - 6:58 pm UTC
totally insufficient data to answer - I can say it is highly likely java isn't needed at all, you can just write a query to return the xml you need.
Tables to XML Followup
A reader, May 27, 2008 - 11:55 am UTC
Sorry for not providing enough info. This is just a hypothetical question. Assuming that this is a Java application and the application already has many Java APIs that the JDBC "loader" must use (business logic already defined in the APIs) to convert table contents to XML files. How should I go about reading the data from the database and write the output to an XML file given that the CLOB column could potentially be very large in size? Would I be better off reading the data one record at a time?
May 27, 2008 - 3:07 pm UTC
I would, as stated, just read XML out of the database - the database is quite capable of producing all of the XML you need in SQL. So, my answer would be "just query it", you can therefore skip invoking all of the API's (I don't see what 'business logic' would have to do with 'generate XML')
xml
A reader, May 27, 2008 - 11:28 pm UTC
Tom:
1. reading this thread, i am concluding that it is not easy to map and load an XML file into an oracle table using PL/sql. is this correct?
Loading a text file (comma delimited or csv) using sqlldr is way much easier.
2. It seems that this stored procedure does not take each record and load it as separte row into the DB and rather loads the whole file as one row. correct?
CREATE OR REPLACE procedure insert_xml( p_directory in varchar2,p_filename in varchar2, vtableName in varchar2 )
3. Do not you have a simple SP that shows how to load an EMP.xml into the EMP oracle table.
xml
A reader, May 27, 2008 - 11:40 pm UTC
xml
A reader, July 15, 2008 - 4:57 pm UTC
Tom:
I am receiving an xml file via http post and saving it into a CLOB in a table. everyting works fine.
my_table
---------
bk_no number PK
file_data CLOB
When a power builder client screen displays the xml file it is not displayed in the XML tree format and more like a string with tags.
I did not know whether it is the data or oracle or PB.
I updated the column with a valid XML file as follows
SQL>update my_table set file_Data = 'XML string'
where bk_no=1234;
and i went to the screen to check the display. It showed the thing in one line (text format) with small squared boxes in between the tags. Do you know where might be the issue here.
SQL> select file_data from my_table where bk_no=1234
2 /
File_Data
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
<url>
<loc>http://www.example.com/</loc>
<lastmod>2005-01-01</lastmod>
<changefreq>monthly</changefreq>
<priority>0.8</priority>
</url>
</urlset>
July 15, 2008 - 8:12 pm UTC
it is pb not accepting the fact that this is xml, if in fact pb can display xml "pretty"
look at pb and ask pb why it is not doing what you believe it should.
xml is just TEXT
xml
A reader, July 15, 2008 - 8:54 pm UTC
Tom:
Yes it can. we have an app that does it.
Is there a way to check in oracle if the file was valid XML syntax. I assume that if it is not it wont be displayed in browser or any client.
would this mean that one file is not valid XML.
SQL> select xmltype(file_Data) from table
ERROR:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00209: PI names starting with XML are reserved
Error at line 19
ORA-06512: at "SYS.XMLTYPE", line 0
ORA-06512: at line 1
I am also wondering if clients look for DTD document defined inthe XML before it can display it.
PB?
A reader, July 16, 2008 - 7:25 am UTC
Your previous followup :
.. look at pb and ask pb why it is not doing what you believe it should
Who is pb?
July 16, 2008 - 10:13 am UTC
power builder
xml
A reader, July 16, 2008 - 11:45 am UTC
Tom:
When an http client uploads/posts a file to oracle. Does oracle save any special characters if file has blanks or spaces like this? if you select the file in sql8plus would it show if there are any special characters like newline or carriage returns?
<?xml version="1.0" encoding="ISO-8859-1" ?>
<note>
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Don't forget me this weekend!</body>
</note>
July 17, 2008 - 11:20 am UTC
we do nothing to the file other than store it.
a newline would appear in sqlplus as - well, a newline, it would not be "visible", you would see it
because
the
line
is
broken
like that text above...
Inserting data from XML file
Thiru, January 17, 2009 - 1:08 pm UTC
I am using DBMS_XMLSave.newContext and DBMS_XMLSave.insertXML ( after creating TYPE as OBJECT and nested table) to populate xml files into regular oracle tables. The files are 1 mill to 2 mill records long.
a) Is this Oracle package efficent to handle files this large. ( The reason I ask is it takes around 30 sec to just insert 20000 records ).
b) Are there any other much faster packages to use?
c) can sql loader be used to insert data by parsing the xml files?
Thanks
January 17, 2009 - 1:32 pm UTC
millions of records - this, this is the reason I don't see the point in XML anymore. It has been so overused, abused, misused.
take a peek at the originally linked to document (way above,
Developer XML
Parsing XML Efficiently
By Ping Guo, Julie Basu, Mark Scardina, and K. Karun
Choose the right XML parsing technique
)
c) no, well, not really, you could load into an xmltype - but sqlldr wouldn't be parsing anything. sqlldr is truly efficient to load good old fashioned boring, but fast and efficient and small - flat files.
Ultimately, I would be going back to the 'entity' that created an xml file with millions of 'records' and ask them "and you thought this would be a good idea why?"
and you can try the xml forum on otn.oracle.com - they've probably fielded this question millions of times themselves.
Use XMLDOM for parsing
Rafey, January 18, 2009 - 11:50 pm UTC
I have successfully used XMLDOM pl/sql API to parse a xml and store the data in various table using 9i and 10g. All it require is XMLDB to be enabled.
This will let you parse the XML docs from directory or from CLOB and get all elements and attribute.
xml loading
vikas, June 06, 2009 - 6:11 am UTC
Could you please post the code or give the link ?
June 08, 2009 - 1:00 pm UTC
did you consider looking at the documention? it is all available for free on otn.oracle.com