SS64 Forum

discov_arp

Member

Registered: 20 Aug 2012

Posts: 2

script select v/s sys_refcursor in proc

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)
IS
   V_Sql_String      VARCHAR2 (15000) := NULL;
BEGIN
     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;
END;

Any help is much appriciated!!!! any idea what could be the reason behind this!!!