7 Replies Latest reply: Jan 22, 2013 10:21 AM by Billy~Verreynne RSS

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

    user565033
      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
          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
            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
              Billy~Verreynne
              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
                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
                  Billy~Verreynne
                  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
                    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
                      Billy~Verreynne
                      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.