I have a select which returns me 20K records in 1-2 min, but same select statment when i put in a procedure and open a sys_refcursor it takes 30 mins to get the results.
This takes 1 min
Select * from xxxxx;
and below procedure takes 30 mins;
CREATE OR REPLACE PROCEDURE C_STATUS (
v_a_id IN VARCHAR2 DEFAULT NULL,
p_ReportData IN OUT SYS_REFCURSOR)
V_Sql_String VARCHAR2 (15000) := NULL;
call Procedure to poulate table tb;
v_Sql_String = 'select * from dbo.table tb where tb.id = ''x''';
Open p_ReportData For v_Sql_String;
Any help is much appriciated!!!! any idea what could be the reason behind this!!!