Home > HowTo, XML DB, XQuery > How To : Access Oracle Sequence from XQuery

How To : Access Oracle Sequence from XQuery

December 11, 2016 Leave a comment Go to comments

In this new post, we’ll see how to access an Oracle sequence from an XQuery expression.
I already wrote about a similar topic a few years ago, in How To : Access database tables from XSLT context.
Pretty much the same concepts presented back then for XSLT also apply here with XQuery.

The solution relies on the ability to query relational or object view data via the oradb URI scheme.
By wrapping the sequence call in a function and a view, we can fetch the sequence value using fn:collection() function.

In the following developments, I’ll distinguish two cases :

  • fetching sequence values from a statically declared sequence
  • fetching values from different sequences dynamically

Preliminary setup

Let’s first create a sequence :

create sequence my_seq;

And the function.
It takes the sequence name as a parameter and use native dynamic SQL to retrieve the value :

create or replace function get_seq_value (
  p_sequence_name in varchar2
) 
return number
is
  l_result  number;
begin
  execute immediate 'SELECT '||p_sequence_name||'.NEXTVAL FROM DUAL' into l_result;
  return l_result;
end;
/

 

Case 1

For case #1, I’ll use an XMLType view to wrap the function call, and an hardcoded sequence name :

create or replace view my_seq_v of xmltype
with object id (0)
as
select xmlelement("VAL", get_seq_value('MY_SEQ'))
from dual;

The XMLType view has the following advantages over a relational view :

  • no internal subquery caching that prevents fetching different values in a single XQuery context (you’ll see what I mean in case #2)
  • bypass of the default check for empty element generation
  • can undergo XQuery rewrite
SQL> select * from my_seq_v;
 
SYS_NC_ROWINFO$
-----------------------------------
1
 

— fetching a single value :

SQL> select xmlcast(
  2           xmlquery('fn:collection("oradb:/PUBLIC/MY_SEQ_V")' returning content)
  3           as number
  4         ) as seqval
  5  from dual;
 
    SEQVAL
----------
         2
 

CBO trace showing XQuery rewrite :

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT CAST(TO_CHAR("DEV"."GET_SEQ_VALUE"('MY_SEQ')) AS number
       ) "SEQVAL" FROM "SYS"."DUAL" "DUAL"

The XMLType view has been totally eliminated from the picture, allowing a direct access to data source.

— fetching multiple values :

SQL> select x.*
  2  from xmltable(
  3         'declare function local:getSequence() as xs:integer {
  4            fn:collection("oradb:/PUBLIC/MY_SEQ_V")
  5          }; (::)
  6          for $i in 1 to 10
  7          return local:getSequence()'
  8         columns seqval  number  path '.'
  9       ) x
 10  ;
 
    SEQVAL
----------
         3
         4
         5
         6
         7
         8
         9
        10
        11
        12
 

— Using XQuery Update to add attribute values from the sequence :

SQL> select xmlserialize(document
  2           xmlquery(
  3             'declare function local:getSequence() as xs:integer {
  4                fn:collection("oradb:/PUBLIC/MY_SEQ_V")
  5              }; (::)
  6              copy $d := .
  7              modify
  8                for $i in $d/root/item
  9                return insert node attribute id {local:getSequence()} into $i
 10              return $d'
 11            passing xmlparse(document '<root><item>A</item><item>B</item><item>C</item></root>')
 12            returning content
 13           )
 14           indent
 15         ) as updated_doc
 16  from dual;
 
UPDATED_DOC
--------------------------------------------------------------------------------
<root>
  <item id="13">A</item>
  <item id="14">B</item>
  <item id="15">C</item>
</root>
 

 

Case 2

Case #2 is about adding a parameter to the local:getSequence() XQuery function so that we can actually choose the sequence to query from at runtime.

To achieve that, we first need another view that exposes both the sequence name and its associated nextval.
Here it is :

create or replace view seq_values (
  name
, val
) 
as
select sequence_name
     , get_seq_value(sequence_name)
from user_sequences;

Querying the view without any predicate will obviously increment all sequences :

SQL> select * from seq_values;
 
NAME                                VAL
---------------------------- ----------
MY_SEQ                               16
PLSQL_PROFILER_RUNNUMBER            208
 

However, and this is what we want, adding a filter predicate will increment only the target sequence :

SQL> select val
  2  from seq_values
  3  where name = 'MY_SEQ';
 
       VAL
----------
        17
 
SQL> /
 
       VAL
----------
        18
 
SQL> /
 
       VAL
----------
        19
 
SQL> select * from seq_values;
 
NAME                                VAL
---------------------------- ----------
MY_SEQ                               20
PLSQL_PROFILER_RUNNUMBER            209
 

Let’s implement that in XQuery now :

SQL> select xmlcast(
  2           xmlquery(
  3             'fn:collection("oradb:/PUBLIC/SEQ_VALUES")/ROW[NAME=$name]/VAL'
  4             passing 'MY_SEQ' as "name"
  5             returning content
  6           )
  7           as number
  8         )  as seqval
  9  from dual;
 
    SEQVAL
----------
        22
 

Uh-oh, looks like we’ve just skipped a number.
Let’s try again :

SQL> /
 
    SEQVAL
----------
        24
 

OK, somehow, the sequence is called twice during query execution.
We can verify that using a CBO trace :

alter session set events '10053 trace name context forever, level 1';

Here’s how the query has been rewritten (formatted) :

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT CAST(
         SYS_XQ_UPKXML2SQL(
           SYS_XQEXVAL( 
             (
               SELECT "SYS"."SYS_IXMLAGG"(
                         CASE  WHEN "DEV"."GET_SEQ_VALUE"("O"."NAME") IS NOT NULL 
                               THEN XMLELEMENT("VAL",TO_CHAR("DEV"."GET_SEQ_VALUE"("O"."NAME"))) 
                               ELSE NULL 
                         END 
                      ) "COLUMN_VALUE" 
               FROM "SYS"."SEQ$" "S","SYS"."OBJ$" "O" 
               WHERE "O"."OWNER#"=USERENV('SCHEMAID') 
               AND "O"."OBJ#"="S"."OBJ#" 
               AND "O"."NAME"='MY_SEQ'
             ),0,0,16777216,1073741824
           ),50,1,2
         ) AS number
       ) "SEQVAL" 
FROM "SYS"."DUAL" "DUAL"

As you can see, the sequence is indeed called twice in the CASE expression.
That CASE expression is there to implement the default behaviour of fn:collection for oradb scheme, that is to not generate any XML element for NULL values.
That behaviour may be controlled via an XQuery pragma : (#ora:view_on_null empty #) which instructs Oracle to return an empty element for NULL values.
The immediate effect is to remove the CASE expression from the rewritten query, and therefore
the double sequence call :

SQL> select xmlcast(
  2           xmlquery(
  3             '(#ora:view_on_null empty #){fn:collection("oradb:/PUBLIC/SEQ_VALUES")/ROW[NAME=$name]/VAL}'
  4             passing 'MY_SEQ' as "name"
  5             returning content
  6           )
  7           as number
  8         ) as seqval
  9  from dual;
 
    SEQVAL
----------
        27
 
SQL> /
 
    SEQVAL
----------
        28
 

Good. We’re now getting contiguous numbers.
But, what about calling the sequence multiple times in the same XQuery context?

SQL> select x.*
  2  from xmltable(
  3         'declare function local:getSequence($name as xs:string) as xs:integer {
  4            (#ora:view_on_null empty #){fn:collection("oradb:/PUBLIC/SEQ_VALUES")/ROW[NAME=$name]/VAL}
  5          }; (::)
  6          for $i in 1 to 5
  7          return local:getSequence("MY_SEQ")'
  8         columns seqval  number  path '.'
  9       ) x
 10  ;
 
    SEQVAL
----------
        29
        29
        29
        29
        29
 

Doesn’t work. We’re getting the same number for all rows.
That’s the subquery caching I was talking about earlier in the introduction.

CBO trace of the query :

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT CAST(
         SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(VALUE(KOKBF$0),0,0,54525952,0),50,1,2) 
         AS number  
       ) "SEQVAL" 
FROM TABLE(
       "SYS"."XQSEQUENCE"( 
         (
           SELECT "SYS"."SYS_IXQAGG"(
                    SYS_XQFUNCR( 
                      (
                        SELECT "SYS"."SYS_IXQAGG"(
                                 SYS_XQCON2SEQ(
                                   XMLELEMENT("VAL",TO_CHAR("DEV"."GET_SEQ_VALUE"("O"."NAME")))
                                 )
                               ) "COLUMN_VALUE" 
                        FROM "SYS"."SEQ$" "S","SYS"."OBJ$" "O" 
                        WHERE "O"."OWNER#"=USERENV('SCHEMAID') 
                        AND "O"."OBJ#"="S"."OBJ#" 
                        AND "O"."NAME"='MY_SEQ'
                      ),1,0,1,33,0,'','','','integer','xs','http://www.w3.org/2001/XMLSchema','','00000000000000000000000000000000',0,2,'local:getSequence return value'
                    )
                  ) "COLUMN_VALUE" 
           FROM TABLE(
                  "SYS"."XQSEQUENCE"(SYS_XQ_NRNG(1,5))
                ) "KOKBF$1"
         )
       )
     ) "KOKBF$0"

See the highlighted scalar subquery.
Its result is cached after first execution and the same value just retrieved as-is for the other rows.

The solution is to introduce a correlation so that the subquery appears as a different one for each row. That can be achieved with an additional filter predicate, always true, against another column of the view.

Here’s the modified SEQ_VALUES view with a new DUMMY column set to a constant value.

create or replace view seq_values (
  name
, val
, dummy
) 
as
select sequence_name
     , get_seq_value(sequence_name)
     , 0
from user_sequences;

The XQuery expression also needs the following modifications :

  • positional variable $p added in FLWOR part
  • new $p argument in getSequence function
  • new predicate against DUMMY column
SQL> select x.*
  2  from xmltable(
  3         'declare function local:getSequence($name as xs:string, $p as xs:integer) as xs:integer {
  4            (#ora:view_on_null empty #){fn:collection("oradb:/PUBLIC/SEQ_VALUES")/ROW[NAME=$name and DUMMY<$p]/VAL}
  5          }; (::)
  6          for $i at $p in 1 to 5
  7          return local:getSequence("MY_SEQ",$p)'
  8         columns seqval number  path '.'
  9       ) x
 10  ;
 
    SEQVAL
----------
        31
        32
        33
        34
        35
 

 

A practical example

I’ll conclude with this last example : loading data from a multilevel XML document into different tables.

create table dept (deptno number(2), dname varchar2(14), loc varchar2(13));

create table emp (empno number(4), ename varchar2(10), deptno number(2), job varchar2(9), hiredate date);

create sequence dept_seq;

create sequence emp_seq;

Source document : departments.xml

<?xml version="1.0" encoding="UTF-8"?>
<Departments>
  <Department>
    <Name>ACCOUNTING</Name>
    <Location>NEW YORK</Location>
    <Employees>
      <Employee>
        <Name>CLARK</Name>
        <Job>MANAGER</Job>
        <HireDate>1981-06-09</HireDate>
      </Employee>
      <Employee>
        <Name>KING</Name>
        <Job>PRESIDENT</Job>
        <HireDate>1981-11-17</HireDate>
      </Employee>
      <Employee>
        <Name>MILLER</Name>
        <Job>CLERK</Job>
        <HireDate>1982-01-23</HireDate>
      </Employee>
    </Employees>
  </Department>
  <Department>
    <Name>RESEARCH</Name>
    <Location>DALLAS</Location>
    <Employees>
      <Employee>
        <Name>SMITH</Name>
        <Job>CLERK</Job>
        <HireDate>1980-12-17</HireDate>
      </Employee>
      <Employee>
        <Name>JONES</Name>
        <Job>MANAGER</Job>
        <HireDate>1981-04-02</HireDate>
      </Employee>
      <Employee>
        <Name>SCOTT</Name>
        <Job>ANALYST</Job>
        <HireDate>1987-04-19</HireDate>
      </Employee>
      <Employee>
        <Name>ADAMS</Name>
        <Job>CLERK</Job>
        <HireDate>1987-05-23</HireDate>
      </Employee>
      <Employee>
        <Name>FORD</Name>
        <Job>ANALYST</Job>
        <HireDate>1981-12-03</HireDate>
      </Employee>
    </Employees>
  </Department>
</Departments>

DEPTNO and EMPNO are not present in the XML file.
Corresponding values will be fetched from their respective sequences DEPT_SEQ and EMP_SEQ directly in the XQuery expression, and data loaded into DEPT and EMP tables using a single multitable INSERT statement.
Here, in order to allow the generation of contiguous numbers out of EMP_SEQ, we need to introduce a double correlation by referencing both $p and $q positional variables in the dummy parameter :

SQL> insert all
  2  when emp_rnum = 1 then
  3    into dept (deptno, dname, loc)
  4    values (deptno, dname, loc)
  5  when 1 = 1 then
  6    into emp (empno, ename, deptno, job, hiredate)
  7    values (empno, ename, deptno, job, hiredate)
  8  select /*+ no_merge */ x.*
  9  from xmltable(
 10         'declare function local:getSequence($name as xs:string, $p as xs:integer) as xs:integer {
 11            (#ora:view_on_null empty #){fn:collection("oradb:/PUBLIC/SEQ_VALUES")/ROW[NAME=$name and DUMMY<$p]/VAL}
 12          }; (: :)
 13          for $i at $p in /Departments/Department
 14            , $j at $q in $i/Employees/Employee
 15          return element r {
 16            element DEPTNO   { local:getSequence("DEPT_SEQ",$p) }
 17          , element DNAME    { $i/Name/text() }
 18          , element LOC      { $i/Location/text() }
 19          , element EMPNO    { local:getSequence("EMP_SEQ",$p+$q) }
 20          , element ENAME    { $j/Name/text() }
 21          , element JOB      { $j/Job/text() }
 22          , element HIREDATE { $j/HireDate/text() }
 23          , element EMP_RNUM { $q }
 24          }'
 25         passing xmltype(bfilename('TMP_DIR', 'departments.xml'), nls_charset_id('AL32UTF8'))
 26         columns deptno   number(2)
 27               , dname    varchar2(14)
 28               , loc      varchar2(13)
 29               , empno    number(4)
 30               , ename    varchar2(10)
 31               , job      varchar2(9)
 32               , hiredate date
 33               , emp_rnum number
 34       ) x
 35  ;
 
10 rows inserted.
 
SQL> select * from dept;
 
DEPTNO DNAME          LOC
------ -------------- -------------
     1 ACCOUNTING     NEW YORK
     2 RESEARCH       DALLAS
 
SQL> select * from emp;
 
EMPNO ENAME      DEPTNO JOB       HIREDATE
----- ---------- ------ --------- -----------
    1 CLARK           1 MANAGER   09/06/1981
    2 KING            1 PRESIDENT 17/11/1981
    3 MILLER          1 CLERK     23/01/1982
    4 SMITH           2 CLERK     17/12/1980
    5 JONES           2 MANAGER   02/04/1981
    6 SCOTT           2 ANALYST   19/04/1987
    7 ADAMS           2 CLERK     23/05/1987
    8 FORD            2 ANALYST   03/12/1981
 
8 rows selected.

 

Disclaimer :
All queries were designed and tested on Oracle Database 12.1.0.2, and exposed in this article mainly for educational purpose.
For the most part, they rely on the internal XQuery Rewrite process, therefore the results presented here may vary depending on the actual evaluation mode chosen by the CBO, or hinted by the user, and of course between database versions.
Use at your own risk :)

 

Advertisements
  1. January 8, 2017 at 16:21

    Hi Mark,

    Considering your last example – loading data from a multilevel XML document into different tables – you inspired me to blog about the way I handle “custom incrementing” of sequences. I use a pipelined table function instead of wrapper functions.

    If you are interested, see https://stewashton.wordpress.com/2017/01/08/multi-table-inserts-with-sequences/

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: