Passing an array as an argument into an Oracle stored procedure

Question

I am in a bit of a pickle. I have a stored procedure that accepts an argument and returns the results of a query. The query is using an IN statement.

Here's the structure of the SP:

CREATE OR REPLACE 
PROCEDURE EXAMPLE_SP
(
   arg VARCHAR2,
   argRS1 OUT cursors.rs
)
AS 
 l_test VARCHAR2(255) := arg;

BEGIN

  OPEN argRS1 FOR
    SELECT * FROM TABLE1
    WHERE LOCATION IN (l_test);

END EXAMPLE_SP;

The number of values within the IN statement can be variable. The options for IN are coming from selected form checkboxes on the UI side.

I am using PHP to retrieve the selected checkbox values. I have already tried imploding the values into a comma deliminated string.

My logic for that was that the query would then look like this:

l_test = 'val1,  val2, val3';
SELECT * FROM TABLE1
WHERE LOCATION IN (val1, val2, val3);

But that didn't work. I am not sure how to proceed. Thanks in advance for any constructive comments or suggestions.

score 0 · Answer 1 · answered Dec 05 '12 at 19:04

You can add this comma separated input parameter as a varchar() and use following where statement:

where (','||l_test||',' like '%,'||LOCATION||',%')

for example if l_test='2,3,4,5' and LOCATION=3 we get:

where (',2,3,4,5,' like '%,3,%')

and it's TRUE if LOCATION value is in this list.

Gaurav Soni · Answer 2 · 2012-12-05T19:48:03.553

I think the location that you have selected is VARCHAR,so for that you need to convert the list as shown below

l_test = '''val1''||','|| ''val2''||','||''val3''';

So that your final query look like

SELECT * FROM TABLE1
WHERE LOCATION IN ('val1', 'val2', 'val3');

You can do like this also

CREATE OR REPLACE 
PROCEDURE EXAMPLE_SP
(
  arg VARCHAR2,
  argRS1 OUT cursors.rs
)
AS 
 l_test VARCHAR2(255) := arg;

 BEGIN
  l_test:=''''||replace(l_test,',',''',''')||'''';
  OPEN argRS1 FOR
    SELECT * FROM TABLE1
     WHERE LOCATION IN (l_test);

 END EXAMPLE_SP;

Note:I have not tested this ,but i think this way you will achieve what you want

score 0 · Answer 3 · answered Dec 06 '12 at 08:43

I would do this without using string manipulation. Theoretically there may currently be little risk of SQL Injection because you're using checkboxes it's best to implement good practice at the beginning so if anything changes you don't put yourself at risk.

The second benefit is that you are still able to utilise any indexes on your column, which you wouldn't be able to do if you use like '%....

To do this you can utilise a table function and an external object to populate your "in" list.

As an example I'm going to return the OBJECT_NAME from USER_OBJECTS.

If I create two tables:

create table tmp_test ( a number );
create table tmp_test2 ( a number );

and an object to hold the list of tables, or in your case locations.

create type t_test_object is table of varchar2(30);

Next, here's the equivalent of your procedure. It's a function that returns a SYS_REFCURSOR. It accepts the T_TEST_OBJECT as a parameter, which means this first needs to be populated before being passed to the function.

create or replace function select_many (
       Ptest_object in t_test_object
       ) return sys_refcursor is

   l_curs sys_refcursor;

begin

   open l_curs for
    select object_name
      from user_objects
     where object_name in ( select *
                              from table(Ptest_object)
                                   );

   return l_curs;

end;

Lastly, here's an example of how to use this set-up. Notice how an instance of T_TEST_OBJECT gets populated with multiple values. This object then gets passed to the function to return your cursor. Lastly, to display the values I loop through the cursor. Obviously you may want to utilise the cursor and populate the TYPE differently.

SQL> declare
  2
  3     l_nested_table t_test_object := new t_test_object();
  4     l_cursor sys_refcursor;
  5     -- Used for display demonstration only.
  6     l_object_name user_objects.object_name%type;
  7
  8  begin
  9
 10     l_nested_table.extend;
 11     l_nested_table(l_nested_table.last) := 'TMP_TEST';
 12     l_nested_table.extend;
 13     l_nested_table(l_nested_table.last) := 'TMP_TEST2';
 14
 15     l_cursor := select_many(l_nested_table);
 16     
 17     loop -- Display example, not really relevant
 18        fetch l_cursor into l_object_name;
 19        exit when l_cursor%notfound;
 20        dbms_output.put_line(l_object_name);
 21     end loop;
 22
 23  end;
 24  /
TMP_TEST
TMP_TEST2

PL/SQL procedure successfully completed.

score 0 · Answer 4 · edited May 23 '17 at 11:55

You can use Oracle examples from Oracle Documentation: http://docs.oracle.com/cd/B28359_01/win.111/b28378/basfeat.htm#sthref207

Look here - return a table: Can an SQL procedure return a table?

And here's another example:

PACKAGE emp_actions IS
  TYPE EnameTabTyp IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
  TYPE SalTabTyp IS TABLE OF emp.sal%TYPE INDEX BY BINARY_INTEGER;
  ...
  PROCEDURE hire_batch (ename_tab IN EnameTabTyp, sal_tab IN SalTabTyp, ...);
  PROCEDURE log_names (ename_tab IN EnameTabTyp);
END emp_actions;

Passing an array as an argument into an Oracle stored procedure

4 Answers4