Skip to Main Content
  • Questions
  • SELECT Statement With Cursor In The Condition


Question and Answer

Chris Saxon

Thanks for the question, Alexei.

Asked: November 30, 2020 - 8:00 pm UTC

Last updated: December 01, 2020 - 8:56 am UTC

Version: Oracle 12C

Viewed 100+ times

You Asked


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 CURSOR

Could 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 ... )

with LiveSQL Test Case:

and we said...

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 
  l_rc sys_refcursor; 
  open l_rc for 
    select account_key from accounts 
    where last_name like 'A%'; 
  return l_rc; 

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;

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


select * from accounts where account_key not in ( 
  select * from table ( get_keys_tab ( get_keys ) ) 

3             BCD

More to Explore


Check out more PL/SQL tutorials on our LiveSQL tool.