Conjunction Junction… Boost Your Functions 

William Phelps, Senior Technical Architect 

Anyone who has seen the 1970’s cartoon “Schoolhouse Rock” learning skits should now have that little song going through their head. 

For everyone else, this article will show a different way to express and consume “functions” in an Oracle database. Conceptually, this concept would be database-agnostic, but the focus in this article is Oracle. 

A “function” in Oracle is a piece of PL/SQL code that, given no inputs or multiple inputs, performs work and returns a singular output value. This is different when contrasted to a “procedure” where the procedure can have multiple outputs. Typically, each input’s and output’s data is “typed”, such as “string”, “integer”, “boolean”, etc.   

In most cases these data types are sufficient. Sometimes though, it’s not enough. Sometimes a table with rows and columns would be nice. 

Consider the following simple example function which is being created using a WITH block for simplicity. This simple function doesn’t do very much. It just takes the numeric input parameter and echoes it back to the screen when called as a “select” as shown. 

PL/SQL function demonstrating precision with number type and query output showing rounded result.

There are scenarios where it is desirable to pass in a delimited “list” of integers or other values. The most likely scenario is the use case of wanting to conduct a query operation within the function or procedure that needs to use an “IN” type of statement like the following examples for performance reasons. 

SQL snippet selecting and deleting employees by ID using hardcoded values in an IN clause.

One approach in this use case may be to create a temporary table and insert all target values into the table, and then use a select as the IN clause like the following:  

SQL query selecting employee names using a subquery from a temporary table.

 It may be though, that the necessary permission to create such a temporary table doesn’t exist, or there now exists a multitude of one-off tables for a host of “no-one-knows-what-this-table-is-used-for” situations.   

Back to the previous ultra simplistic example, if three values are provided, the sample function fails with a “wrong number of arguments” message. 

PL/SQL function error caused by passing multiple arguments to a function expecting one parameter.

Let’s just look at an alternative approach to attack the problem. 

Today’s approach is to create a function that can take a string, split it, and then “pipeline” the output results back as a table-like object.   

For compactness, a package is created that has the basic setup with the following code. 

Oracle package declaration defining a pipelined function returning a table type of varchar2 strings.
Full Oracle package body using SQL logic to split comma-separated strings into rows via pipelined function.

The short explanation of the package code is as follows: 

  • Line 2 in the package signature declares a single columned table of strings. 
  • Line 5 in the package signature defines a function with the “pipelined” keyword, returning the single columned “table” defined in line 2. 

The package body from line 17 through line 24 simply takes a comma separated string and basically pivots the data from a single delimited row into a single column with multiple rows. This is accomplished using the analytics function “lag” and a connect-by level. Reading-wise it’s the hardest part, but the code can be followed reasonably well. 

Lines 28 through 31 then simply flush the rows back to the client. 

Executing this function takes the form of:

Query execution displaying output from pipelined function splitting '1,2,3' into individual rows.

This complete sample can be found online at SQL Fiddle: 
https://sqlfiddle.com/oracle/online-compiler?&id=1ca57192-514d-4c17-a726-583913f8a42d 

Ultimately, going back to the specific use case of SELECTING or DELETING as “IN”, those calls now look like the following, keeping in mind that the column name that is returned by default is named “column_value”. 

SQL queries using pipelined function to dynamically select and delete employees based on ID string input.

There are caveats to this approach, depending on your environment. 

  • The sample is not production-ready code but solely intended as informational. Proper checks for nulls, whitespace, etc. should be implemented.   
  • The VARCHAR limit of characters in the source string will be the limiting driver. Let’s assume that each desired “value” is 4 characters, plus an additional 1 character for the comma “delimiter”, which then results in a cap of 800 values. However, multiple invocations could be UNIONED to create whatever size “table” ultimately needed.   
  • This example uses values extracted from a string as numbers, but the returned values actually and technically are strings. To be clear, if numbers are required in your output, cast the output to the appropriate type.  
  • This approach could also be implemented using standalone types and functions. For source code control and general maintenance, such a specific use case is better suited to be created in a package. 
  • If passing in strings that within themselves contain the delimiter, the spurious delimiter will cause the results to be incorrect. Know your data or cleanse it appropriately. 

The concept of “conjunction” in today’s post allows us to tie together multiple values in a loose way to avoid the need of creating more disposable database objects. Let us know what other loose items that we can help tie up for you…that’s OUR function! 

Want to learn more about Oracle services? Click here.