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

Breadcrumb

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

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

More to Explore

PL/SQL

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