Array in In() Clause Oracle Plsql

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.

PL/SQL where clause using IN() for an array

One of your problem is that you are defining namesArray type as Int but you are querying the name column which is varchar. The other problem is that you should change your where clause and cursor parameter part.

If you want to check Name column you should try the code below :

CREATE OR REPLACE TYPE namesArray AS TABLE OF varchar(255);

/

CURSOR luckyPeople(names namesArray) IS
select PersonID from Users
where Name IN(select Name from table(namesArray));

Oracle how to use array in where clause?

I want store the value in array.

This is how you store value:

CREATE OR REPLACE TYPE array_item IS object( id NUMBER, TYPE varchar2(3));

CREATE OR REPLACE TYPE array_collection IS VARRAY(10) OF array_item;


declare
--Initialization 
var_array array_collection:=array_collection();
begin
  ---Storing 3 elements
 var_array.extend(3);
 var_array(1):= array_item(1,'N');
 var_array(2):= array_item(2,'N');
 var_array(3):= array_item(3,'Y');

 for i in 1..var_array.count
 loop
  dbms_output.put_line( var_array(i).id ||'    '|| var_array(i).TYPE );
 end loop;

end;

You can use as :

SELECT ... ... 
  FROM myTable 
 WHERE (myTable.id, myTable.type) MEMBER OF var_array ;

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>

PL/SQL - Use List Variable in Where In Clause

Use a collection:

CREATE TYPE Varchar2TableType AS TABLE OF VARCHAR2(200);

Or use a built-in type like SYS.ODCIVARCHAR2LIST or SYS.ODCINUMBERLIST:

VARIABLE cursor REFCURSOR;

DECLARE
  your_collection SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
BEGIN
  your_collection.EXTEND( 100 );

  your_collection(  1) := 'Some value';
  your_collection(  2) := 'Some other value';
  -- ...
  your_collection(100) := DBMS_RANDOM.STRING( 'x', 20 );

  OPEN :cursor FOR
  SELECT t.*
  FROM   your_table t
         INNER JOIN
         TABLE( your_collection ) c
         ON t.id = c.COLUMN_VALUE;
END;
/

PRINT cursor;

Syntax for Arrays, Loops in Oracle PL/SQL

The problem certainly has a trivial SQL solution, but since this is PL/SQL practice (specifically), here are a few suggestions for improvement. Note that the code is almost correct; in particular, it is not clear what, if anything, throws the exact error you mentioned.

In the loop, you can assign to both local variables in a single select statement, as shown below. Notice also the where clause, where you must compare ISBN from the table to array(i), not to i. (That is the only error I found!)

for i in 1 .. array.count loop
  select category, retail
    into vCATEGORYtemp, vRETAILtemp
  from   books
  where  ISBN = array(i);

Finally, the assignment to vRETAILtemp can be simplified, using a case expression (not a case statement, which is a construct very similar to the if... then... elsif... else... end construct):

vRETAILtemp := vRETAILtemp * case vCATEGORYtemp when 'COMPUTER' then 0.7
                                                when 'FITNESS'  then 0.6
                                                when 'BUSINESS' then 0.8
                                                                else 0.9 end;

Oracle PL/SQL - How to create a simple array variable?

You can use VARRAY for a fixed-size array:

declare
   type array_t is varray(3) of varchar2(10);
   array array_t := array_t('Matt', 'Joanne', 'Robert');
begin
   for i in 1..array.count loop
       dbms_output.put_line(array(i));
   end loop;
end;

Or TABLE for an unbounded array:

...
   type array_t is table of varchar2(10);
...

The word "table" here has nothing to do with database tables, confusingly. Both methods create in-memory arrays.

With either of these you need to both initialise and extend the collection before adding elements:

declare
   type array_t is varray(3) of varchar2(10);
   array array_t := array_t(); -- Initialise it
begin
   for i in 1..3 loop
      array.extend(); -- Extend it
      array(i) := 'x';
   end loop;
end;

The first index is 1 not 0.

---

Related Topics