XPath with PL/SQL variables

Question

As a bloody newbie when it comes to complex sql commands, I want to execute two XPath database queries where the second command uses the result of the first one:

declare
version VARCHAR2(256);
document VARCHAR2(256);
begin

select ID_VERSION into version from VERSIONTABLE where extract(VERSION_XML, '//p:contract[@id="contract-001"]', 'xmlns:p="http://www.foo.com/v1"').getClobVal() IS NOT NULL;
select ID_DOCUMENT into document from DOCUMENTTABLE where extract(DOCUMENT_XML, '//p:content[@ref='$version']', 'xmlns:p="http://www.foo.com/v1"').getClobVal() IS NOT NULL;

end;
/

The first command will always return exactly one ID_VERSION. As a result, I want to print document.

The question: how do I properly include version in the second command? Executing above commands, it outputs following error at the code position of '$version':

PLS-00181: unsupported preprocessor directive '$VERSION' [SQL State=65000, DB Errorcode=6550]

Thank you!

Bob Jarvis - Слава Україні · Accepted Answer · 2014-10-24 19:35:27Z

1

The string concatenation operator in PL/SQL is || - so you'd want to do something like:

select ID_DOCUMENT
  into document
  from DOCUMENTTABLE
  where extract(DOCUMENT_XML,
                '//p:content[@ref=' || version || ']', 
                'xmlns:p="http://www.foo.com/v1"').getClobVal() IS NOT NULL;

Share and enjoy.

answered Oct 24, 2014 at 19:35

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

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

1 Comment

DanPlo Over a year ago

Thank you for your answer. It worked partly: the former error is gone of course, but for some reason it doesn't find any data: if I try version := 'version-123;

select ID_DOCUMENT into document from DOCUMENTTABLE where extract(DOCUMENT_XML, '//p:content[@ref=' || version || ']', 'xmlns:p="http://www.foo.com/v1"').getClobVal() IS NOT NULL;

the error is ORA-01403: no data found. However, there is data if I insert the string directly. Do you know the reason? Thanks in advance!

Collectives™ on Stack Overflow

XPath with PL/SQL variables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related