Archive

Posts Tagged ‘sequence’

How To : Access Oracle Sequence from XQuery

December 11, 2016 1 comment

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;
/

 

Read more…

Advertisements

How To : Access database tables from XSLT context

July 30, 2012 12 comments

In this post we’ll see how to access a database table (or view) in the context of an XSL transformation.

The key feature allowing that is the ability to query a table using a DBUri URI.
In SQL or PL/SQL, DBUris are managed via the DBUriType object and its methods (see DBUris: Pointers to Database Data), and over HTTP protocol via the DBUriServlet.
The URI address is an XPath-based string, just like the oradb scheme available in XQuery.

In XSLT, we can pass a DBUri URI to the document() function in order to retrieve data in XML format. There are however some restrictions.

Read more…

Categories: HowTo, XSLT Tags: , , ,