oracle aide

August 10, 2007

The “Missing IN or OUT parameter at index:: 1” error in DBMS_XMLQuery

Filed under: XSU — oracleaide @ 2:44 pm

Surprisingly enough the error means what it says – a missing bind variable.
Not excusing the weird behavior of the (otherwise lovely) DBMS_XMLQuery package.

This query works fine in my Toad, in spite of the unusual place for a bind variable.


select :r_max from dual where rownum <= :r_max

If I try to use DBMS_XMLQuery to turn the query results into XML I get an error.


declare
  ctx dbms_xmlquery.ctxHandle;
begin
  ctx := dbms_xmlquery.newContext(
    'select :r_max from dual where rownum <= :r_max');
  dbms_xmlquery.setBindValue(ctx, 'r_max', 10);
  dbms_output.put_line(dbms_xmlquery.getxml(ctx));
  dbms_xmlquery.closeContext(ctx);
end;

The error is:


oracle.xml.sql.OracleXMLSQLException: Missing IN or OUT parameter at index:: 1


At the same time the DBMS_XMLGen package processes the same statement without any problems.


declare
  ctx dbms_xmlgen.ctxHandle;
begin
  ctx := dbms_xmlgen.newContext(
    'select :r_max from dual where rownum <= :r_max');
  dbms_xmlgen.setBindValue(ctx, 'r_max', 10);
  dbms_output.put_line(dbms_xmlgen.getxml(ctx));
  dbms_xmlgen.closeContext(ctx);
end;

The remedy is simple – an extra bind variable.


declare
  ctx dbms_xmlquery.ctxHandle;
begin
  ctx := dbms_xmlquery.newContext(
    'select :r_max1 from dual where rownum <= :r_max');
  dbms_xmlquery.setBindValue(ctx, 'r_max', 10);
  dbms_xmlquery.setBindValue(ctx, 'r_max1', 10);
  dbms_output.put_line(dbms_xmlquery.getxml(ctx));
  dbms_xmlquery.closeContext(ctx);
end;

But why?

The solution credit goes to michaels from OTN forums.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: