This discussion is archived
6 Replies Latest reply: Oct 11, 2012 6:57 AM by 967661 RSS

Xquery SQL Function

967661 Newbie
Currently Being Moderated
Hi,

In an XQuery query is it posssible to call a function or procedure SQL (PL / SQL)?
Here is an example of what I'd achieved :


SELECT PRD.XMLCOLUMN from
XMLTABLE('for $i in ora:view("P_iXXX")/ROW,
                    $j in ora:view("P_jXXX")/ROW,
where
($i/NSU_xxx = $j/NSU_xxx)
return
(
<NCL>
{
$i/NSU_xxx,
h3. CALL Function SQL ???? as sql_xxx
for $k in ora:view("P_kxxx")/ROW
$vk/COD_RMP_VPM = "PRD")
return()
}
</NCL>
)' COLUMNS "resultsqlfunction" INTEGER PATH 'xsql_xxx' "NSU_xxx" INTEGER PATH 'NSU_xxx',"XMLCOLUMN" XMLType PATH '.') as PRD;

Thank you in advance for your answers
François
  • 1. Re: Xquery SQL Function
    odie_63 Guru
    Currently Being Moderated
    In an XQuery query is it posssible to call a function or procedure SQL (PL / SQL)?
    If you mean a user-defined function, then no, it's not possible.

    The best you can do is wrap a function with no input parameter in a view and call it via ora:view().
    A simplistic example of that would be :
    SQL> create or replace function getCurrentDate return date
      2  is
      3  begin
      4   return sysdate;
      5  end;
      6  /
     
    Function created
     
    SQL> create or replace view getCurrentDate_v as select getCurrentDate() dt from dual;
     
    View created
     
    SQL> 
    SQL> select *
      2  from xmltable('ora:view("GETCURRENTDATE_V")/ROW/DT')
      3  ;
     
    COLUMN_VALUE
    --------------------------------------------------------------------------------
    <DT>2012-10-11</DT>
     
    Based on your example, I think you'd better use SQL/XML functions XMLElement, XMLAgg etc. to directly generate the XML document.
    That way, you'll also be able to call the function.

    Edited by: odie_63 on 11 oct. 2012 10:54
  • 2. Re: Xquery SQL Function
    967661 Newbie
    Currently Being Moderated
    Thanks for your answer,

    In fact, my goal is to test the Xquery, see if xquery have best performance than SQL/XML functions (If you have any information on this subject I am very interested.)

    Currently I have code that uses SQL/XML functions XMLElement, XMLAgg ... and I am trying to convert iy in XQuery.

    This code (SQL/XML) calls a procedure pl/sql that returns data...

    You can not call a function pl / sql (which must pass values ​​and returns data) via xquery?


    François
  • 3. Re: Xquery SQL Function
    odie_63 Guru
    Currently Being Moderated
    In fact, my goal is to test the Xquery, see if xquery have best performance than SQL/XML functions (If you have any information on this subject I am very interested.)
    Depends on the complexity of the original query, number of JOINs etc.
    When using ora:view (which is now deprecated), Oracle actually rewrites the XQuery expression using SQL/XML functions.

    For example, the following query :
    select * 
    from xmltable('ora:view("SCOTT","EMP")/ROW');
    is internally rewritten to :
    +(excerpt from a CBO trace, reformatted for display purpose)+
    Final query after transformations:******* UNPARSED QUERY IS *******
    SELECT XMLELEMENT("ROW",
              XMLELEMENT("EMPNO",TO_CHAR("SYS_ORAVW_4"."EMPNO")),
              CASE  WHEN "SYS_ORAVW_4"."ENAME" IS NOT NULL THEN XMLELEMENT("ENAME","SYS_ORAVW_4"."ENAME") ELSE NULL END ,
              CASE  WHEN "SYS_ORAVW_4"."JOB" IS NOT NULL THEN XMLELEMENT("JOB","SYS_ORAVW_4"."JOB") ELSE NULL END ,
              CASE  WHEN "SYS_ORAVW_4"."MGR" IS NOT NULL THEN XMLELEMENT("MGR",TO_CHAR("SYS_ORAVW_4"."MGR")) ELSE NULL END ,
              CASE  WHEN LTRIM(TO_CHAR("SYS_ORAVW_4"."HIREDATE",'SYYYY-MM-DD')) IS NOT NULL THEN XMLELEMENT("HIREDATE",LTRIM(TO_CHAR("SYS_ORAVW_4"."HIREDATE",'SYYYY-MM-DD'))) ELSE NULL END ,
              CASE  WHEN "SYS_ORAVW_4"."SAL" IS NOT NULL THEN XMLELEMENT("SAL",TO_CHAR("SYS_ORAVW_4"."SAL")) ELSE NULL END ,
              CASE  WHEN "SYS_ORAVW_4"."COMM" IS NOT NULL THEN XMLELEMENT("COMM",TO_CHAR("SYS_ORAVW_4"."COMM")) ELSE NULL END ,
              CASE  WHEN "SYS_ORAVW_4"."DEPTNO" IS NOT NULL THEN XMLELEMENT("DEPTNO",TO_CHAR("SYS_ORAVW_4"."DEPTNO")) ELSE NULL END ) "COLUMN_VALUE" 
    FROM "SCOTT"."EMP" "SYS_ORAVW_4"
    So it's very similar to what we can do directly with SQL/XML publishing functions.

    However, that was a very simple example, on a single table.
    When the complexity gets higher, as in your test case, with more than one table, multiple joins or filter predicates etc., the rewrite process may not be as efficient and could lead to suboptimal execution plans.

    Check explain plans for both queries and/or CBO traces to see how Oracle resolves them, then you'll be able to compare.
    Given how Oracle interprets ora:view() calls, I guess the XQuery equivalent will never surpass SQL/XML publishing functions in terms of performance, it should be equal at best.

    You can not call a function pl / sql (which must pass values ​​and returns data) via xquery?
    No.
  • 4. Re: Xquery SQL Function
    967661 Newbie
    Currently Being Moderated
    Thank you for all your answers, they will be very useful to me
    When using ora:view (which is now deprecated), Oracle actually rewrites the XQuery expression using SQL/XML functions.
    Which now replaces "ora: view" ?


    On my database the DBA have installed Oracle XML DB to use the function XMLType and XMLTable, I thought these functions allow among others to accelerate the creation of XML.

    What are the advantages of using the XQuery and XMLTable and XMLQUERY functions ?

    It is there another method to accelerate the creation of XML?


    In some clause "Where" that I will turn, I can have multiple "outer join" . Here is an example.

    where
    PPM.NSU_PPM=w.NSU_PPM and FRN.NSU_FRN(+)=FCO.NSU_FRN and FCO.NSU_FCO(+)=PPM.NSU_FCO
    and T2O.NSQ_T2O(+)=PPM.NSQ_T2O
    and T51.NSQ_T51(+)=PPM.NSQ_T51
    and STT.NSU_STT(+)=PPM.NSU_STT
    and T01.NSQ_T01(+)=PPM.NSQ_T01
    and vpm.cod_rmp_vpm (+) = 'PRA'
    and vpm.num_vsn_vpm (+) = PPM.vsn_sup
    and w.nsu_ppm = A.nsu_ppm (+)
    and W.NSU_SOO = A_HEADER.NSU_SOO and A_HEADER.NOR_SOM = 0
  • 5. Re: Xquery SQL Function
    odie_63 Guru
    Currently Being Moderated
    Which now replaces "ora: view" ?
    On 11.2, ora:view() extension function is deprecated in favour of fn:collection("oradb:/") method :

    http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb_xquery.htm#BABECFFD

    On my database the DBA have installed Oracle XML DB to use the function XMLType and XMLTable, I thought these functions allow among others to accelerate the creation of XML.

    What are the advantages of using the XQuery and XMLTable and XMLQUERY functions ?
    Which database version are you using?

    Please read : http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb_xquery.htm#CBAGCBGJ
    It is there another method to accelerate the creation of XML?
    SQL/XML publishing functions are probably the best way to build custom XML documents out of relational data.

    The core functionality of XQuery is not to query RDBMS tables or views, although Oracle provides us with extensions to do so.
    Most of the time, I use XQuery (via XMLTable and XMLQuery) to do just the opposite, i.e. extracting XML data in a relational form, or transforming XML fragments.

    If your actual queries are slow - though you'll have to define what it means and what you're expecting - then maybe SQL/XML functions are not the real problem.

    In some clause "Where" that I will turn, I can have multiple "outer join" . Here is an example.

    where
    PPM.NSU_PPM=w.NSU_PPM and FRN.NSU_FRN(+)=FCO.NSU_FRN and FCO.NSU_FCO(+)=PPM.NSU_FCO
    and T2O.NSQ_T2O(+)=PPM.NSQ_T2O
    and T51.NSQ_T51(+)=PPM.NSQ_T51
    and STT.NSU_STT(+)=PPM.NSU_STT
    and T01.NSQ_T01(+)=PPM.NSQ_T01
    and vpm.cod_rmp_vpm (+) = 'PRA'
    and vpm.num_vsn_vpm (+) = PPM.vsn_sup
    and w.nsu_ppm = A.nsu_ppm (+)
    and W.NSU_SOO = A_HEADER.NSU_SOO and A_HEADER.NOR_SOM = 0
    Yes, that's exactly what I was talking about.
    Transposing the logic to XQuery 1.0 is not the most straightforward approach (to say the least), as there's no direct OUTER JOIN support.
    XQuery 3.0 provides the functionality but it's not there yet in the database.

    My suggestion would be to stick to SQL/XML publishing functions.
    If you have a specific issue regarding performance, we (as in forum members) will be glad to help.
    Provide the necessary steps to reproduce the issue, including some sample data.

    Regards,

    Marc.
  • 6. Re: Xquery SQL Function
    967661 Newbie
    Currently Being Moderated
    Thank you very much for your help..

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points