Hi sir,
Have facing issue with the time. The query which is used in dynamic cursor type like 'open for select" taking 550 ms but the output we used as collection passing as pipelined value. At that time it's taking more time like 50 s. Please give us few solutions.
Sample code like,
Function fn-name (a number, b varchar, c varchar)
Return records as pipelined
Is
Begin
Case when a = 1
Then
Open c for
Select * from t1 where column = c;
When a = 2
Then
Open c for
Select * from t1 where column =c;
End case;
Fetch c into output collection variable;
Close c;
Return output collection variable;
End;
Collection variables are created already and given you as sample.
Thanks,
Manoj
Sorry, I can't replicate that result. We'll need to see your test case. Here is mine
SQL> @drop t
Y1 Y2
-------------------------------------------------------------------------------------------- -------------------------
TABLE cascade constraints purge
1 row selected.
Table dropped.
SQL> @drop sample_list
Y1 Y2
-------------------------------------------------------------------------------------------- -------------------------
TYPE
1 row selected.
Type dropped.
SQL> @drop sample_row
Y1 Y2
-------------------------------------------------------------------------------------------- -------------------------
TYPE
1 row selected.
Type dropped.
SQL>
SQL> create or replace
2 type sample_row as object
3 ( x int,
4 y date,
5 z varchar2(200)
6 );
7 /
Type created.
SQL>
SQL> create or replace
2 type sample_list as table of sample_row
3 /
Type created.
SQL>
SQL> create table t as select d.* from dba_objects d,
2 ( select 1 from dual connect by level <= 10 );
Table created.
SQL> select count(*) from t;
COUNT(*)
----------
795110
1 row selected.
SQL>
SQL> create or replace
2 function f1(p_owner varchar2) return sample_list is
3 rc sys_refcursor;
4 result sample_list := sample_list();
5 begin
6 open rc for
7 'select sample_row(object_id,created,object_name) from t where owner = :1' using p_owner;
8 fetch rc
9 bulk collect into result;
10 close rc;
11 return result;
12 end;
13 /
Function created.
SQL>
SQL> create or replace
2 function f2(p_owner varchar2) return sample_list pipelined is
3 rc sys_refcursor;
4 result sample_list := sample_list();
5 begin
6 open rc for
7 'select sample_row(object_id,created,object_name) from t where owner = :1' using p_owner;
8 fetch rc
9 bulk collect into result;
10 close rc;
11 for i in 1 .. result.count
12 loop
13 pipe row ( result(i) );
14 end loop;
15 return;
16 end;
17 /
Function created.
SQL>
SQL> set timing on
SQL> select max(x), count(*) from table(f1('SYS'));
MAX(X) COUNT(*)
---------- ----------
107230 519710
1 row selected.
Elapsed: 00:00:01.60
SQL> select max(x), count(*) from table(f2('SYS'));
MAX(X) COUNT(*)
---------- ----------
107230 519710
1 row selected.
Elapsed: 00:00:00.80
SQL>
SQL>
SQL>