Internal handling of XQuery functions

… or more precisely user-defined recursive XQuery functions.
I recently stumble upon this while checking the explain plan of a query :

SQL> explain plan set statement_id = 'XQ1' for
  2  select *
  3  from xmltable(
  4  'declare function local:base26($n as xs:integer, $a as xs:string) as xs:string
  5   {
  6    let $q := ($n - 1) idiv 26
  7    let $r := ($n - 1) mod 26
  8    let $c := concat(codepoints-to-string($r + 65), $a)
  9    return if ($q = 0) then $c else local:base26($q, $c)
 10   }; (::)
 11   for $i in 1 to xs:integer($maxnum)
 12   return local:base26($i, "")'
 13    passing 100 as "maxnum"
 14    columns val varchar2(30) path '.'
 15  )
 16  ;

Explicité.

SQL> select * from table(dbms_xplan.display('PLAN_TABLE','XQ1','ALL'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
Plan hash value: 303350441

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                       |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR XMLSEQ FETCH   | XQSEQUENCEFROMXMLTYPE |  8168 | 16336 |    29   (0)| 00:00:01 |
|   2 |   SORT AGGREGATE                    |                       |     1 |     2 |            |       |
|   3 |    COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE |  8168 | 16336 |    29   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$3396EF02 / KOKBF$0@SEL$2
   2 - SEL$BB54A818
   3 - SEL$BB54A818 / KOKBF$1@SEL$AEEEB114

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - VALUE(A0)[40]
   2 - (#keys=0) "SYS"."SYS_IXQAGG"("SYSFNFRqsWrZ7TBGiE7p7Dz2qYQ=="."SYSFN09KYXXCvRs6+WP0Fc3hgfA=="(V
       ALUE(KOKBF$),SYS_XQ_PKSQL2XML(NULL,1,2,0)))[40]
   3 - VALUE(A0)[40]

Note
-----
   - Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)

A little background information about the query : it generates first 100 integers converted to an alphabetic base26 format (similar to MS Excel column naming), for example 1 = ‘A’, 26 = ‘Z’, 27 = ‘AA’, … , or 266583 = ‘ODIE’ ;-)
The algorithm is just a recursive Euclidean division.

Now, what’s with this mysterious object ?

"SYSFNFRqsWrZ7TBGiE7p7Dz2qYQ=="."SYSFN09KYXXCvRs6+WP0Fc3hgfA=="

Answer’s in ALL_SOURCE dictionary view :

select text from all_source where name = 'SYSFNFRqsWrZ7TBGiE7p7Dz2qYQ==';

It turns out that’s an XDB PL/SQL package created “on the fly” to implement the recursive XQuery function.
Here’s the content of the package body (manually formatted here for clarity) :

PACKAGE BODY "SYSFNFRqsWrZ7TBGiE7p7Dz2qYQ==" IS 

FUNCTION "SYSFN09KYXXCvRs6+WP0Fc3hgfA=="
( 
  "$n" IN XMLTYPE
, "$a" IN XMLTYPE
) 
RETURN  XMLTYPE 
IS 
  RET_DATA XMLTYPE; 
BEGIN 
  SELECT SYS_XQFUNCR(
           CASE TRUNC((SYS_XQ_UPKXML2SQL("$n",33,2,0)-1)/26,0) 
             WHEN 0 THEN SYS_XQ_PKSQL2XML(
                           SYS_XQCODEP2STR(
                             SYS_XQ_PKSQL2XML(MOD(SYS_XQ_UPKXML2SQL("$n",33,2,0)-1,26)+65,2,33,0)
                           ) || SYS_XQ_UPKXML2SQL("$a",2,1,0),1,2,0) 
             ELSE "XDB"."SYSFNFRqsWrZ7TBGiE7p7Dz2qYQ=="."SYSFN09KYXXCvRs6+WP0Fc3hgfA==" ( 
                    SYS_XQ_PKSQL2XML(TRUNC((SYS_XQ_UPKXML2SQL("$n",33,2,0)-1)/26,0),2,33,0)
                  , SYS_XQFUNCR(
                      SYS_XQ_PKSQL2XML(
                        SYS_XQCODEP2STR(
                          SYS_XQ_PKSQL2XML(MOD(SYS_XQ_UPKXML2SQL("$n",33,2,0)-1,26)+65,2,33,0)
                        ) || SYS_XQ_UPKXML2SQL("$a",2,1,0),1,2,0
                      ),1,0,1,2,0,'','',''
                    , 'string','xs','http://www.w3.org/2001/XMLSchema'
                    , '','00000000000000000000000000000000',0,2
                    , 'local:base26 parameter :a'
                    )
                  ) 
           END 
         , 1,0,1,2,0,'','',''
         , 'string','xs'
         , 'http://www.w3.org/2001/XMLSchema'
         , '','00000000000000000000000000000000',0,2
         , 'local:base26 return value'
         ) "1"  
  INTO RET_DATA 
  FROM SYS.DUAL; 
  
  RETURN RET_DATA; 
END "SYSFN09KYXXCvRs6+WP0Fc3hgfA=="; 

END "SYSFNFRqsWrZ7TBGiE7p7Dz2qYQ==";

Sometimes, the package body is not available in ALL_SOURCE, so we have to dig into an SQL trace to find it.
That’s the case with the 3rd query in this post – again, using a recursive XQuery function. Here’s the generated package body for this one :

CREATE PACKAGE BODY "XDB"."SYSFNIT8S2OXeQxWLGh0hcJSLKQ==" IS  

FUNCTION "SYSFNsgVDFcq4SuyjJ6ed4kXvAg=="
(
  "$emps" IN XMLTYPE
, "$mgr" IN XMLTYPE
) 
RETURN  XMLTYPE 
IS 
  RET_DATA XMLTYPE; 
BEGIN 
  SELECT SYS_XQCON2SEQ( 
          (
           SELECT "SYS"."SYS_IXMLAGG"(
                    XMLELEMENT(NOMAPPING "employee",
                      XMLATTRIBUTES(
                        SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(EXTRACT("$i"."COLUMN_VALUE",'/ROW/EMPLOYEE_ID')),50,1,0) AS "id"
                      , SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(EXTRACT("$i"."COLUMN_VALUE",'/ROW/LAST_NAME')),50,1,0) AS "name"
                      )
                    , SYS_XQSEQ2CON(
                        "XDB"."SYSFNIT8S2OXeQxWLGh0hcJSLKQ=="."SYSFNsgVDFcq4SuyjJ6ed4kXvAg==" (
                         "$emps"
                        , SYS_XQ_PKSQL2XML(
                            SYS_XQ_ATOMCNVCHK(TO_NUMBER(EXTRACTVALUE("$i"."COLUMN_VALUE",'/ROW/EMPLOYEE_ID',null,1,50,536873984,1073872896)),2,33),2,33,0
                          )
                        )
                      )
                    ) 
                    ORDER BY EXTRACTVALUE("$i"."COLUMN_VALUE",'/ROW/EMPLOYEE_ID',null,1,50,536873984,1073872896)
                  ) "COLUMN_VALUE" 
           FROM (
             SELECT VALUE(KOKBF$0) "COLUMN_VALUE" 
             FROM TABLE("SYS"."XMLSEQUENCE"(SYS_XQSEQ2CON("$emps"))) "KOKBF$0"
           ) "$i" 
           WHERE  EXISTS ( SELECT NULL "SYS_XQCON2SEQ(VALUE(P))" 
                           FROM  (
                             SELECT VALUE(KOKBF$1) "COLUMN_VALUE" 
                             FROM TABLE("SYS"."XMLSEQUENCE"(EXTRACT("$i"."COLUMN_VALUE",'/ROW/MANAGER_ID'))) "KOKBF$1"
                           ) "P" 
                           WHERE TO_BINARY_DOUBLE(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT("P"."COLUMN_VALUE",'/MANAGER_ID'),1,50),50,1,0))
                               = TO_BINARY_DOUBLE(SYS_XQ_UPKXML2SQL("$mgr",33,2,0)) ) 
           OR "$mgr" IS NULL 
           AND LNNVL(
                 SYS_XQ_UPKXML2SQL(
                   CASE  WHEN EXISTSNODE("$i"."COLUMN_VALUE",'/ROW/MANAGER_ID')=1 
                     THEN SYS_XQ_PKSQL2XML(HEXTORAW('01') ,23,3,0) 
                     ELSE SYS_XQ_PKSQL2XML(HEXTORAW('00') ,23,3,0) 
                   END ,3,23,0
                 ) = HEXTORAW('01') 
               )
          )
         ) "1"  
  INTO RET_DATA 
  FROM SYS.DUAL; 
  
  RETURN RET_DATA; 
END "SYSFNsgVDFcq4SuyjJ6ed4kXvAg=="; 

END "SYSFNIT8S2OXeQxWLGh0hcJSLKQ==";