This discussion is archived
4 Replies Latest reply: Mar 9, 2012 8:25 AM by Jason_(A_Non) RSS

xml error whiclke inserting records

V prasad Pro
Currently Being Moderated
xml file is:  

<?xml version="1.0" encoding="UTF-8"?>
<root>
<CUSTOMER>
<CUSTOMER_ID>1</CUSTOMER_ID>
<TYPE>1</TYPE>
<ACTIVE>1</ACTIVE>
<CREATED_DT>2004-06-07T01:14:38.230</CREATED_DT>
<CREATED_BY>50</CREATED_BY>
<LAST_MOD_DT>2004-06-07T01:14:38.230</LAST_MOD_DT>
<LAST_MOD_BY>50</LAST_MOD_BY>
</CUSTOMER>
<CUSTOMER>
<CUSTOMER_ID>2</CUSTOMER_ID>
<TYPE>2</TYPE>
<ACTIVE>1</ACTIVE>
<CREATED_DT>2004-06-07T01:14:38.230</CREATED_DT>
<CREATED_BY>50</CREATED_BY>
<LAST_MOD_DT>2004-06-07T01:14:38.230</LAST_MOD_DT>
<LAST_MOD_BY>50</LAST_MOD_BY>
</CUSTOMER>
</root>

create or replace procedure sp_insert_xml_emps
   as
     v_filelocator bfile;
     v_cloblocator clob;
     l_ctx   dbms_xmlsave.ctxType;
     l_rows  number;
     p_directory varchar2(200);
     p_filename varchar2(200);
     vTableName varchar2(200);
     begin
      p_directory := get_handoff_path(51);
      p_filename:='custdata.xml';
      vTableName:='tb_CUSTOMERxml';
               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);
              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);
end sp_insert_xml_emps;




error is:

ORA-29532: Java call terminated by uncaught Java exception: oracle.xml.sql.OracleXMLSQLException: Start of root element expected.
  • 1. Re: xml error whiclke inserting records
    V prasad Pro
    Currently Being Moderated
    at-least any links. to helpful.
  • 2. Re: xml error whiclke inserting records
    Jason_(A_Non) Expert
    Currently Being Moderated
    What's the DDL for the CUSTOMER table look like?

    What's
    get_handoff_path(51);
    return?

    How are you running this?

    What version? (4 digits)

    I can't think of anything else that would be needed to complete a test case/script a random person could use to duplicate the issue but if you are aware of anything, please include it.

    Also, since I see you just started a new thread with this question, pick one spot to continue the discussion on
    {message:id=10197490}
  • 3. Re: xml error whiclke inserting records
    V prasad Pro
    Currently Being Moderated
    Hi a_non
    
    
    Table Structure
    SQL> desc tb_customerxml;
    Name                 Type          Nullable Default Comments 
    -----------          ------------- -------- ------- -------- 
    CUSTOMER_ID    NUMBER              Y                         
    TYPE                 VARCHAR2(100)  Y                         
    ACTIVE              VARCHAR2(50)   Y                         
    CREATED_DT      VARCHAR2(30)   Y                         
    CREATED_BY      VARCHAR2(100) Y                         
    LAST_MOD_DT   VARCHAR2(30)   Y                         
    LAST_MOD_BY   VARCHAR2(100)  Y                         
     
    
    get_handoff_path(51); is a Procedure it returns the Directory Path.
    ex cheio/chennai/plo
    
    i execute procedure.
     pick the directory name
    open the file from directory
    reading the xml data.
    and placing the data into TABLE.
    
    
    execute the procedure like below, in anonymous block
    
    begin
        sp_insert_xml_emps;
        commit;
    end;
    
    version is ORACLE 11i.
  • 4. Re: xml error whiclke inserting records
    Jason_(A_Non) Expert
    Currently Being Moderated
    version is ORACLE 11i.
    That's a marketing label. Your 4 digit version can be found via
    select * from v$version

    The documentation for [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions019.htm#SQLRF00610]BFILENAME states that the first parm is a directory object. Based off what you said, you are providing the server path instead. I doubt you have a directory object called "cheio/chennai/plo" defined in your system. You will need to create directory objects that reference the server paths. The BFILENAME documentation contains a reference to the [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_5007.htm#i2061958]CREATE DIRECTORY documentation. You would just need to modify
    get_handoff_path(51)
    to return the directory name instead of the path.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points