Skip to Main Content
  • Questions
  • merge the cursor output of multiple stored procedures into one result

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Anitha.

Asked: November 14, 2017 - 11:00 pm UTC

Last updated: November 15, 2017 - 6:19 am UTC

Version: Oracle 10

Viewed 1000+ times

You Asked

Hello Tom,

Can you please tell me how can i merge the output of 6 stored procedures into one result.
The output of each stored procedure is a cursor which holds n number of records(records data structure is same).

I have to merge the data and remove duplicates and return the result to Java code for display.


Thanks,
Anitha.

and Connor said...

Drag them into a plsql table type, or if the set is large, into a global temporary table, eg

SQL> create or replace
  2  type my_row is object (
  3     OWNER                                                          VARCHAR2(128)
  4    ,OBJECT_NAME                                                    VARCHAR2(128)
  5    ,SUBOBJECT_NAME                                                 VARCHAR2(128)
  6    ,OBJECT_ID                                                      NUMBER
  7    ,DATA_OBJECT_ID                                                 NUMBER
  8    ,OBJECT_TYPE                                                    VARCHAR2(23)
  9    );
 10  /

Type created.

SQL>
SQL> create or replace
  2  type my_row_list is table of my_row;
  3  /

Type created.

SQL>
SQL> set serverout on
SQL> variable rc refcursor
SQL> declare
  2    cursor c1 is
  3      select owner,object_name,subobject_name,object_id,data_object_id,object_type
  4      from dba_objects where object_id <= 50;
  5
  6    cursor c2 is
  7      select owner,object_name,subobject_name,object_id,data_object_id,object_type
  8      from dba_objects where object_type = 'INDEX'
  9      and object_id between 50 and 120;
 10
 11    r1 my_row_list := my_row_list();
 12    r2 my_row_list := my_row_list();
 13  begin
 14    for i in c1 loop
 15      r1.extend;
 16      r1(r1.count) := my_row(i.owner,i.object_name,i.subobject_name,i.object_id,i.data_object_id,i.object_type);
 17    end loop;
 18    dbms_output.put_line('r1.count='||r1.count);
 19
 20    for i in c2 loop
 21      r2.extend;
 22      r2(r2.count) := my_row(i.owner,i.object_name,i.subobject_name,i.object_id,i.data_object_id,i.object_type);
 23    end loop;
 24    dbms_output.put_line('r2.count='||r2.count);
 25
 26    open :rc for
 27      select * from table(r1)
 28      union
 29      select * from table(r2);
 30  end;
 31  /
r1.count=49
r2.count=43

PL/SQL procedure successfully completed.

SQL>
SQL> print rc

OWNER                          OBJECT_NAME
------------------------------ ----------------------------------------
SUBOBJECT_NAME
----------------------------------------------------------------------------------------------------
 OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -----------------------
SYS                            CCOL$

        32             29 TABLE

SYS                            CDEF$

        31             29 TABLE

SYS                            CLU$

         5              2 TABLE

SYS                            COL$

        21              2 TABLE

SYS                            CON$

        28             28 TABLE

SYS                            C_COBJ#

        29             29 CLUSTER

SYS                            C_FILE#_BLOCK#

         8              8 CLUSTER

SYS                            C_OBJ#

         2              2 CLUSTER

SYS                            C_TS#

         6              6 CLUSTER

SYS                            C_USER#

        10             10 CLUSTER

SYS                            FET$

        12              6 TABLE

SYS                            FILE$

        17             17 TABLE

SYS                            ICOL$

        20              2 TABLE

SYS                            IND$

        19              2 TABLE

SYS                            I_ACCESS1

        87             87 INDEX

SYS                            I_CCOL1

        57             57 INDEX

SYS                            I_CCOL2

        58             58 INDEX

SYS                            I_CDEF1

        53             53 INDEX

SYS                            I_CDEF2

        54             54 INDEX

SYS                            I_CDEF3

        55             55 INDEX

SYS                            I_CDEF4

        56             56 INDEX

SYS                            I_COBJ#

        30             30 INDEX

SYS                            I_COL1

        48             48 INDEX


OWNER                          OBJECT_NAME
------------------------------ ----------------------------------------
SUBOBJECT_NAME
----------------------------------------------------------------------------------------------------
 OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -----------------------
SYS                            I_COL2

        49             49 INDEX

SYS                            I_COL3

        50             50 INDEX

SYS                            I_COLTYPE1

       112            112 INDEX

SYS                            I_COLTYPE2

       113            113 INDEX

SYS                            I_CON1

        51             51 INDEX

SYS                            I_CON2

        52             52 INDEX

SYS                            I_DEFERRED_STG1

        82             82 INDEX

SYS                            I_DEPENDENCY1

        85             85 INDEX

SYS                            I_DEPENDENCY2

        86             86 INDEX

SYS                            I_FILE#_BLOCK#

         9              9 INDEX

SYS                            I_FILE1

        43             43 INDEX

SYS                            I_FILE2

        44             44 INDEX

SYS                            I_FIXED_OBJ$_OBJ#

        72             72 INDEX

SYS                            I_HH_OBJ#_COL#

        69             69 INDEX

SYS                            I_HH_OBJ#_INTCOL#

        70             70 INDEX

SYS                            I_H_OBJ#_COL#

        67             67 INDEX

SYS                            I_ICOL1

        42             42 INDEX

SYS                            I_IDNSEQ1

       107            107 INDEX

SYS                            I_IND1

        41             41 INDEX

SYS                            I_IND_STATS$_OBJ#

        76             76 INDEX

SYS                            I_LOB1

       109            109 INDEX

SYS                            I_LOB2

       110            110 INDEX

SYS                            I_NTAB1

       117            117 INDEX


OWNER                          OBJECT_NAME
------------------------------ ----------------------------------------
SUBOBJECT_NAME
----------------------------------------------------------------------------------------------------
 OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -----------------------
SYS                            I_NTAB2

       118            118 INDEX

SYS                            I_NTAB3

       119            119 INDEX

SYS                            I_OBJ#

         3              3 INDEX

SYS                            I_OBJ#_INTCOL#

        65             65 INDEX

SYS                            I_OBJ1

        36             36 INDEX

SYS                            I_OBJ2

        37             37 INDEX

SYS                            I_OBJ3

        38             38 INDEX

SYS                            I_OBJ4

        39             39 INDEX

SYS                            I_OBJ5

        40             40 INDEX

SYS                            I_OBJAUTH1

        62             62 INDEX

SYS                            I_OBJAUTH2

        63             63 INDEX

SYS                            I_PARTOBJ$

        80             80 INDEX

SYS                            I_PROXY_DATA$

        24             24 INDEX

SYS                            I_PROXY_ROLE_DATA$_1

        26             26 INDEX

SYS                            I_PROXY_ROLE_DATA$_2

        27             27 INDEX

SYS                            I_SEQ1

       105            105 INDEX

SYS                            I_STATS_OBJ#

        78             78 INDEX

SYS                            I_SUBCOLTYPE1

       115            115 INDEX

SYS                            I_SUPEROBJ1

        98             98 INDEX

SYS                            I_SUPEROBJ2

        99             99 INDEX

SYS                            I_SYN1

       103            103 INDEX

SYS                            I_SYN2

       104            104 INDEX

SYS                            I_TAB1

        33             33 INDEX


OWNER                          OBJECT_NAME
------------------------------ ----------------------------------------
SUBOBJECT_NAME
----------------------------------------------------------------------------------------------------
 OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -----------------------
SYS                            I_TAB_STATS$_OBJ#

        74             74 INDEX

SYS                            I_TS#

         7              7 INDEX

SYS                            I_TS1

        45             45 INDEX

SYS                            I_TYPED_VIEW1

       102            102 INDEX

SYS                            I_UGROUP1

        91             91 INDEX

SYS                            I_UGROUP2

        92             92 INDEX

SYS                            I_UNDO1

        34             34 INDEX

SYS                            I_UNDO2

        35             35 INDEX

SYS                            I_USER#

        11             11 INDEX

SYS                            I_USER1

        46             46 INDEX

SYS                            I_USER2

        47             47 INDEX

SYS                            I_USERAUTH1

        89             89 INDEX

SYS                            I_VIEW1

       101            101 INDEX

SYS                            OBJ$

        18             18 TABLE

SYS                            PROXY_DATA$

        23             23 TABLE

SYS                            PROXY_ROLE_DATA$

        25             25 TABLE

SYS                            SEG$

        14              8 TABLE

SYS                            TAB$

         4              2 TABLE

SYS                            TS$

        16              6 TABLE

SYS                            UET$

        13              8 TABLE

SYS                            UNDO$

        15             15 TABLE

SYS                            USER$

        22             10 TABLE


91 rows selected.

SQL>
SQL>
SQL>


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Anitha, December 10, 2017 - 11:38 pm UTC

Thanks

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library