7 Replies Latest reply: Jul 2, 2012 9:36 AM by 943494 RSS

    EXECUTE IMMEDIATE PASSING XML THROWS ERROR

    943494
      Oracle Database 10g 10.2.0.3.0

      Hi All,
      The below code( CODE 1) running fine without execute immediate.

      ----CODE 1----
      set serveroutput on;
      declare
      xml_in xmltype;
      sql_stmt varchar2(32000);
      v_ret_ac_no number;
      v_xpath varchar2(32000);
      begin
      xml_in := xmltype('<?xml version="1.0"?>
      <rowset>
      <row>
      <request>
      <ac_no>18343</ac_no>
      </request>
      </row>
      </rowset>');
      select distinct xtab.ac_no
      into v_ret_ac_no
      FROM DUAL,
      XMLTABLE('for $i in /rowset/row/request
      return $i'
      passing xml_in
      columns ac_no varchar2(20) path 'ac_no') xtab;

      dbms_output.put_line(v_ret_ac_no);
      end;

      If i try the same with execute immediate (CODE 2 ) as below then i am getting error

      ORA-06550: line 16, column 13:
      PLS-00306: wrong number or types of arguments in call to '||'

      Please help me to get out of this error i need execute immediate, Thanks. Oracle Database 10g 10.2.0.3.0

      ---CODE 2-----
      set serveroutput on;
      declare
      xml_in xmltype;
      sql_stmt varchar2(32000);
      v_ret_ac_no number;
      v_xpath varchar2(32000);
      begin
      xml_in := xmltype('<?xml version="1.0"?>
      <rowset>
      <row>
      <request>
      <ac_no>18343</ac_no>
      </request>
      </row>
      </rowset>');
      v_xpath := ''''||'for $i in rowset/row/request return $i'||'''';
      sql_stmt := 'select distinct xtab.ac_no
      FROM DUAL,
      XMLTABLE(' ||v_xpath||
      ' PASSING '||xml_in||
      ' columns ac_no varchar2(20) path ''ac_no'') xtab';
      execute immediate sql_stmt into v_ret_ac_no;
      dbms_output.put_line(v_ret_ac_no);
      end;

      Edited by: 940491 on Jul 1, 2012 11:51 AM
        • 1. Re: EXECUTE IMMEDIATE PASSING XML THROWS ERROR
          AlexAnd
          smth
          SQL> select * from v$version where rownum=1;
           
          BANNER
          ----------------------------------------------------------------
          Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
           
          SQL> -- code1
          SQL> 
          SQL> declare
            2    xml_in      xmltype;
            3    sql_stmt    varchar2(32000);
            4    v_ret_ac_no number;
            5    v_xpath     varchar2(32000);
            6  begin
            7    xml_in := xmltype('<?xml version="1.0"?>
            8  <rowset>
            9  <row>
           10  <request>
           11  <ac_no>18343</ac_no>
           12  </request>
           13  </row>
           14  </rowset>');
           15  
           16    select distinct xtab.ac_no
           17      into v_ret_ac_no
           18      FROM XMLTABLE('for $i in /rowset/row/request
           19                     return $i' passing xml_in
           20                     columns ac_no varchar2(20) path 'ac_no') xtab;
           21  
           22    dbms_output.put_line(v_ret_ac_no);
           23  end;
           24  /
           
          18343
           
          PL/SQL procedure successfully completed
           
          SQL>  
          SQL> -- code2
          SQL> 
          SQL> declare
            2    xml_in      xmltype;
            3    sql_stmt    varchar2(32000);
            4    v_ret_ac_no number;
            5    v_xpath     varchar2(32000);
            6  begin
            7    xml_in := xmltype('<?xml version="1.0"?>
            8  <rowset>
            9  <row>
           10  <request>
           11  <ac_no>18343</ac_no>
           12  </request>
           13  </row>
           14  </rowset>');
           15  
           16    v_xpath := '''' || 'for $i in rowset/row/request return $i' || '''';
           17  
           18    sql_stmt := 'select distinct xtab.ac_no' || ' FROM XMLTABLE(' ||
           19                v_xpath ||
           20                ' passing :xml_in ' ||
           21                ' columns ac_no varchar2(20) path ''ac_no'') xtab';
           22  
           23    execute immediate sql_stmt
           24      into v_ret_ac_no
           25      using xml_in;
           26  
           27    dbms_output.put_line(v_ret_ac_no);
           28  
           29  end;
           30  /
           
          18343
           
          PL/SQL procedure successfully completed
           
          SQL> 
          • 2. Re: EXECUTE IMMEDIATE PASSING XML THROWS ERROR
            odie_63
            It won't work if there are more than 1 distinct values.

            You need to bulk collect into a collection, or use a REF CURSOR to loop through the result set in this case.
            • 3. Re: EXECUTE IMMEDIATE PASSING XML THROWS ERROR
              943494
              Thanks Alex, Odie.

              If the in_xml is coming as a IN parameter through a function also need bulk collect / ref cursor? If possible with my code can you please show me an example. Thanks
              • 4. Re: EXECUTE IMMEDIATE PASSING XML THROWS ERROR
                odie_63
                If the in_xml is coming as a IN parameter through a function also need bulk collect / ref cursor?
                I fail to see a connection between the two.

                This is a simple issue : based on what you know about the data, can the query return more than one row or not?

                - If not, then it's OK like it is now (you could even use ROWNUM = 1 instead of DISTINCT)

                - If yes, then it won't work, you'll get a TOO_MANY_ROWS exception.

                The solution is to use a cursor (REF CURSOR since you decided to go dynamic) or a BULK COLLECT clause to fetch the whole result set into a collection.
                • 5. Re: EXECUTE IMMEDIATE PASSING XML THROWS ERROR
                  943494
                  Thanks odie,
                  one more query please.
                  my database version 10.2.0.3.0 no binary storage as you always suggest. we will go for it next year it seems. until no way.

                  i have a function which will receive xmltype data as IN parameter.
                  i will process these xml data using xmltable and insert into tables.

                  i have two ways to process.

                  1) store the IN parameter data into a table; column defined as xmltype and query from the table.

                  SQL_STMT := ' SELECT ' ||V_XTAB_COLUMN_NAME ||
                  ' XML_STORED_TABLE, ----table where xmldata storing
                  XMLTABLE(' ||v_xpath||
                  'PASSING XML_VALUE ---column defined as xmltype columns ' || V_COLUMNS_DATA_TYPE
                  ||') XTAB
                  WHERE SEQUENCE_NO = ' || P_SEQUENCE_NO ;

                  EXECUTE IMMEDIATE SQL_STMT


                  2)No query from table instead process the IN parameter as a PL/SQL xml type. like
                  SQL_STMT := ' SELECT ' ||v_xtab_column_name ||
                  ' FROM XMLTABLE(' ||v_xpath||
                  ' passing :p_xml_value '||
                  ' columns ' || V_COLUMNS_DATA_TYPE
                  ||') XTAB'
                  EXECUTE IMMEDIATE SQL_STMT using p_xml_value;

                  found out that querying from table is always fast. can you please suggest me which way to follow. Thanks Rubu

                  Edited by: 940491 on Jul 2, 2012 7:34 AM
                  • 6. Re: EXECUTE IMMEDIATE PASSING XML THROWS ERROR
                    odie_63
                    found out that querying from table is always fast. can you please suggest me which way to follow.
                    Yes, proceed with method #1, especially if you're going to query the same XML multiple times.
                    • 7. Re: EXECUTE IMMEDIATE PASSING XML THROWS ERROR
                      943494
                      Thank You.