CODEWITHSUNDEEP
arraysplsql
Łukasz
Łukasz

Reputation: 2172

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

Simple table:

CREATE TABLE Users (
  PersonID int,
  Name varchar(255),
  Salary int
);

I have to write a cursor that will be used in a procedure. Cursor is supposed to return records whose Name field belongs to the array.

CREATE OR REPLACE TYPE namesArray AS TABLE OF int;
/

CURSOR luckyPeople(names IN namesArray) IS
select PersonID from Users
where Name IN(namesArray); //how this can be solved?

How can I use the array in the select clause?

thanks

Upvotes: 1

Views: 2054

Answers (2)

Andrew Jon Dodds
Andrew Jon Dodds

Reputation: 184

Surely 

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

and

CURSOR luckyPeople(names IN namesArray) IS
select PersonID from Users, table(names) tnames
where Name = tnames.column_value;

Upvotes: 1

Hasan Alper Ocalan
Hasan Alper Ocalan

Reputation: 318

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));

Upvotes: 3

Related Questions