Unable to get the PL/SQL block to work [message #684643] |
Fri, 16 July 2021 11:50 |
buggleboy007
Messages: 282 Registered: November 2010 Location: Canada
|
Senior Member |
|
|
I have an issue where in I am unable to make the following PL/SQL block work. When I run the following block I get the message ORA-00947: not enough values.
What is that I am doing wrong here?
declare
-- Local variables here
l_string VARCHAR2(32767);
L_STRING_RESULT VARCHAR2(32767);
L_ROWCOUNT NUMBER(3);
i integer;
begin
-- Test statements here
/*
l_string:=q'('<Lines>
<line id="0" itemqty=" " skustyle=" " skudimension=" " skusize=" " skucolor=" "/>
<line id="1" itemqty="2" skustyle="style1" skudimension="12" skusize="XL" skucolor="34"/>
<line id="2" itemqty="5" skustyle="style2" skudimension="14" skusize="L" skucolor="40"/>
</Lines>')';
*/
l_string:='<Lines>
<line id="0" itemqty=" " skustyle=" " skudimension=" " skusize=" " skucolor=" "/>
<line id="1" itemqty="2" skustyle="style1" skudimension="12" skusize="XL" skucolor="34"/>
<line id="2" itemqty="5" skustyle="style2" skudimension="14" skusize="L" skucolor="40"/>
</Lines>';
dbms_output.PUT_LINE(L_STRING);
l_string_result:= l_string;
with t(dta) as (
select L_string
from dual
)
select lns.*
into L_STRING_RESULT
from t
cross join
xmltable('Lines/line'
passing xmltype(t.dta)
columns rn for ordinality
,id number path './@id'
,itemqty number path './@itemqty'
,skustyle varchar2(10) path './@skustyle'
,skudimension number path './@skudimension'
,skusize varchar2(4) path './@skusize'
,skucolor number path './@skucolor'
) lns;
end;
The same logic(query being a bit hardcoded) when executed at SQL prompt works well
with t(dta) as (
select '<Lines>
<line id="0" itemqty="" skustyle="" skudimension="" skusize="" skucolor=""/>
<line id="1" itemqty="2" skustyle="style1" skudimension="12" skusize="XL" skucolor="34"/>
<line id="2" itemqty="5" skustyle="style2" skudimension="14" skusize="L" skucolor="40"/>
</Lines>' from dual
)
select lns.*
from t
cross join
xmltable('Lines/line'
passing xmltype(t.dta)
columns rn for ordinality
,id number path './@id'
,itemqty number path './@itemqty'
,skustyle varchar2(10) path './@skustyle'
,skudimension number path './@skudimension'
,skusize varchar2(4) path './@skusize'
,skucolor number path './@skucolor'
) lns;
What is that I am doing wrong? Can any one of you help me?
|
|
|
|
|
|