Hello
I have a use case, which has the following steps:
1. get a list of account keys using a function
get_keys;
2. select results from a table where account keys
NOT in the function results i.e.
SELECT * FROM accounts WHERE account_key NOT IN ( get_keys );
I am getting the following error:
ORA-00932: inconsistent datatypes: expected - got CURSORCould you please advise if there is a way to use a function in such a way instead of a nested SELECT like
... NOT IN ( SELECT ... )
You can't query a ref cursor like that.
To retrieve the values in SQL, wrap the output of get_keys in a (pipelined) table function. For example:
create table accounts (
account_key varchar2(20 byte),
last_name varchar2(20 byte)
)
/
insert into accounts values ( '1', 'AAA' );
insert into accounts values ( '2', 'ABC' );
insert into accounts values ( '3', 'BCD' );
create or replace function get_keys
return sys_refcursor
is
l_rc sys_refcursor;
begin
open l_rc for
select account_key from accounts
where last_name like 'A%';
return l_rc;
end;
/
create or replace type keys_nt is table of number;
/
create or replace function get_keys_tab ( keys_cur sys_refcursor )
return keys_nt pipelined as
keys keys_nt;
begin
loop
fetch keys_cur
bulk collect into keys
limit 100;
exit when keys.count = 0;
for ky in 1 .. keys.count loop
pipe row ( keys ( ky ) );
end loop;
end loop;
end get_keys_tab;
/
select * from table ( get_keys_tab ( get_keys ) );
COLUMN_VALUE
1
2
select * from accounts where account_key not in (
select * from table ( get_keys_tab ( get_keys ) )
);
ACCOUNT_KEY LAST_NAME
3 BCD