This discussion is archived
7 Replies Latest reply: Jul 2, 2012 7:36 AM by 943494 RSS

EXECUTE IMMEDIATE PASSING XML THROWS ERROR

943494 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thank You.

Legend

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