4 Replies Latest reply: Mar 9, 2012 10:25 AM by Jason_(A_Non) RSS

    xml error whiclke inserting records

    V prasad
      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
          at-least any links. to helpful.
          • 2. Re: xml error whiclke inserting records
            Jason_(A_Non)
            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
              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)
                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.