This discussion is archived
7 Replies Latest reply: Jan 22, 2013 8:21 AM by BillyVerreynne RSS

Need to return data from a query in different ways - Please help

user565033 Newbie
Currently Being Moderated
We are using 10g R2

I have a proc as follows that has a query with over 100 values in the select clause:

proc one( input param1, input_param2,.... output_cursor )
as
begin
open cursor for
select ...about 100 values with most of them being calculated
from table1, view 1, table2, table 3, view 2 ...
where ....
and table1.col1 = input param1
and table1.col2 = input param 2
and view1.col5 = input param5...

end;

I need to return the data that comes from the above query in different formats, columns for a report would be different from columns for screen A and different for screen B. I need only certain columns for a report and different set of columns for another screen. I have wrapper procs that get different input params. From the wrapper procs I intend to call the above proc but would like only selected values.

How can I accomplish this? Since my main goal is to select different columns for each wrapper I was thinking of insert the data from the above proc into global temp table and selecting whatever columns and order I want from the wrappers.

What do you think? Any other solutions?

Thanks

Edited by: user565033 on Jan 21, 2013 7:50 PM
  • 1. Re: Need to return data from a query in different ways - Please help
    rp0428 Guru
    Currently Being Moderated
    A CURSOR wll have all 100+ columns when you fetch each row. Just use the columns you need for each wrapper.

    Create two procedures; one for each wrapper. Modify the cursor in each procedure to return the columns needed.
  • 2. Re: Need to return data from a query in different ways - Please help
    817257 Newbie
    Currently Being Moderated
    using cursor will always return you same number of rows , writing different set of wrapper procedures to select the corresponding values is the only way.
  • 3. Re: Need to return data from a query in different ways - Please help
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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>
  • 4. Re: Need to return data from a query in different ways - Please help
    user565033 Newbie
    Currently Being Moderated
    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 )
    as
    begin
    open cursor for
    select ...about 100 values with most of them being calculated
    from table1, view 1, table2, table 3, view 2 ...
    where ....
    and table1.col1 = input param1
    and table1.col2 = input param 2
    and view1.col5 = input param5...
    end;

    proc wrapper1 (in1, in2, in3,..., out1 ref_cursor)
    as
    l_c ref_cursor;
    begin
    getData(in1, in2,...,l_c);

    open out1 for
    select col1, col7, col10, col15, col23....
    from l_c;
    end;

    proc wrapper2 (in1, in2, in3,..., out1 ref_cursor)
    as
    l_c ref_cursor;
    begin
    getData(in1, in2,...,l_c);

    open out1 for
    select col5, col7, col10, col19, col23, col25....
    from l_c; <---------- How do i do this with a cursor??
    end;
  • 5. Re: Need to return data from a query in different ways - Please help
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 &trade;. 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.
  • 6. Re: Need to return data from a query in different ways - Please help
    user565033 Newbie
    Currently Being Moderated
    Can u pls. provide an alternative solution for what I am trying to achieve?
  • 7. Re: Need to return data from a query in different ways - Please help
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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

    Do this:
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points