Oracle XMLQuery passing parameter value

Question

Need help in my XMLQuery.

getting the loop seq

SELECT extractvalue(update_xmldoc, 'count(/invoice/AR_ITEMS/ITEMS[NAME="Voice" and ITEM_TOTAL!=0])')
    INTO item_check
    FROM xml_billrun_files
    WHERE seq_id = seq
    AND docname  = REPLACE(fn,'.','_HULK.')
  ORDER BY TIMESTAMP DESC;

then item_check value will pass to the loop

FOR lp IN 1..item_check LOOP
      SELECT tst, bill_info_id, bill_no, dur_net_amt, dur_vat_amt, dur_pro_net_amt, dur_pro_vat_amt, sys_descr
      INTO xmlU, bp, bill, net_amt1, vat_amt1, net_amt2, vat_amt2, disc_name
      FROM
        (SELECT row_number() over (partition BY t.docname order by t.timestamp DESC) rn,
          t.docname,
          XMLQuery('for $i in distinct-values(/invoice/AR_ITEMS['||lp||']/ITEMS/EVENTS/BAL_IMPACTS/DISCOUNT_INFO)
                    where $i = "Plan499 Corp Disc" 
                    or $i = "Plan899 Corp Disc" 
                    or $i = "Plan1099 Corp Disc" 
                    or $i = "Plan1599 Corp Disc"
                    return $i' passing original_xmldoc returning content ).getStringVal() sys_descr

i just post a part of my script.

the script is good but when i pass the value of lp inside the XMLQuery i get the error

Error(528,66): PL/SQL: ORA-19109: RETURNING keyword expected

Okay, so below is an example of my script.

DECLARE
item_check NUMBER;
fn VARCHAR2(100) := 'test.xml';
seq NUMBER := 1;
BEGIN

SELECT extractvalue(update_xmldoc, 'count(/invoice/AR_ITEMS/ITEMS[NAME="Voice" and ITEM_TOTAL!=0])')
    INTO item_check
    FROM xml_billrun_files
    WHERE seq_id = seq
    AND docname  = REPLACE(fn,'.','_HULK.')
    ORDER BY TIMESTAMP DESC;

    IF item_check <> 0 THEN
    FOR lp IN 1..item_check LOOP
      SELECT tst
      INTO xmlU
      FROM
        (SELECT row_number() over (partition BY t.docname order by t.timestamp DESC) rn,
          t.docname,
          XMLQuery('for $i in distinct-values(/invoice/AR_ITEMS['||lp||']/ITEMS/EVENTS/BAL_IMPACTS/DISCOUNT_INFO)
                    where $i = "Plan499 Corp Disc" 
                    or $i = "Plan899 Corp Disc" 
                    or $i = "Plan1099 Corp Disc" 
                    or $i = "Plan1599 Corp Disc"
                    return $i' passing original_xmldoc returning content ).getStringVal() sys_descr,
          SUM(SUBSTR(x.chrg_duration,1,instr(x.chrg_duration,':',1)-1)) over (partition BY t.docname) chrg_duration,
          CASE
            WHEN extractvalue(xmltype('<DISCOUNT_INFO>'||XMLQuery('for $i in distinct-values(/invoice/AR_ITEMS/ITEMS/EVENTS/BAL_IMPACTS/DISCOUNT_INFO)
                              where $i = "Plan499 Corp Disc" 
                              or $i = "Plan899 Corp Disc" 
                              or $i = "Plan1099 Corp Disc" 
                              or $i = "Plan1599 Corp Disc"
                              return $i' passing original_xmldoc returning content )||'</DISCOUNT_INFO>'),'DISCOUNT_INFO') = 'Plan499 Corp Disc' AND SUM(SUBSTR(x.chrg_duration,1,instr(x.chrg_duration,':',1)-1)) over (partition BY t.docname) <= prorate_mins
            THEN insertchildxml(
                  insertchildxml(
                    updatexml(
                      updatexml(
                        updatexml(t.update_xmldoc,'/invoice/BILLINFO/TOTAL_DUE/text()', 
                        extractvalue(t.update_xmldoc,'/invoice/BILLINFO/TOTAL_DUE') - SUM(x.amount) over (partition BY t.docname)),'/invoice/BILLINFO/CURRENT_TOTAL/text()', 
                      extractvalue(t.update_xmldoc,'/invoice/BILLINFO/CURRENT_TOTAL') - (SUM(x.amount) over (partition BY t.docname))),'/invoice/BILLINFO/DISCOUNT_VALUE', 
                    xmltype('<DISCOUNT_VALUE>'||(NVL(extractvalue(t.update_xmldoc,'/invoice/BILLINFO/DISCOUNT_VALUE'),0) - ROUND((SUM(x.amount) over (partition BY t.docname))/1.12,2))||'</DISCOUNT_VALUE>')), '/invoice/AR_ITEMS/ITEMS[NAME="Voice"]/USAGE_RECORDS[STREAM_NAME="NDD"]', 'DURATION_NET_DISCOUNT', 
                xmlelement("DURATION_NET_DISCOUNT", ROUND((SUM(x.amount) over (partition BY t.docname)*-1)/1.12,2))),'/invoice/AR_ITEMS/ITEMS[NAME="Voice"]/USAGE_RECORDS[STREAM_NAME="NDD"]','DURATION_VAT_DISCOUNT',
              xmlelement("DURATION_VAT_DISCOUNT",ROUND(((SUM(x.amount) over (partition BY t.docname)*-1)/1.12)*.12,2)))
              END tst
          FROM xml_billrun_files t ,
          xmltable('/invoice/AR_ITEMS/ITEMS/USAGE_RECORDS/SESSION_INFO' 
                    passing t.update_xmldoc 
                    columns chrg_duration VARCHAR2(20) path 'DURATION', 
                            amount NUMBER path 'AMOUNT') x
        WHERE seq_id  = seq
        AND t.docname = REPLACE(fn,'.','_HULK.')
        )
      WHERE rn = 1;
      UPDATE xml_billrun_files a
      SET update_xmldoc = xmlU
      WHERE seq_id      = seq
      AND docname       = REPLACE(fn,'.','_HULK.');
      COMMIT;
    END LOOP;
    END IF;
END;

If you don't share the whole thing it will be challenging for anyone to help you. Ideally, you should present a small worked example that demonstrates the problem in isolation, without us needing to understand the domain. — ninesided
– ninesided, Commented Feb 26, 2016 at 8:45

Alex Poole · Accepted Answer · 2016-02-26 12:02:16Z

4

The problem is the string concatenation in the XPath. It doesn't like you doing ... AR_ITEMS['||lp||']/ITEMS ....

You can pass the value of your PL/SQL lp variable in through the passing clause, which allows multiple comma-separated arguments; give it an identifier and then refer to that directly in the XPath. I've stuck used "lp" and $lp; they don't have to match the PL/SQL variable name, but it might be clearer if they do.

      XMLQuery('for $i in distinct-values(/invoice/AR_ITEMS[$lp]/ITEMS/EVENTS/BAL_IMPACTS/DISCOUNT_INFO)
                where $i = "Plan499 Corp Disc" 
                or $i = "Plan899 Corp Disc" 
                or $i = "Plan1099 Corp Disc" 
                or $i = "Plan1599 Corp Disc"
                return $i' passing original_xmldoc, cast(lp as number) as "lp" returning content ).getStringVal() sys_descr,

The PL/SQL loop index variable lp is a pls_integer which the passing clause doesn't like; if you pass that directly you get ORA-00932, so you need to cast it to the number data type.

edited Feb 26, 2016 at 12:02

answered Feb 26, 2016 at 9:35

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

NelzKi Over a year ago

I did that, when passing the lp value i get in the FOR statement, it returns Error(535,57): PL/SQL: ORA-00932: inconsistent datatypes: expected - got NATIVE INTEGER

Alex Poole Over a year ago

@NelzKi - I've added an xmltype(lp) call. Can you add a sample XML document to the question? It would be easier to test with some data. Could also possibly then suggest alternatives to the deprecated functions you're using.

Alex Poole Over a year ago

I've fixed this to cast the loop variable; the value is actually used now.

Collectives™ on Stack Overflow

Oracle XMLQuery passing parameter value

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related