Thanks for the question, JOSEPH.
Asked: January 19, 2017 - 1:09 am UTC
Last updated: January 20, 2017 - 12:20 pm UTC
Version: 11g
Viewed 1000+ times
You Asked
Hi Team,
I am returning a data set to an interface via a ref cursor using the below code:-
OPEN pRefCur FOR
SELECT ct.ct_value1 AS A,
ct.ct_value2 AS B,
getArray(ct.ct_value4, ct.ct_value12) AS C,
getArray(ct.ct_value5, ct.ct_value12) AS D,
getArray(ct.ct_value6, ct.ct_value12) AS E,
getResArray(ct.ct_value12) AS F,
getResCodeArray(cch.he_code, cch.cch_code, ct.ct_value4, ct.ct_value12) AS G,
getRBArray(ct.ct_value4, ct.ct_value12) AS H,
getRAArray(cch.he_code, cch.cch_code, ct.ct_value4, ct.ct_value12) AS I,
getAdjRArray(ct.ct_value4, ct.ct_value12) AS J
from ...
Except for A & B, everything else (C - J)is arrays in the the data set.
My question is
1 - If I use code all the above functions as pipelined table functions. Will there be any visible performance improvement. Is that recomended.
2 - To return a data set with each record having an array, is there a better way?
and Connor said...
"I use code all the above functions as pipelined table functions"
Unlikely. Pipelining is all about the rows being returned to the client, not the rows within an array.
"To return a data set with each record having an array, is there a better way?"
That really depends on the client. If you will definitely be using all elements in each array, then probably not. If some arrays will only be access under certain circumstances, then you could look at perhaps using cursor variables, so that you will only fetch that data from each "subordinate" array when required.
SQL> select deptno,
2 dname,
3 cursor( select ename from scott.emp where emp.deptno = dept.deptno )
4 from scott.dept
5 where deptno = 10 ;
DEPTNO DNAME CURSOR(SELECTENAMEFR
---------- -------------- --------------------
10 ACCOUNTING CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
ENAME
----------
CLARK
KING
MILLER
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment