CODEWITHSUNDEEP
sqloracle-databaseplsql
Coat
Coat

Reputation: 717

How to use in statement with nested table

Hey there I have a function, and part of the function is to make sure that the selected value is within the passed in table of varchar2s. To start I declare a varchar2 table type like so.

create or replace type Varchar2Table is table of varchar2(200)

Then I have the function which accepts the nested table parameter and has a select statement on them.

function SelectPeople(inputNames Varchar2Table) return People
begin
--stuff
select * from person_table where name in inputNames; --line of interest
--more stuff
end;

This doesn't seem to work though, I get the following error:

ORA-00932: inconsistent datatypes: expected NUMBER got ENGSPL5.VARCHAR2TABLE

Any suggestions?

Upvotes: 2

Views: 847

Answers (1)

Jon Heller
Jon Heller

Reputation: 36807

The TABLE operator allows nested tables to be used in SQL statements. The function was also missing an IS and an INTO.

create or replace type Varchar2Table is table of varchar2(200);

create table person_table(id number, name varchar2(100));

create or replace function SelectPeople(inputNames Varchar2Table) return number
is --Missing "IS".
    type numberTable is table of number; --Need a collection to store results.
    numbers numberTable;
begin
    select id
    bulk collect into numbers --Missing "INTO".
    from person_table
    where name in (select column_value from table(inputNames)); --Missing "TABLE".
    --Alternatively a multiset condition can be used.
    --where name member of inputNames;

    --Dummy return value to make the function compile.
    return 1;
end;
/

Upvotes: 3

Related Questions