Reputation: 2172
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
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
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