CODEWITHSUNDEEP
oracle-databaseplsql
Smashinsameer
Smashinsameer

Reputation: 41

How to dynamically pass select query to a procedure/function and get the record count using ref cursor?

FUNCTION prc_check_src_dest_cnt (p_source        IN SYS_REFCURSOR)
      RETURN VARCHAR2
   IS

p_source -- how to find the count of this cursor which is dynamically changing query passed by user?

Upvotes: 0

Views: 374

Answers (1)

mahi_0707
mahi_0707

Reputation: 1062

If you can pass SQL Query text instead, you can try the below,as Alex pointed out in the comments

Create or replace function FUN_REF_COUNT(IN_SQL varchar2)
RETURN number
AS
    v_count number;
Begin
    execute immediate 'select count(*) from (' || IN_SQL || ' )'
    into v_count;

    return v_count;
end;
/

Calling Function:

select FUN_REF_COUNT('select * from all_tables') FUNCTION_OUTPUT 
from dual ;

--Output  
!----------!-----!
FUNCTION_OUTPUT 
    1697

Upvotes: 1

Related Questions