Home » SQL & PL/SQL » SQL & PL/SQL » Unable to get the PL/SQL block to work (Oracle 12c, O/S: Windows 10 pro)
Unable to get the PL/SQL block to work [message #684643] Fri, 16 July 2021 11:50 Go to next message
buggleboy007
Messages: 278
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?
Re: Unable to get the PL/SQL block to work [message #684646 is a reply to message #684643] Fri, 16 July 2021 15:35 Go to previous messageGo to next message
buggleboy007
Messages: 278
Registered: November 2010
Location: Canada
Senior Member
I fixed it myself. Thank you.
Re: Unable to get the PL/SQL block to work [message #684647 is a reply to message #684646] Sat, 17 July 2021 00:11 Go to previous messageGo to next message
Michel Cadot
Messages: 67991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OraFAQ Forum Guide, #12. f you found an answer yourself, post it. That way we know the issue is resolved and we might learn from it.



Re: Unable to get the PL/SQL block to work [message #684657 is a reply to message #684647] Sat, 17 July 2021 12:29 Go to previous message
buggleboy007
Messages: 278
Registered: November 2010
Location: Canada
Senior Member
Michel Cadot wrote on Sat, 17 July 2021 00:11

OraFAQ Forum Guide, #12. f you found an answer yourself, post it. That way we know the issue is resolved and we might learn from it.

https://community.oracle.com/tech/developers/discussion/4486850/question-regarding-breaking-3-lines-into-individual-table-rows#latest

Read the entire thread, particularly the hybrid approach that I took and the errors that followed post hybrid approach + steps taken to fix that. That's the answer.
Previous Topic: Issues with my Associative Array inside a Package spec
Next Topic: get the record where the amount columns are same in 2 tables
Goto Forum:
  


Current Time: Sun Oct 17 21:27:57 CDT 2021