Forum Stats

  • 3,815,823 Users
  • 2,259,094 Discussions
  • 7,893,259 Comments

Discussions

Categories

Xquery function in pl sql

749391
749391 Member Posts: 32
edited Mar 2, 2010 2:20PM in SQL & PL/SQL
Hi all.

Is some body know can XQuery functions be used/declared in pl sql? More deep, can I cover XQuery function by pl sql function or procedure? And how to input parameters/variables to Xquery function from pl sql?

Any links to manual will be helpful.

Best regards,
Anton.
· Share on TwitterShare on Facebook

Best Answer

  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    Answer ✓
    Something like that?:
    SQL> create or replace procedure index_scan (index_name varchar2, name varchar2)
as
   xml   xmltype;
begin
   select xmlquery (
             '
if ("article-by-title" = $index_name) then doc("/wiki")/root/page[title=$name]
else if ("article-by-id" = $index_name) then doc("/wiki")/root/page[id=$name]
else if ("link-by-target" = $index_name) then doc("/wiki")/root/page/links/link[@target = $name]
else if ("article-by-cat" = $index_name) then doc("/wiki")/root/page/catlinks/catlink[@target=$name]
else error(fn:QName("http://www.w3.org/2005/xqt-errors",  $index_name) , "Some error occured") '
             passing index_name as "index_name", name as "name"
             returning content)
     into xml
     from dual;
end index_scan;
/
Procedure created.

SQL> exec index_scan('My Index Name','My Name')
BEGIN index_scan('My Index Name','My Name'); END;
Error at line 20
ORA-19112: error raised during evaluation: :My Index Name
Detail: Some error occured
ORA-06512: at "MICHAEL.INDEX_SCAN", line 5
ORA-06512: at line 1
    Don't have your resources - therefore the error!
    · Share on TwitterShare on Facebook

Answers

  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    Is some body know can XQuery functions be used/declared in pl sql?
    Lots of options here. Which database are you on?

    One option on my 11gr2 is
    SQL>var xml varchar2(4000)
SQL>exec :xml := dbms_xquery.eval('for $i in 1 to 3 return $i').getstringval()

PL/SQL procedure successfully completed.

SQL>print xml

XML
--------------------------------------------------------------------------------
1 2 3
    also xmlquery and xmltable are at your disposal ...
    · Share on TwitterShare on Facebook
  • 749391
    749391 Member Posts: 32
    edited Mar 2, 2010 1:36PM
    I have 11gr1. That's interesting way,not exactly what I want, but any way thank you, it's step to win ;)
    I need to create pl/sql function or procedure and put there Xquery function with XQuery variables, something like this:
    CREATE OR REPLACE PROCEDURE ($index_name) INDEX_SCAN AS

    BEGIN
    SELECT XMLQuery('
    if ('article-by-title' = $index_name) then doc('/wiki')/root/page[title=$name]
    else if ('article-by-id' = $index_name) then doc('/wiki')/root/page[id=$name]
    else if ('link-by-target' = $index_name) then doc('/wiki')/root/page/links/link[@target = $name]
    ELSE IF ('article-by-cat' = $INDEX_NAME) THEN DOC('/wiki')/ROOT/PAGE/CATLINKS/CATLINK[@TARGET=$NAME]
    else error (concat('Unknown index ', $index-name)) '
    END INDEX_SCAN;

    Here is $index_name, $name and $rel - XQuery variables which was inputed by arguments of XQuery function, so I need to declare it in pl sql, I think. But may be there is another way, I do not know.

    And thank you for links!

    Edited by: Galadrim on Mar 2, 2010 10:36 AM
    · Share on TwitterShare on Facebook
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    Answer ✓
    Something like that?:
    SQL> create or replace procedure index_scan (index_name varchar2, name varchar2)
as
   xml   xmltype;
begin
   select xmlquery (
             '
if ("article-by-title" = $index_name) then doc("/wiki")/root/page[title=$name]
else if ("article-by-id" = $index_name) then doc("/wiki")/root/page[id=$name]
else if ("link-by-target" = $index_name) then doc("/wiki")/root/page/links/link[@target = $name]
else if ("article-by-cat" = $index_name) then doc("/wiki")/root/page/catlinks/catlink[@target=$name]
else error(fn:QName("http://www.w3.org/2005/xqt-errors",  $index_name) , "Some error occured") '
             passing index_name as "index_name", name as "name"
             returning content)
     into xml
     from dual;
end index_scan;
/
Procedure created.

SQL> exec index_scan('My Index Name','My Name')
BEGIN index_scan('My Index Name','My Name'); END;
Error at line 20
ORA-19112: error raised during evaluation: :My Index Name
Detail: Some error occured
ORA-06512: at "MICHAEL.INDEX_SCAN", line 5
ORA-06512: at line 1
    Don't have your resources - therefore the error!
    · Share on TwitterShare on Facebook
  • 749391
    749391 Member Posts: 32
    Thank you very match! This is exactly what I need :)
    · Share on TwitterShare on Facebook
This discussion has been closed.