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

Read Time:3 Minute, 44 Second

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

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:

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))

About Post Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.

lucas.jellema@amis.nl

https://www.amis.nl/