Oracle DBMS_XQUERY Demos

Oracle DBMS_XQUERY Version 11.2.0.3

General Information

AUTHID

CURRENT_USER

Dependencies

XMLTYPE

First Available

11.2

Security Model

Owned by SYS with EXECUTE granted to PUBLIC

Source

{ORACLE_HOME}/rdbms/admin/initxqry.sql

Source Code

CREATE OR REPLACE PACKAGE dbms_xquery AUTHID CURRENT_USER IS
FUNCTION eval(xqry IN VARCHAR2) RETURN XMLTYPE;
END;
/

CREATE OR REPLACE PACKAGE BODY dbms_xquery AS
FUNCTION eval(xqry varchar2) RETURN XMLTYPE IS
   PRAGMA AUTONOMOUS_TRANSACTION;
   rval XMLTYPE := NULL;
BEGIN
    EXECUTE IMMEDIATE 'SELECT xmlquery(:1 returning content) FROM dual;
    INTO rval
    USING xqry;

    COMMIT;    -- I'd love to know why anyone at Oracle thinks this commit has value

    RETURN rval;
EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK; -- or that this rollback has anything to roll back
      RAISE;
END;
END;
/

EVAL

Undocumented

dbms_xquery.eval(xqry IN VARCHAR2) RETURN XMLTYPE;

conn uwclass/uwclass

set serveroutput on

DECLARE
inval VARCHAR2(200) := '''FOR $i IN /PDRecord where $i /PDName = "Daniel Morgan" ORDER BY $i/PDName return $i/PDName'' PASSING BY VALUE person_data';
retval XMLTYPE;
BEGIN
retval := dbms_xquery.eval(inval);
END;
/

DECLARE
inval VARCHAR2(200) := 'FOR $i IN /PDRecord where $i /PDName = "Daniel Morgan" ORDER BY $i/PDName return $i/PDName';
retval XMLTYPE;
BEGIN
retval := dbms_xquery.eval(inval);
END;
/