Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2007
    Posts
    7

    Unanswered: How to use union in procedures

    Hi to All,

    Can Anyone help me out how to use union, for more than one select query,when i am trying to use cursor to store the result of select queries?

    my procedure resembles like this,

    create or replace procedure myprocedure(p_recordset OUT SYS_REFCURSOR) AS
    begin
    open p_recordset for
    select empid from emp where location='xyz';
    union
    select empid from emp1 where location='abc';
    end;


    how can i resolve this,
    can any one give me a solution

    Thanks in Advance,
    Suresh Midde
    Last edited by sureshmidde; 09-11-07 at 05:19.

  2. #2
    Join Date
    Jun 2007
    Posts
    10
    begin
    for var in (select emp from empl union select emp from empl1)
    loop
    dbms_output.put_line(var.emp);
    end loop;
    end;

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Just remove the erroneous semi-colon:
    Code:
    create or replace procedure myprocedure(p_recordset OUT SYS_REFCURSOR) AS
    begin
    open p_recordset for
    select empid from emp where location='xyz' -- No semi-colon here please
    union
    select empid from emp1 where location='abc';
    end;

  4. #4
    Join Date
    Sep 2007
    Posts
    7
    Hi Tony,

    Thanks a lot for quick reply.
    Your suggestion worked.

    Regards
    Suresh Midde

Posting Permissions

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