Sending an Array of Values to Oracle Procedure to Use in Where in Clause

Sending an array of values to Oracle procedure to use in WHERE IN clause

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

where (','||PARAM_THAT_WILL_BE||',' like '%,'||COL1||',%')

for example if PARAM_THAT_WILL_BE='2,3,4,5' and col1=3 we get:

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

and it's TRUE if COL1 value is in this list.
Here you don't use a dynamic query so you avoid concerns 1) and 2).

Passing an array of strings to a procedure and using it in the WHERE clause with IN

Your array needs to be a SQL object type, created directly in SQL, not a PLSQL type declared in a package:

SQL> CREATE OR REPLACE TYPE t_strarray IS TABLE OF VARCHAR2(30);
  2  /
Type created.

SQL> CREATE TABLE mytable (field1 VARCHAR2(30));
Table created.

SQL> INSERT INTO mytable VALUES ('A');
1 row created.

SQL> INSERT INTO mytable VALUES ('D');
1 row created.

SQL> CREATE OR REPLACE PROCEDURE getstuff(p_list IN t_strarray,
  2                                       io_cursor OUT SYS_REFCURSOR) IS
  3  BEGIN
  4     OPEN io_cursor FOR
  5        SELECT *
  6          FROM mytable
  7         WHERE mytable.field1 IN (SELECT COLUMN_VALUE FROM TABLE(p_list));
  8  END;
  9  /
Procedure created.

SQL> VARIABLE cc REFCURSOR;
SQL> EXEC getstuff (t_strarray('A', 'B', 'C'), :cc);    
PL/SQL procedure successfully completed.

SQL> print cc

FIELD1
------------------------------
A

Passing an array to function and use it in WHERE IN clause

The error is because CONCAT (||) operator accepts only scalar variables (string/number), you cannot pass an array to it.

You need to execute this as a dynamic PL/SQL block.

In case you want to bind the array dynamically, try something like this.

Bind the variables using IN and OUT keywords appropriately.

In your Anonymous block string, prefix the to-be-bind variables with colon (:)

EXECUTE IMMEDIATE '
  BEGIN
       SELECT
         COLUM1,COLUMN2..
       INTO
         :VAR1, :VAR2..
       FROM .... WHERE...
       AND Employee.empId in (select column_value from table(:empId_arr));
  END;
  '
  USING OUT VAR1, OUT VAR2... IN empId_arr;

It can also be Simply,

OPEN EMP_CURSOR FOR
  'SELECT * FROM Employee 
      where empId in SELECT COLUMN_VALUE FROM TABLE(:empId_arr)'
   USING empId_arr ;

If you take the output as a cursor;

AS Wernfried mentioned.. Using MEMBER OF operator.

OPEN EMP_CURSOR FOR
  'SELECT * FROM Employee 
      where empId member of :empId_arr'
   USING empId_arr ;

PLSQL - Use variable array on where clause IN

As you don't want to create your own type, use built-in one - sys.odcivarchar2list. For example, fetch employees who are either clerks or managers:

SQL> select deptno, empno, ename, job, sal
  2  from emp
  3  where job in (select *
  4                from table(sys.odcivarchar2list('CLERK', 'MANAGER'))
  5               );

    DEPTNO      EMPNO ENAME      JOB              SAL
---------- ---------- ---------- --------- ----------
        10       7934 MILLER     CLERK           1300
        30       7900 JAMES      CLERK            950
        20       7876 ADAMS      CLERK           1100
        20       7369 SMITH      CLERK            800
        10       7782 CLARK      MANAGER         2450
        30       7698 BLAKE      MANAGER         2850
        20       7566 JONES      MANAGER         2975

7 rows selected.

SQL>

---

If you want to declare a variable whose datatype is sys.odcivarchar2list (so, you're in a PL/SQL procedure), then

SQL> declare
  2    l_job sys.odcivarchar2list := sys.odcivarchar2list('CLERK', 'MANAGER');
  3    l_cnt number;
  4  begin
  5    select count(*)
  6      into l_cnt
  7      from emp
  8      where job in (select * from table(l_job));
  9
 10    dbms_output.put_line('Found ' || l_cnt || ' employees');
 11  end;
 12  /
Found 7 employees

PL/SQL procedure successfully completed.

SQL>

Passing an array of data as an input parameter to an Oracle procedure

This is one way to do it:

SQL> set serveroutput on
SQL> CREATE OR REPLACE TYPE MyType AS VARRAY(200) OF VARCHAR2(50);
  2  /

Type created

SQL> CREATE OR REPLACE PROCEDURE testing (t_in MyType) IS
  2  BEGIN
  3    FOR i IN 1..t_in.count LOOP
  4      dbms_output.put_line(t_in(i));
  5    END LOOP;
  6  END;
  7  /

Procedure created

SQL> DECLARE
  2    v_t MyType;
  3  BEGIN
  4    v_t := MyType();
  5    v_t.EXTEND(10);
  6    v_t(1) := 'this is a test';
  7    v_t(2) := 'A second test line';
  8    testing(v_t);
  9  END;
 10  /

this is a test
A second test line

To expand on my comment to @dcp's answer, here's how you could implement the solution proposed there if you wanted to use an associative array:

SQL> CREATE OR REPLACE PACKAGE p IS
  2    TYPE p_type IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
  3  
  4    PROCEDURE pp (inp p_type);
  5  END p;
  6  /

Package created
SQL> CREATE OR REPLACE PACKAGE BODY p IS
  2    PROCEDURE pp (inp p_type) IS
  3    BEGIN
  4      FOR i IN 1..inp.count LOOP
  5        dbms_output.put_line(inp(i));
  6      END LOOP;
  7    END pp;
  8  END p;
  9  /

Package body created
SQL> DECLARE
  2    v_t p.p_type;
  3  BEGIN
  4    v_t(1) := 'this is a test of p';
  5    v_t(2) := 'A second test line for p';
  6    p.pp(v_t);
  7  END;
  8  /

this is a test of p
A second test line for p

PL/SQL procedure successfully completed

SQL> 

This trades creating a standalone Oracle TYPE (which cannot be an associative array) with requiring the definition of a package that can be seen by all in order that the TYPE it defines there can be used by all.

Array in IN() clause oracle PLSQL

Assuming that your collection is defined in SQL, not just in PL/SQL, you can use the TABLE operator (the definition you posted isn't syntactically valid-- you'd need to specify a length for the VARCHAR2)

AND p.plc_status IN (SELECT column_value
                       FROM TABLE( plcListchar ))

Since I don't have your tables, an example using the SCOTT schema

SQL> create type ename_tbl is table of varchar2(30);
  2  /

Type created.

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    l_enames ename_tbl := ename_tbl( 'KING', 'SMITH' );
  3  begin
  4    for i in (select *
  5                from emp
  6               where ename in (select column_value
  7                                 from table( l_enames )))
  8    loop
  9      dbms_output.put_line( 'ENAME = ' || i.ename );
 10    end loop;
 11* end;
SQL> /
ENAME = KING
ENAME = SMITH

PL/SQL procedure successfully completed.

Oracle stored procedure with parameters for IN clause

Using CSV is probably the simplest way, assuming you can be 100% certain that your elements won't themselves contain strings.

An alternative, and probably more robust, way of doing this is to create a custom type as a table of strings. Supposing your strings were never longer than 100 characters, then you could have:

CREATE TYPE string_table AS TABLE OF varchar2(100);

You can then pass a variable of this type into your stored procedure and reference it directly. In your case, something like this:

FUNCTION EXECUTE_UPDATE(
    identifierList string_table,
    value int)
RETURN int
IS
BEGIN

    [...other stuff...]

    update table1 set col1 = col1 - value 
    where id in (select column_value from table(identifierList));

    RETURN SQL%ROWCOUNT;

END

The table() function turns your custom type into a table with a single column "COLUMN_VALUE", which you can then treat like any other table (so do joins or, in this case, subselects).

The beauty of this is that Oracle will create a constructor for you, so when calling your stored procedure you can simply write:

execute_update(string_table('foo','bar','baz'), 32);

I'm assuming that you can handle building up this command programatically from C#.

As an aside, at my company we have a number of these custom types defined as standard for lists of strings, doubles, ints and so on. We also make use of Oracle JPublisher to be able to map directly from these types into corresponding Java objects. I had a quick look around but I couldn't see any direct equivalents for C#. Just thought I'd mention it in case Java devs come across this question.

How to pass parameters in exec Oracle procedure which has array parameter

You are missing type reference while passing the arrays into the procedure,

try below,

BEGIN
   flange_derating_upsert('A1'
                         ,'2 inch Quad High Pressure'
                         ,'2'
                         ,'Quad High Pressure'
                         ,pressure_array(275, 235, 215, 195, 170, 140, 125, 110, 95, 80, 65)
                         ,temperature_array(100, 200, 300, 400, 500, 600, 650, 700, 750, 800, 850));
END;
/

OR

exec flange_derating_upsert('A1','2 inch Quad High Pressure','2','Quad High Pressure',pressure_array(275,235,215,195,170,140,125,110,95,80,65),temperature_array(100,200,300,400,500,600,650,700,750,800,850));

---

Related Topics