Using an Array of strings in a query with Oracle PL/SQL

Oracle Function

In this case we will use a function created in Oracle PL/SQL that receives a string value and return as an array to your query:



CREATE OR REPLACE FUNCTION string_to_list (stringParameter IN VARCHAR2,separator IN VARCHAR2 DEFAULT ',')
   RETURN ARRAY_TABLE
AS
   stringValue      LONG DEFAULT stringParameter || separator;
   dataToReturn     ARRAY_TABLE := ARRAY_TABLE ();
   n                NUMBER;
BEGIN
   LOOP
      EXIT WHEN stringValue IS NULL;
      n                                 := INSTR (stringValue, separator);
      dataToReturn.EXTEND;
      dataToReturn (dataToReturn.COUNT) := LTRIM (RTRIM (SUBSTR (stringValue, 1, n - 1)));
      stringValue                       := SUBSTR (stringValue, n + 1);
   EN

You can see that the return is a type of ARRAY TABLE, this type can be created in this way:



CREATE TYPE "ARRAY_TABLE" as table
     of varchar2 (4210)

So with this type and function created, you can use your query like this:



SELECT * FROM TABLE_TO_SEARCH
WHERE (upper(column_to_search) in (SELECT * FROM TABLE(IN_LIST(upper(:stringParameter)))))

Where the stringParameter it's a value like:

"Value 1, Value 2, Value 3".

The stringParameter is written with ":" because in this case i used the Dbeaver tool.

Sorry for the Queries shown above for not being indented and with the usual colors.
I hope that this can help you like it helped me.

Thanks for reading.

Blog

Using an Array of strings in a query with Oracle PL/SQL

Bruno Panassi

Oracle Function

Join Our Newsletter. No Spam, Only the good stuff.

Related