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

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