1 2 Previous Next 20 Replies Latest reply: Aug 22, 2012 1:20 AM by SnigdhaBhanu Go to original post RSS
      • 15. Re: XML 1header and n detail records
        SnigdhaBhanu
        Hi Friends,

        When am executing the procedure as below am getting the following errors

        Please help me how to rectify this..


        create or replace
        procedure create_customer_file_xml as

        doc clob;

        begin

             FOR r IN _(_
             select bix_customer_code,
             xmlserialize(document
             xmlelement("RecordType",
             xmlelement("Header",
             xmlforest( interface_type
             , partner_id
             , transfer_date
             , bix_customer_code
             , h_bix_article_num
             , uom
             , quantity_sold
             , batch_number )
             ),
             xmlagg(
             xmlelement("detail",
             xmlforest( item_type
             , l_bix_article_num
             , batch_number
             , usable_until
             , total_quantity
             , qc_status
             , manufacturing_date
             , expiry_date
             , xml_version )
             )
             )
             )
             as clob
             ) as xmldoc*
             from agg_apo_stock
             group by interface_type
             , partner_id
             , transfer_date
             , bix_customer_code
             , h_bix_article_num
             , uom
             , quantity_sold
             , batch_number
             )
             LOOP
             
        _     dbms_xslprocessor.clob2file(_
                  '<?xml version="1.0" encoding="UTF-8"?>' || r.xmldoc*
                  , 'MY_DIR'
                  , 'BIX_CUSTOMER_'||r.bix_customer_code||'.xml'
                  , nls_charset_id('AL32UTF8')
                  );
             END LOOP;

        end;


        Errors_

        Error(8,11): PL/SQL: SQL Statement ignored
        Error(37,7): PL/SQL: ORA-00942: table or view does not exist
        Error(50,3): PL/SQL: Statement ignored
        Error(51,47): PLS-00364: loop index variable 'R' use is invalid


        Analysis

        i tried to modify the braces and searched in internet..none is helpful.
        In the code i have underlined some lines...where the error is displayed.


        THank you.

        Edited by: user8595010 on Aug 12, 2012 9:42 PM
        • 16. Re: XML 1header and n detail records
          AlexAnd
          ok
          reproducing your error
          SQL> create table t (a number, b varchar2(10), c varchar2(10));
           
          Table created
           
          SQL> 
          SQL> insert into t values (1, 'qwe', 'qwe');
           
          1 row inserted
          SQL> insert into t values (1, 'asd', 'as');
           
          1 row inserted
          SQL> insert into t values (2, 'zxc', 'zxc');
           
          1 row inserted
          SQL> insert into t values (2, 'ert', 'ert');
           
          1 row inserted
           
          SQL> select * from t;
           
                   A B          C
          ---------- ---------- ----------
                   1 qwe        qwe
                   1 asd        as
                   2 zxc        zxc
                   2 ert        ert
           
          SQL> 
          SQL> select a, xmlserialize(document
            2                                  xmlelement("B-C-Rec",
            3                                             xmlelement("B-Rec",
            4                                                        xmlforest(b,
            5                                                                  a)),
            6                                             xmlagg(xmlelement("C-Rec",
            7                                                               xmlforest(c)))) as clob) as xmldoc
            8  from t
            9  group by b,c,a
           10  /
           
                   A XMLDOC
          ---------- --------------------------------------------------------------------------------
                   1 <B-C-Rec><B-Rec><B>asd</B><A>1</A></B-Rec><C-Rec><C>as</C></C-Rec></B-C-Rec>
                   2 <B-C-Rec><B-Rec><B>ert</B><A>2</A></B-Rec><C-Rec><C>ert</C></C-Rec></B-C-Rec>
                   1 <B-C-Rec><B-Rec><B>qwe</B><A>1</A></B-Rec><C-Rec><C>qwe</C></C-Rec></B-C-Rec>
                   2 <B-C-Rec><B-Rec><B>zxc</B><A>2</A></B-Rec><C-Rec><C>zxc</C></C-Rec></B-C-Rec>
           
          SQL> 
          SQL> declare
            2    doc clob;
            3  begin
            4  
            5    FOR r IN (select a, xmlserialize(document
            6                                  xmlelement("B-C-Rec",
            7                                             xmlelement("B-Rec",
            8                                                        xmlforest(b,
            9                                                                  a)),
           10                                             xmlagg(xmlelement("C-Rec",
           11                                                               xmlforest(c)))) as clob) as xmldoc
           12              from t
           13              group by b,c,a) LOOP
           14  
           15      dbms_xslprocessor.clob2file('<?xml version="1.0" encoding="UTF-8"?>' ||
           16                                    r.xmldoc,
           17                                    'MYDIR',
           18                                    'BIX_CUSTOMER_' || r.a ||
           19                                    '.xml',
           20                                    nls_charset_id('AL32UTF8'));
           21    END LOOP;
           22  
           23  end;
           24  /
           
          PL/SQL procedure successfully completed
           
          SQL> 
          SQL> declare
            2    doc clob;
            3  begin
            4  
            5    FOR r IN (select a, xmlserialize(document
            6                                  xmlelement("B-C-Rec",
            7                                             xmlelement("B-Rec",
            8                                                        xmlforest(b,
            9                                                                  a)),
           10                                             xmlagg(xmlelement("C-Rec",
           11                                                               xmlforest(c)))) as clob) as xmldoc
           12              from tt --<not table in db
           13              group by b,c,a) LOOP
           14  
           15      dbms_xslprocessor.clob2file('<?xml version="1.0" encoding="UTF-8"?>' ||
           16                                    r.xmldoc,
           17                                    'MYDIR',
           18                                    'BIX_CUSTOMER_' || r.a ||
           19                                    '.xml',
           20                                    nls_charset_id('AL32UTF8'));
           21    END LOOP;
           22  
           23  end;
           24  /
           
          declare
            doc clob;
          begin
          
            FOR r IN (select a, xmlserialize(document
                                          xmlelement("B-C-Rec",
                                                     xmlelement("B-Rec",
                                                                xmlforest(b,
                                                                          a)),
                                                     xmlagg(xmlelement("C-Rec",
                                                                       xmlforest(c)))) as clob) as xmldoc
                      from tt --< notexist
                      group by b,c,a) LOOP
          
              dbms_xslprocessor.clob2file('<?xml version="1.0" encoding="UTF-8"?>' ||
                                            r.xmldoc,
                                            'MYDIR',
                                            'BIX_CUSTOMER_' || r.a ||
                                            '.xml',
                                            nls_charset_id('AL32UTF8'));
            END LOOP;
          
          end;
           
          ORA-06550: line 13, column 18:
          PL/SQL: ORA-00942: table or view does not exist
          ORA-06550: line 6, column 12:
          PL/SQL: SQL Statement ignored
          ORA-06550: line 17, column 35:
          PLS-00364: loop index variable 'R' use is invalid
          ORA-06550: line 16, column 5:
          PL/SQL: Statement ignored
           
          SQL> 
          so your table agg_apo_stock isn't in your db/shema or may be you can't have permissions on it
          check it
          • 17. Re: XML 1header and n detail records
            SnigdhaBhanu
            Hi,

            Thank you. As per your suggestion i have created the directory.
            Am new to write procedures and execute them.

            Below is the procedure i have created in my database.

            Could you please let me know how to execute this.
            I have executed this as below
            EXECUTE CREATE_CUSTOMER_FILE_XML('global');
            Error PLS-00306: wrong number or types of arguments in call to 'CREATE_CUSTOMER_FILE_XML'

            Please let me know how to execute this procedure. thank you.

            PROCEDURE_


            create or replace procedure create_customer_file_xml as

            doc clob;

            begin

                 FOR r IN (
                 select bix_customer_code,
                 xmlserialize(document
                 xmlelement("RecordType",
                 xmlelement("Header",
                 xmlforest( interface_type
                 , partner_id
                 , transfer_date
                 , bix_customer_code
                 , h_bix_article_num
                 , uom
                 , quantity_sold
                 , batch_number )
                 ),
                 xmlagg(
                 xmlelement("detail",
                 xmlforest( item_type
                 , l_bix_article_num
                 , batch_number
                 , usable_until
                 , total_quantity
                 , qc_status
                 , manufacturing_date
                 , expiry_date
                 , xml_version )
                 )
                 )
                 )
                 as clob
                 ) as xmldoc
                 from agg_apo_stock
                 group by interface_type
                 , partner_id
                 , transfer_date
                 , bix_customer_code
                 , h_bix_article_num
                 , uom
                 , quantity_sold
                 , batch_number
                 )
                 LOOP
                 
                      dbms_xslprocessor.clob2file(
                      '<?xml version="1.0" encoding="UTF-8"?>' || r.xmldoc
                      , 'EP_DATA'
                      , 'BIX_CUSTOMER_'||r.bix_customer_code||'.xml'
                      , nls_charset_id('AL32UTF8')
                      );
                 END LOOP;

            end;
            • 18. Re: XML 1header and n detail records
              AlexAnd
              plz read some docs before all

              >
              EXECUTE CREATE_CUSTOMER_FILE_XML('global');
              Error PLS-00306: wrong number or types of arguments in call to 'CREATE_CUSTOMER_FILE_XML'

              Please let me know how to execute this procedure. thank you.

              PROCEDURE

              create or replace procedure create_customer_file_xml as

              doc clob;

              begin
              >
              you created procedure without parameters/arguments
              but
              when you want to execute it you pass value 'global'

              you can execute you procedure as
              begin
                create_customer_file_xml;
              end;
              plz read http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6009.htm
              • 19. Re: XML 1header and n detail records
                SnigdhaBhanu
                Hi,

                I have created and executed the procedure as per your suggestion.

                I have got only one record in XML.

                Requirement:

                All the records having Batch number with "Dummy" should be loaded in XML File.
                If Batch number is not Dummy then all the records should be loaded in another XML File.

                XML Format should be
                <header>
                <n detail records>
                <header>
                <n detail records>


                Procedure_

                Declare
                doc clob;

                begin

                     FOR r IN (
                     select batch_number,
                     xmlserialize(document
                     xmlelement("RecordType",
                     xmlelement("Header",
                     xmlforest( interface_type
                     , partner_id
                     , transfer_date
                     , bix_customer_code
                     , h_bix_article_num
                     , uom
                     , quantity_sold
                     , batch_number )
                     ),
                     xmlagg(
                     xmlelement("detail",
                     xmlforest( item_type
                     , l_bix_article_num
                     , batch_number
                     , usable_until
                     , total_quantity
                     , qc_status
                     , manufacturing_date
                     , expiry_date
                     , xml_version )
                     )
                     )
                     )
                     as clob
                     ) as xmldoc
                     from agg_apo_stock
                     group by interface_type
                     , partner_id
                     , transfer_date
                     , bix_customer_code
                     , h_bix_article_num
                     , uom
                     , quantity_sold
                     , batch_number
                     )
                     LOOP
                     
                          dbms_xslprocessor.clob2file(
                          '<?xml version="1.0" encoding="UTF-8"?>' || r.xmldoc
                          , 'EP_DATA'
                          , 'Batch_number_'||r.batch_number||'.xml'
                          , nls_charset_id('AL32UTF8')
                          );
                     END LOOP;
                end;


                Results*
                File Name: Batch_number_DUMMY.xml
                Data:
                <?xml version="1.0" encoding="UTF-8" ?>
                - <RecordType>
                - <Header>
                <INTERFACE_TYPE>DWLP</INTERFACE_TYPE>
                <PARTNER_ID>BICUSSA</PARTNER_ID>
                <TRANSFER_DATE>20120814</TRANSFER_DATE>
                <BIX_CUSTOMER_CODE>0001101437</BIX_CUSTOMER_CODE>
                <H_BIX_ARTICLE_NUM>920174</H_BIX_ARTICLE_NUM>
                <UOM>ST</UOM>
                <QUANTITY_SOLD>46</QUANTITY_SOLD>
                <BATCH_NUMBER>DUMMY</BATCH_NUMBER>
                </Header>
                - <detail>
                <ITEM_TYPE>P</ITEM_TYPE>
                <L_BIX_ARTICLE_NUM>920174</L_BIX_ARTICLE_NUM>
                <BATCH_NUMBER>DUMMY</BATCH_NUMBER>
                <USABLE_UNTIL>29993112</USABLE_UNTIL>
                <MANUFACTURING_DATE>19000101</MANUFACTURING_DATE>
                <EXPIRY_DATE>29993112</EXPIRY_DATE>
                <_xFFFF_XML_VERSION>000</_xFFFF_XML_VERSION>
                </detail>
                </RecordType>

                Thank you, Please help me in this regards.

                Snigdha.
                • 20. Re: XML 1header and n detail records
                  SnigdhaBhanu
                  Hi,

                  I have created and executed the procedure as per your suggestion.

                  I have got only one record in XML.

                  Requirement:_

                  All the records having Batch number with "Dummy" should be loaded in XML File.
                  If Batch number is not Dummy then all the records should be loaded in another XML File.

                  XML Format should be
                  <header>
                  <n detail records>
                  <header>
                  <n detail records>

                  Procedure_


                  Declare
                  doc clob;

                  begin

                  FOR r IN (
                  select batch_number,
                  xmlserialize(document
                  xmlelement("RecordType",
                  xmlelement("Header",
                  xmlforest( interface_type
                  , partner_id
                  , transfer_date
                  , bix_customer_code
                  , h_bix_article_num
                  , uom
                  , quantity_sold
                  , batch_number )
                  ),
                  xmlagg(
                  xmlelement("detail",
                  xmlforest( item_type
                  , l_bix_article_num
                  , batch_number
                  , usable_until
                  , total_quantity
                  , qc_status
                  , manufacturing_date
                  , expiry_date
                  , xml_version )
                  )
                  )
                  )
                  as clob
                  ) as xmldoc
                  from agg_apo_stock
                  group by interface_type
                  , partner_id
                  , transfer_date
                  , bix_customer_code
                  , h_bix_article_num
                  , uom
                  , quantity_sold
                  , batch_number
                  )
                  LOOP


                  dbms_xslprocessor.clob2file(
                  '<?xml version="1.0" encoding="UTF-8"?>' || r.xmldoc
                  , 'EP_DATA'
                  , 'Batch_number_'||r.batch_number||'.xml'
                  , nls_charset_id('AL32UTF8')
                  );
                  END LOOP;
                  end;


                  Results_

                  File Name: Batch_number_DUMMY.xml

                  Data:

                  <?xml version="1.0" encoding="UTF-8" ?>
                  - <RecordType>
                  - <Header>
                  <INTERFACE_TYPE>DWLP</INTERFACE_TYPE>
                  <PARTNER_ID>BICUSSA</PARTNER_ID>
                  <TRANSFER_DATE>20120814</TRANSFER_DATE>
                  <BIX_CUSTOMER_CODE>0001101437</BIX_CUSTOMER_CODE>
                  <H_BIX_ARTICLE_NUM>920174</H_BIX_ARTICLE_NUM>
                  <UOM>ST</UOM>
                  <QUANTITY_SOLD>46</QUANTITY_SOLD>
                  <BATCH_NUMBER>DUMMY</BATCH_NUMBER>
                  </Header>
                  - <detail>
                  <ITEM_TYPE>P</ITEM_TYPE>
                  <L_BIX_ARTICLE_NUM>920174</L_BIX_ARTICLE_NUM>
                  <BATCH_NUMBER>DUMMY</BATCH_NUMBER>
                  <USABLE_UNTIL>29993112</USABLE_UNTIL>
                  <MANUFACTURING_DATE>19000101</MANUFACTURING_DATE>
                  <EXPIRY_DATE>29993112</EXPIRY_DATE>
                  <_xFFFF_XML_VERSION>000</_xFFFF_XML_VERSION>
                  </detail>
                  </RecordType>

                  Thank you, Please help me in this regards.

                  Snigdha.
                  1 2 Previous Next