Oracle SQL With Function clause

It’s been around for a while: the with-clause in Oracle SQL. And indeed, I used it in the past. The with clause is very useful to modularize your SQLs, especially in views.

The first time I used it, I had a case that I did not know how to solve, without the With-clause. It was around the time (in about 2018) when I forced myself to get familiar with ANSI-SQL.
Anyway, I fell sort of in love with the with-clause, so I used it also in cases where it was not necessary. But, it looked neat. And it modularized my queries.

The With-clause can be seen as an inline view. That then can be referenced in different parts of your query, as a join view, or in a sub-query.

From 12c onward, you can also use it for PL/SQL functions. I knew this was possible, but haven’t used it before. And didn’t know since when it is supported. Googling around, I found this video from Tim ORACLE-BASE.com Hall: WITH Clause: PL/SQL Declaration Section in Oracle Database 12c. And who can better explain it than he?

My Case

Now, in my case I wanted to check if a check constraint already existed, to make a DDL script re-entrant. The problem is that even in 19c, the SEARCH_CONDITION column in the user/all_constraints view still has long as a datatype.

And Oracle SQL can’t handle long. Even a substr function around it does not help.

Provided that the content of the long column does not exceed 32KB, you can use a function to transform it to a varchar2:

function get_search_condition( p_cons_name in varchar2 ) return varchar2
is
  l_search_condition user_constraints.search_condition%type;
begin
  select search_condition into l_search_condition
  from user_constraints
  where constraint_name = p_cons_name;

  return l_search_condition;
end;

I found this query on the Stack-overflow.com question I’m trying to search a long column with like, but Oracle complains.

Using the with-clause you can use it easily in an SQL-Query:

with function get_search_condition( p_cons_name in varchar2 ) return varchar2
  is
    l_search_condition user_constraints.search_condition%type;
  begin
    select search_condition into l_search_condition
    from user_constraints
    where constraint_name = p_cons_name;

     return l_search_condition;
  end;
select constraint_name 
from (
  select constraint_name
  , get_search_condition(constraint_name) search_condition 
  from user_constraints
  where table_name = 'ORCL_PROJECTS'
  and constraint_type = 'C'
) 
where search_condition like '%ORCL%_ID%IS NOT NULL%'

Neat, right?

However, one could notice my query looks for a NOT NULL-constraint. That search condition would not exceed the 4000 characters. So, you could think of the SEARCH_CONDITION_VC column in the all_constraints view. Mind that this may truncate the search condition. In my case the query could be a bit simpler:

select substr( search_condition_vc, 1, 4000 ) 
from user_constraints
where table_name = 'ORCL_PROJECTS'
and constraint_type = 'C'
and search_condition_vc like '%ORCL%_ID%IS NOT NULL%'

Recommendations

Although I love the With-clause, I found some points I wanted to share:

• Use with-clauses to share code within a view or to modularize your query significantly. Or in cases when not using a with-clause makes your query question hard or seemingly impossible to solve.
• Move with-subqueries that are the same are exactly the same, or very similar to with-clauses in other views into a separate view.
• Try to merge With-subqueries that aren’t used as a direct source for columns, but only used as a source or join for another (one) query and not shared. Provided that the merged query is not significantly more complicated.
• Use with-clauses for subqueries and PL/SQL functions when you can’t create them as standalone objects because you can’t modify the database schema.
• Standalone functions and views improve reuse and are easier to document. And I read in Tim Hall’s article WITH Clause Enhancements in Oracle Database 12c Release 1 (12.1) that a standalone function with the pragma UDF (User Defined Function) may outperform an in-line with-function.
• Avoid using stand-alone functions and procedures. Use functions and packages instead.

Conclusion

Yes, it turned out that I did not need the WITH-function clause in the end. But, it was fun to play with it. And I’m happy to have it in my toolbox. At least, it led to a new article.

So, as we say in Dutch: “Take advantage of it” (“Doe er je voordeel mee”)!

Links

• 19c SQL Language Reference: Using a PL/SQL Function in the WITH Clause: Examples
• ORACLE-BASE.com: WITH Clause Enhancements in Oracle Database 12c Release 1 (12.1)