Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.8K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.8K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 394 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
Xquery function in pl sql

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