This content has been marked as final. Show 7 replies
You need to clearly separate roles and responsibilities. The PL/SQL code that creates and supplies a cursor handle is server code tasked to supply data. The code that makes the call for server data, is responsible for formatting and rendering that data.
Thus moving data formatting into the server code needs to be question. Simple example. Cursor does not return invoice date as a date - but formats it into a string using TO_CHAR().
This works for client1 - as that is the date format expected. However, client2 has different International settings and specifies a different date format. Invoice date, formatted into a string by the server, now renders in the wrong format on client2.
Server code should not be concerned with rendering and formatting of data send to a client.
As for the idea to use a global temp table is ..., well to put it nicely, it smells. Badly.
The single most expensive operation on a database platform is I/O. And now you want to read server data and write it to temporary storage, and the read data from temporary storage to return to the client? What on earth for!? Why purposefully increase the size of the I/O workload? Why decrease performance and undermine scalability?
Provide a proper abstraction interface to the client. Enable it to specify (as simplistically as possible) what it wants ito data. There are a number of ways to design and implement this in PL/SQL. Simplistic example:
SQL> create or replace package Employees as 2 3 EMP_FULL_DETAILS constant integer := 1; 4 EMP_BASIC_DETAILS constant integer := 2; 5 6 procedure GetEmpByID( 7 cur out sys_refcursor, 8 empID in emp.empno%type, 9 template in integer default EMP_BASIC_DETAILS 10 ); 11 12 procedure GetEmpByName( 13 cur out sys_refcursor, 14 empName in emp.ename%type, 15 template in integer default EMP_BASIC_DETAILS 16 ); 17 end; 18 / Package created. SQL> SQL> create or replace package body Employees as 2 3 type TArray is table of varchar2(32767); 4 5 TemplateList constant TArray := 6 new TArray( 7 'EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ', 8 'EMPNO, ENAME, JOB ' 9 ); 10 11 procedure GetEmpByID( 12 cur out sys_refcursor, 13 empID in emp.empno%type, 14 template in integer default EMP_BASIC_DETAILS 15 ) is 16 sqlSelect varchar2(32767); 17 begin 18 sqlSelect := 19 'select '||TemplateList(template)|| 20 'from emp where empno = :empID'; 21 22 open cur for sqlSelect using empID; 23 end; 24 25 procedure GetEmpByName( 26 cur out sys_refcursor, 27 empName in emp.ename%type, 28 template in integer default EMP_BASIC_DETAILS 29 ) is 30 sqlSelect varchar2(32767); 31 begin 32 sqlSelect := 33 'select '||TemplateList(template)|| 34 'from emp where ename like :empName'; 35 open cur for sqlSelect using empName; 36 end; 37 38 39 end; 40 / Package body created. SQL> SQL> var c refcursor SQL> SQL> exec Employees.GetEmpByID( :c, 7499 ); PL/SQL procedure successfully completed. SQL> print c EMPNO ENAME JOB ---------- ---------- ---------- 7499 ALLEN SALESMAN SQL> SQL> exec Employees.GetEmpByName( :c, 'A%', Employees.EMP_FULL_DETAILS ); PL/SQL procedure successfully completed. SQL> print c EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- ---------- ---------- ------------------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 1981/02/20 00:00:00 1600 300 30 7876 ADAMS CLERK 7788 1987/05/23 00:00:00 1100 20 SQL>
Thanks Billy. global temp tables and I/O issues really helped.
My question is that I have a proc that returns data in a cursor. In the wrapper procs, I want to select a subset of the columns from the cursor and return it to the front end.
proc getData( input param1, input_param2,.... output_cursor )
open cursor for
select ...about 100 values with most of them being calculated
from table1, view 1, table2, table 3, view 2 ...
and table1.col1 = input param1
and table1.col2 = input param 2
and view1.col5 = input param5...
proc wrapper1 (in1, in2, in3,..., out1 ref_cursor)
open out1 for
select col1, col7, col10, col15, col23....
proc wrapper2 (in1, in2, in3,..., out1 ref_cursor)
open out1 for
select col5, col7, col10, col19, col23, col25....
from l_c; <---------- How do i do this with a cursor??
You should not attempt to wrap a cursor with a wrapper that transforms/modify that cursor's output.
There are very times that this makes sense. Such as complex data transformation in parallel (via a PL/SQL pipeline).
However, running standard cursors via pipelines to "format" output and selectively determine what columns to show and what to suppress, is a Bad Idea ™. Something that should be avoided.
The basic concept is to use PL/SQL code to create cursors. Not use PL/SQL code to wrap cursors into new new cursors.
Use PL/SQL more extensively to apply conditional logic to create the required "custom" ref cursor that the caller expects - as my basic example above shows.
So instead of doing this:
a) PL/SQL code create a ref cursor
b) PL/SQL code wraps ref cursor, and apply transformation
c) Wrapped code returns a new ref cursor on an existing ref cursor
a) PL/SQL code applies conditional process/rules/logic
b) custom SQL is created and ref cursor returned
A cursor is basically a SQL program (executable code). Not a data or result set. An important point to note.
If you create a cursor and wrap it, in order to transform the output, you need another cursor. So you have a program that outputs data. You now create a second program to read the first program's data, change it, and output it in a different format.
This is slower than to simply create the first program to output the actual format desired.
So instead of wrapping the cursor with a cursor (the only approach that addresses your <i>wrap-cursor-to-reformat-data</i> requirement), use the powerful PL/SQL language to directly create a cursor that outputs the desired data in the desired format.