6 Replies Latest reply: Jul 3, 2012 5:09 PM by Jason_(A_Non) RSS

    can't read XML

    user12144220
      Hello everyone,

      I'm trying to insert rows reading from xml string. I created a stored procedure as following. But it is created but with compilation errors. Could you, please, check the errors? And what is the best way for bulk inserting? If there is better way than below one, could you provide me a sample, please?

      My oracle database version is 10g, 11g.

      CREATE OR REPLACE PROCEDURE InsertFinData(V_FINDTL VARCHAR2) AS
      XMLType SYS.XMLTYPE;
      BEGIN

      XMLType:=sys.xmltype.createXML(V_FINDTL);

      FOR FIN IN
      (
      FINANCIALDATADETAIL.EXTRACT ('//FINANCIALDATADETAIL/FK_GROUPID/text()').getstringval() AS V_FK_GROUPID,
      FINANCIALDATADETAIL.EXTRACT ('//FINANCIALDATADETAIL/FK_COLUMNID/text()').getstringval() AS V_FK_COLUMNID,
      FINANCIALDATADETAIL.EXTRACT ('//FINANCIALDATADETAIL/FK_COMPANYREGID/text()').getstringval() AS V_FK_COMPANYREGID,
      FINANCIALDATADETAIL.EXTRACT ('//FINANCIALDATADETAIL/FYear/text()').getstringval() AS V_FISCALYEAR,
      FINANCIALDATADETAIL.EXTRACT ('//FINANCIALDATADETAIL/FPeriod/text()').getstringval() AS V_ACCOUNTINGPRD,
      FINANCIALDATADETAIL.EXTRACT ('//FINANCIALDATADETAIL/FK_REGIONID/text()').getstringval() AS V_FK_REGION,
      FINANCIALDATADETAIL.EXTRACT ('//FINANCIALDATADETAIL/FK_PERIODID/text()').getstringval() AS V_FK_PERIODID,
      FINANCIALDATADETAIL.EXTRACT ('//FINANCIALDATADETAIL/FK_AMOUNT/text()').getstringval() AS V_AMOUNT,
      FINANCIALDATADETAIL.EXTRACT ('//FINANCIALDATADETAIL/FK_REPORTID/text()').getstringval() AS V_FK_REPORTID,
      FINANCIALDATADETAIL.EXTRACT ('//FINANCIALDATADETAIL/FK_ROWID/text()').getstringval() AS V_FK_ROWID,
      FINANCIALDATADETAIL.EXTRACT ('//FINANCIALDATADETAIL/FK_ACCOUNTANTID/text()').getstringval() AS V_FK_ACCOUNTANTID
      FROM
      TABLE (XMLSEQUENCE (XMLType.EXTRACT('//FINANCIALDATA/FINANCIALDATADETAIL')))FINANCIALDATADETAIL
      )

      LOOP
      INSERT INTO TBLFINANCIALDATA(FK_GROUPID, FK_COLUMNID, FK_COMPANYREGID, FK_REGION, FK_PERIODID, ENTRYDATE, AMOUNT, FK_REPORTID, FK_ROWID, FK_ACCOUNTANTID, FISCALYEAR, ACCOUNTINGPRD) VALUES(V_FK_GROUPID, V_FK_COLUMNID, V_FK_COMPANYREGID, V_FK_REGION, V_FK_PERIODID, V_ENTRYDATE, V_AMOUNT, V_FK_REPORTID, V_FK_ROWID, V_FK_ACCOUNTANTID, V_FISCALYEAR, V_ACCOUNTINGPRD);
      COMMIT;
      END LOOP;
      END;


      My original xml is:

      {<FINANCIALDATA>
      <FINANCIALDATADETAIL>
      <FK_GROUPID>1</FK_GROUPID>
      <FK_COLUMNID>1</FK_COLUMNID>
      <FK_COMPANYREGID>2006040592491</FK_COMPANYREGID>
      <FYear>2011</FYear>
      <FPeriod>4</FPeriod>
      <FK_REGIONID>2006010700152</FK_REGIONID>
      <FK_PERIODID>4</FK_PERIODID>
      <AMOUNT>-229.3</AMOUNT>
      <FK_REPORTID>4</FK_REPORTID>
      <FK_ROWID>128</FK_ROWID>
      <FK_ACCOUNTANTID>06</FK_ACCOUNTANTID>
      </FINANCIALDATADETAIL>
      <FINANCIALDATADETAIL>
      <FK_GROUPID>1</FK_GROUPID>
      <FK_COLUMNID>1</FK_COLUMNID>
      <FK_COMPANYREGID>2006040592491</FK_COMPANYREGID>
      <FYear>2011</FYear>
      <FPeriod>4</FPeriod>
      <FK_REGIONID>2006010700152</FK_REGIONID>
      <FK_PERIODID>4</FK_PERIODID>
      <AMOUNT>770.7</AMOUNT>
      <FK_REPORTID>1</FK_REPORTID>
      <FK_ROWID>3</FK_ROWID>
      <FK_ACCOUNTANTID>06</FK_ACCOUNTANTID>
      </FINANCIALDATADETAIL>
      </FINANCIALDATA>}


      Thank you.
        • 1. Re: can't read XML
          odie_63
          Hi,
          But it is created but with compilation errors. Could you, please, check the errors?
          What errors are you getting? Why not giving them in the first place?
          We obviously can't reproduce your problem since we're missing the DDL for the table.

          That being said, it's not hard to immediately spot a few ones :

          - No SELECT in the FOR query
          - "XMLType" is a very bad choice for a variable name
          - Columns from the implicit cursor FOR-LOOP must be referenced through the record alias, e.g. FIN.V_FK_GROUPID
          - You're trying to insert a value you don't extract : V_ENTRYDATE
          - etc.
          And what is the best way for bulk inserting?
          Certainly not a PL/SQL FOR-LOOP with a COMMIT inside it.

          A straightforward INSERT SELECT will do just fine (adjust the datatypes if necessary) :
          INSERT INTO TBLFINANCIALDATA
          (FK_GROUPID, FK_COLUMNID, FK_COMPANYREGID, FK_REGION, FK_PERIODID, AMOUNT, FK_REPORTID, FK_ROWID, FK_ACCOUNTANTID, FISCALYEAR, ACCOUNTINGPRD) 
          SELECT FK_GROUPID, FK_COLUMNID, FK_COMPANYREGID, FK_REGION, FK_PERIODID, AMOUNT, FK_REPORTID, FK_ROWID, FK_ACCOUNTANTID, FISCALYEAR, ACCOUNTINGPRD
          FROM XMLTable(
                 '/FINANCIALDATA/FINANCIALDATADETAIL'
                 passing xmltype(V_FINDTL)
                 columns FK_GROUPID       number        path 'FK_GROUPID' 
                       , FK_COLUMNID      number        path 'FK_COLUMNID'
                       , FK_COMPANYREGID  varchar2(15)  path 'FK_COMPANYREGID'
                       , FK_REGION        varchar2(15)  path 'FK_REGIONID'
                       , FK_PERIODID      number        path 'FK_PERIODID'   
                       , AMOUNT           number        path 'AMOUNT'
                       , FK_REPORTID      number        path 'FK_REPORTID'
                       , FK_ROWID         number        path 'FK_ROWID'
                       , FK_ACCOUNTANTID  varchar2(30)  path 'FK_ACCOUNTANTID'
                       , FISCALYEAR       number        path 'FYear'
                       , ACCOUNTINGPRD    number        path 'FPeriod'
               ) x
          ;
          • 2. Re: can't read XML
            user12144220
            Thank you for your quick help.

            As you said, I was trying to insert V_ENTRYDATE I didn't extract. I fixed it.

            When I creata the stored procedure it says "Warning: compiled but with compilation errors". Could you provide me a stored procedure if possible?

            The table creation script is :

            CREATE TABLE PROD.TBLFINANCIALDATA
            (
            FK_GROUPID INTEGER,
            FK_COLUMNID INTEGER,
            FK_COMPANYREGID NVARCHAR2(20),
            FK_REGIONID INTEGER,
            FK_PERIODID INTEGER,
            ENTRYDATE DATE,
            AMOUNT NUMBER(19,2),
            FK_REPORTID INTEGER,
            FK_ROWID INTEGER,
            FK_ACCOUNTANTID NVARCHAR2(30),
            FISCALYEAR INTEGER,
            ACCOUNTINGPRD INTEGER,
            FK_REGION NVARCHAR2(30)
            )
            TABLESPACE PROD
            PCTUSED 0
            PCTFREE 10
            INITRANS 1
            MAXTRANS 255
            STORAGE (
            INITIAL 64K
            NEXT 1M
            MINEXTENTS 1
            MAXEXTENTS UNLIMITED
            PCTINCREASE 0
            BUFFER_POOL DEFAULT
            )
            NOLOGGING
            NOCOMPRESS
            NOCACHE
            NOPARALLEL
            MONITORING;




            Thank you for your help.
            • 3. Re: can't read XML
              odie_63
              Please post what you've tried, along with the error message.

              You can check compilation errors with the SHOW ERRORS command in SQL*Plus, or by querying USER_ERRORS view.
              • 4. Re: can't read XML
                user12144220
                I changed my procedure to :

                CREATE OR REPLACE PROCEDURE PROD.InsertFinancialData(V_FINDTL VARCHAR2) AS
                XMLType SYS.XMLTYPE;
                BEGIN

                XMLType:=sys.xmltype.createXML(V_FINDTL);

                FOR FIN IN
                (
                FINANCIALDATADETAIL.EXTRACT ('//FINANCIALDATADETAIL/FK_GROUPID/text()').getstringval() AS V_FK_GROUPID,
                FINANCIALDATADETAIL.EXTRACT ('//FINANCIALDATADETAIL/FK_COLUMNID/text()').getstringval() AS V_FK_COLUMNID,
                FINANCIALDATADETAIL.EXTRACT ('//FINANCIALDATADETAIL/FK_COMPANYREGID/text()').getstringval() AS V_FK_COMPANYREGID,
                FINANCIALDATADETAIL.EXTRACT ('//FINANCIALDATADETAIL/FYear/text()').getstringval() AS V_FISCALYEAR,
                FINANCIALDATADETAIL.EXTRACT ('//FINANCIALDATADETAIL/FPeriod/text()').getstringval() AS V_ACCOUNTINGPRD,
                FINANCIALDATADETAIL.EXTRACT ('//FINANCIALDATADETAIL/FK_REGIONID/text()').getstringval() AS V_FK_REGION,
                FINANCIALDATADETAIL.EXTRACT ('//FINANCIALDATADETAIL/FK_PERIODID/text()').getstringval() AS V_FK_PERIODID,
                FINANCIALDATADETAIL.EXTRACT ('//FINANCIALDATADETAIL/FK_AMOUNT/text()').getstringval() AS V_AMOUNT,
                FINANCIALDATADETAIL.EXTRACT ('//FINANCIALDATADETAIL/FK_REPORTID/text()').getstringval() AS V_FK_REPORTID,
                FINANCIALDATADETAIL.EXTRACT ('//FINANCIALDATADETAIL/FK_ROWID/text()').getstringval() AS V_FK_ROWID,
                FINANCIALDATADETAIL.EXTRACT ('//FINANCIALDATADETAIL/FK_ACCOUNTANTID/text()').getstringval() AS V_FK_ACCOUNTANTID,
                FINANCIALDATADETAIL.EXTRACT ('//FINANCIALDATADETAIL/ENTRYDATE/text()').getstringval() AS V_ENTRYDATE
                FROM
                TABLE (XMLSEQUENCE (XMLType.EXTRACT('//FINANCIALDATA/FINANCIALDATADETAIL')))FINANCIALDATADETAIL
                )

                LOOP
                INSERT INTO TBLFINANCIALDATA(FK_GROUPID, FK_COLUMNID, FK_COMPANYREGID, FK_REGION, FK_PERIODID, ENTRYDATE, AMOUNT, FK_REPORTID, FK_ROWID, FK_ACCOUNTANTID, FISCALYEAR, ACCOUNTINGPRD) VALUES(V_FK_GROUPID, V_FK_COLUMNID, V_FK_COMPANYREGID, V_FK_REGION, V_FK_PERIODID, V_ENTRYDATE, V_AMOUNT, V_FK_REPORTID, V_FK_ROWID, V_FK_ACCOUNTANTID, V_FISCALYEAR, V_ACCOUNTINGPRD);
                END LOOP;
                END;
                /


                And when I execute it, it says me "Warning: Procedure created with compilation errors.". Error is:

                LINE/COL ERROR
                -------- -----------------------------------------------------------------
                9/111 PLS-00103: Encountered the symbol "," when expecting one of the
                following:
                . ) @ %

                10/97 PLS-00103: Encountered the symbol "AS" when expecting one of the
                following:
                . ( ) , * % & = - + < / > at in is mod remainder not rem =>
                <an exponent (**)> <> or != or ~= >= <= <> and or like like2
                like4 likec between || multiset member submultiset

                21/5 PLS-00103: Encountered the symbol "FROM" when expecting one of

                LINE/COL ERROR
                -------- -----------------------------------------------------------------
                the following:
                , into bulk

                23/8 PLS-00103: Encountered the symbol ")" when expecting one of the
                following:
                ; return returning where

                Thank you.
                • 5. Re: can't read XML
                  user12144220
                  And I've created a stored procedure as you recommended as following:

                  CREATE OR REPLACE PROCEDURE PROD.InsertFData(V_FINDTL VARCHAR2) AS
                  XMLType SYS.XMLTYPE;
                  BEGIN
                  INSERT INTO TBLFINANCIALDATA
                  (FK_GROUPID, FK_COLUMNID, FK_COMPANYREGID, FK_REGION, FK_PERIODID, AMOUNT, FK_REPORTID, FK_ROWID, FK_ACCOUNTANTID, FISCALYEAR, ACCOUNTINGPRD, ENTRYDATE)
                  SELECT FK_GROUPID, FK_COLUMNID, FK_COMPANYREGID, FK_REGION, FK_PERIODID, AMOUNT, FK_REPORTID, FK_ROWID, FK_ACCOUNTANTID, FISCALYEAR, ACCOUNTINGPRD, ENTRYDATE
                  FROM XMLTable(
                  '/FINANCIALDATA/FINANCIALDATADETAIL'
                  passing xmltype(V_FINDTL)
                  columns FK_GROUPID number path 'FK_GROUPID'
                  , FK_COLUMNID number path 'FK_COLUMNID'
                  , FK_COMPANYREGID varchar2(15) path 'FK_COMPANYREGID'
                  , FK_REGION varchar2(15) path 'FK_REGIONID'
                  , FK_PERIODID number path 'FK_PERIODID'
                  , AMOUNT number path 'AMOUNT'
                  , FK_REPORTID number path 'FK_REPORTID'
                  , FK_ROWID number path 'FK_ROWID'
                  , FK_ACCOUNTANTID varchar2(30) path 'FK_ACCOUNTANTID'
                  , FISCALYEAR number path 'FYear'
                  , ACCOUNTINGPRD number path 'FPeriod'
                  , ENTRYDATE date path 'EntryDate'
                  ) x
                  ;

                  END;
                  /

                  And I'm still having issues with create stored procedure without any errors. When I try to create above stored procedure, it says following:


                  LINE/COL ERROR
                  -------- -----------------------------------------------------------------
                  4/1 PL/SQL: SQL Statement ignored
                  9/16 PL/SQL: ORA-00904: : invalid identifier
                  9/16 PLS-00222: no function with name 'XMLTYPE' exists in this scope

                  Could you, please, help me on that.

                  Thank you.

                  Edited by: user12144220 on Jul 3, 2012 4:47 AM

                  Edited by: user12144220 on Jul 3, 2012 4:50 AM
                  • 6. Re: can't read XML
                    Jason_(A_Non)
                    I'll repeat something that odie said above
                    - "XMLType" is a very bad choice for a variable name
                    The variable name is the reason your program will not compile. The xmltype() in the SQL statement is trying to reference the variable name. If you were to rename it to a better choice, the procedure would compile. As you don't even use that variable, you could safely delete it.

                    This is why you should not use names that Oracle uses. It has strange side-effects.