Forum Stats

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

Discussions

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.

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!

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 ...
  • 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
  • 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!
  • 749391
    749391 Member Posts: 32
    Thank you very match! This is exactly what I need :)
This discussion has been closed.