CODEWITHSUNDEEP
oracle11g
Naman Goyal
Naman Goyal

Reputation: 375

How to add rows into a cursor manually in stored procedure in oracle

I have a cursor in stored procedure. I want to select some data from a query and insert that data into cursor, then again select some data from another query and append that data into the same cursor.

How can I do this?

Upvotes: 3

Views: 5599

Answers (2)

Thiyagu ATR
Thiyagu ATR

Reputation: 2264

i don't know how procedure return values just see this one here 'm using simple ref_cursor 

create or replace function test_ref() return sys_refcursor is
     temp sys_refcursor;
   begin
     open temp for 'select * from hr.employees ;
     return v_rc;
   end;
   /

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231761

A cursor is a read-only handle for a SQL statement. A cursor has no data. You cannot append data to a cursor. The only thing you can do with a cursor is fetch the next row.

You can change the SQL statement that is used to open the cursor to UNION together the two different SQL statements, i.e.

OPEN rc FOR 
  SELECT <<column list>>
    FROM table1
  UNION ALL
  SELECT <<column list>>
    FROM table2;

RETURN rc;

Upvotes: 4

Related Questions