SQL: combine inline PL/SQL function with inline view in Oracle Database 12c SQL Query

0
Share this on .. Tweet about this on Twitter8Share on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on TumblrBuffer this page

Oracle Database 7.3 was the first to support inline views – using the select * from (select * from) syntax. Oracle Database 9i added the with clause for subquery factoring, using a syntax like with vw as (select * from) , vw2 as (select * from vw) select * from vw2 join vw join tbl. Oracle Database release 12c (12.1) added a new feature into this mix: Inline PL/SQL functions, using a syntax like: with function x (p_in varchar2) return varchar2 is begin return ‘X’;end; select x(‘ahaha’) from dual. This feature is described in various articles, such as this one: https://technology.amis.nl/2013/06/25/oracle-database-12c-in-line-plsql-functions-in-sql-queries/.

Today I was wondering how to combine in a single query both an inline function and an inline query. I was not sure what the exact syntax had to be – and to be honest, I was not even sure if the combination was supported at all. The good news: the combination is supported, and the syntax is very straightforward. There is just a comma less than you (I) perhaps would expect.

This next query uses a PL/SQL string tokenizer to split a string into its constituents (using a comma as a delimiter).

with  
  function string_tokenizer( p_string in varchar2, p_token in varchar2 default ',')
  return   string_table
  is
    l_token   VARCHAR2(50) ;
    i         PLS_INTEGER := 1 ;  
    l_tokens  string_table := string_table();

    FUNCTION get_token(
       p_input_string IN VARCHAR2,            -- input string
       p_token_number IN PLS_INTEGER,         -- token number
       p_delimiter    IN VARCHAR2 DEFAULT ',' -- separator character
    )
      RETURN VARCHAR2
    IS
      l_temp_string VARCHAR2(500) := p_delimiter || p_input_string ;
      l_pos1 number ;
      l_pos2 number ;
    BEGIN
      l_pos1     := INSTR( l_temp_string, p_delimiter, 1, p_token_number ) ;
      IF l_pos1   > 0
      THEN
        l_pos2   := INSTR( l_temp_string, p_delimiter, 1, p_token_number + 1) ;
        IF l_pos2 = 0
        THEN
          l_pos2 := LENGTH( l_temp_string ) + 1 ;
        END IF ;
        RETURN( SUBSTR( l_temp_string, l_pos1+1, l_pos2 - l_pos1-1 ) ) ;
      ELSE
        RETURN NULL ;
      END IF ;
    END get_token;
    
    BEGIN
     LOOP
       l_token := get_token( p_string, i , ',') ;
       EXIT WHEN l_token IS NULL ;
       l_tokens.extend;
       l_tokens(l_tokens.last) := l_token;
       i := i + 1 ;
      END LOOP ;
    return l_tokens;
  end string_tokenizer;
tag_filter as
( select 'recent,comeback,early' selected_tags
  from   dual
)
, filter_tags as
( select string_tokenizer(selected_tags, ',') filter_tag
  from   tag_filter
)   
select column_value tag
from   table( (select filter_tag from filter_tags))

The result is simply

image

With a little bit help of a Scalar Subquery and the Table function – see this next query –

...
( select string_tokenizer(selected_tags, ',') filter_tag
  from   tag_filter
)   
select column_value tag
from   table( (select filter_tag from filter_tags))

I can extract the elements from this nested table as if they are rows in a relation:

image

Note: a SQL query can contain multiple Inline PL/SQL Functions – with one invoking the other; I have rewritten the first query as:

with  
  FUNCTION get_token(
     p_input_string IN VARCHAR2,            -- input string
     p_token_number IN PLS_INTEGER,         -- token number
     p_delimiter    IN VARCHAR2 DEFAULT ',' -- separator character
    )
      RETURN VARCHAR2
    IS
      l_temp_string VARCHAR2(500) := p_delimiter || p_input_string ;
      l_pos1 number ;
      l_pos2 number ;
    BEGIN
      l_pos1     := INSTR( l_temp_string, p_delimiter, 1, p_token_number ) ;
      IF l_pos1   > 0
      THEN
        l_pos2   := INSTR( l_temp_string, p_delimiter, 1, p_token_number + 1) ;
        IF l_pos2 = 0
        THEN
          l_pos2 := LENGTH( l_temp_string ) + 1 ;
        END IF ;
        RETURN( SUBSTR( l_temp_string, l_pos1+1, l_pos2 - l_pos1-1 ) ) ;
      ELSE
        RETURN NULL ;
      END IF ;
  END get_token;
  function string_tokenizer( p_string in varchar2, p_token in varchar2 default ',')
  return   string_table
  is
    l_token   VARCHAR2(50) ;
    i           PLS_INTEGER := 1 ;  
    l_tokens  string_table := string_table();
  BEGIN
    LOOP
       l_token := get_token( p_string, i , ',') ;
       EXIT WHEN l_token IS NULL ;
       l_tokens.extend;
       l_tokens(l_tokens.last) := l_token;
       i := i + 1 ;
    END LOOP ;
    return l_tokens;
  end string_tokenizer;
tag_filter as
( select 'recent,comeback,early' selected_tags
  from   dual
)
, filter_tags as
( select string_tokenizer(selected_tags, ',') filter_tag
  from   tag_filter
)   
select column_value tag
from   table( (select filter_tag from filter_tags))
Share this on .. Tweet about this on Twitter8Share on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on TumblrBuffer this page

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PL/SQL), Service Oriented Architecture, BPM, ADF, JavaScript, Java in various shapes and forms and many other things. Author of the Oracle Press books: Oracle SOA Suite 11g Handbook and Oracle SOA Suite 12c Handbook. Frequent presenter on conferences such as JavaOne and Oracle OpenWorld. Presenter for Oracle University Celebrity specials.

Leave a Reply