Best way to check if SYS_REFCURSOR is empty
You need to execute a FETCH against the cursor prior to using the %FOUND attribute. Change your code to something like
DECLARE recs_Table SYS_REFCURSOR; nTable_1_value NUMBER; nTable_2_value NUMBER;begin open recs_Table for select * from table1, table2; FETCH recs_Table INTO nTable_1_value, nTable_2_value; if recs_Table%found then --do this else --do that end if;end;
Note that the way you'll probably need to add variables to the INTO clause of the FETCH statement, one for each column in TABLE1 and TABLE2. Note also that the way this cursor is written you'll probably get more rows returned than you might expect; because there is no join criteria specified you'll get what's called a Cartesian join, where each row in TABLE1 is joined to each row in TABLE2 - thus, the number of rows you'll get back is (# of rows in TABLE1) * (# of rows in TABLE2).
A potentially simpler way to do this would be to use a cursor FOR loop, as follows:
DECLARE bData_found BOOLEAN := FALSE;begin FOR aRow IN (select * from table1, table2) LOOP -- If the program gets here, it means a row was fetched -- do this bData_found := TRUE; EXIT; -- if you only care if data was found and don't want to -- process all the rows END LOOP; IF NOT bData_found THEN -- do that END IF;end;
Share and enjoy.
we use two procedures to execute the result
create or replace procedure pro_sample(recs_Table out SYS_REFCURSOR) is begin open recs_Table for select * from table1, table2; end;
this above procedure will be used to open a cursor
create or replace procedure pro_sample(recs_Table out SYS_REFCURSOR) is sam sys_refcursor; var number; -- if you have any variables then declare them begin pro_sample(sam); fetch sam into var; if sam%found then --do this else --do that end if; close sam; end;
the above procedure will help you to know whether the cursor contains rows or not
create or replace procedure pro_sample(recs_Table out SYS_REFCURSOR) is begin open recs_Table for select a,b,c,d from table1, table2; end;create or replace function haveRows_pro_sample issam sys_refcursor;var varchar(200);varOut number:=0; begin pro_sample(sam); fetch sam into var,var,var,var; if sam%found then varOut :=1; end if; return varOut; end;