combining result sets
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: combining result sets

  1. #1
    Join Date
    Jan 2003
    Location
    St. Louis, USA
    Posts
    3

    combining result sets

    Hi,

    I would like to know if mutiple result sets in PL/SQL can be combined into one resultset. These individual result sets will have the same columns coming from the same tables, but from different schemas.


    Example code
    //Package
    CREATE OR REPLACE PACKAGE TEST
    AS

    TYPE ObjectRC IS REF CURSOR;

    FUNCTION SelectData
    RETURN ObjectRC;

    END TEST;

    //Package Body
    CREATE OR REPLACE PACKAGE BODY TEST
    AS
    FUNCTION SelectData
    RETURN TEST.ObjectRC
    IS
    resultsetALL TEST.ObjectRC;
    resultset1 TEST.ObjectRC;
    resultset2 TEST.ObjectRC;


    BEGIN

    OPEN resultset1 FOR
    SELECT col1,
    col2
    FROM schema1.Test;

    OPEN resultset2 FOR
    SELECT col1,
    col2
    FROM schema2.Test;

    resultsetALL := resultset1 + resultset2; ????????????

    RETURN resultsetALL;

    END SelectData;
    END TEST;


    In the above example, I need to somehow combine resultset1 and resultset2 and return as one.

    Appreciate your help

    Reddy

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Code:
    OPEN resultset1 FOR
    SELECT col1,
    col2
    FROM schema1.Test
    UNION ALL
    SELECT col1,
    col2
    FROM schema2.Test;
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width