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

Question

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

Hasan Alper Ocalan · Accepted Answer · 2017-02-06 12:36:47Z

3

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

answered Feb 6, 2017 at 12:36

Hasan Alper Ocalan

3181 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Łukasz Over a year ago

The int vs varchar was a copy paste error. Provided solution works. Thank you

Andrew Jon Dodds · Accepted Answer · 2017-02-06 14:28:23Z

1

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;

answered Feb 6, 2017 at 14:28

Andrew Jon Dodds

1844 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related