Stored Procedure Update a Cursor then Return the Result

Question

I am trying to create an ORACLE stored procedure that selects some info into a cursor, run some updates on that cursor. Then return the contents of the cursor, this is to move away from the current setup that involves creating a temp table and running queries on that.

Everything that I have tried has resulted in the contents of the cursor being out of scope of any updates etc that I want to run..

Essentially I'm trying to:

Select some data
Do some processing on it
Return some of the processed data (based on a select)

Please help!

My current code is along the lines of:

create or replace
PROCEDURE TEMP_REPORT
(
  returnTable OUT SYS_REFCURSOR
)
IS
  CURSOR resultTable
  IS
  SELECT FNAME,SALARY FROM STAFF;

   NAME     VARCHAR2 (10);
   SALARY   VARCHAR2 (10);

BEGIN


    Update resultTable set Salary = (salary * 1.1);
    --- some more processing here
    ---- now return the datarows
    Cursor returnTable from select name, salary from resultTable where salary > 1000;


END TEMP_REPORT;

I dont think you can update the data in a cursor. Cursors are just pointers to a dataset (i.e. select statement). One thing you can try is using collections (Nested Tables of User defined types) — Jafar Kofahi, Jul 18 '13 at 14:38
You can't alter data "in the cursor". A cursor is not some sort of data structure with all the results "in" it that you can manipulate. — Bob Jarvis - Слава Україні, Jul 18 '13 at 14:42
As for the "updates" you want to run, are those super-complicated? With some SQL gymnastics you *may* be able to calculate everything in a single query. If not, a pipelined function may work for you. I finally got around to my first pipelined function a few months ago and it was surprisingly easy, though you do have to create at least two Oracle TYPEs and some DBAs may balk at that. — Ed Gibbs, Jul 18 '13 at 14:52
I have updated the code above to show what I am trying to do (I know the code doesn't work). Is it not possible to create another cursor that contains the updates values of the original cursor and so on, then return the final cursor? Or would all of this just be better done in some kind of disposable view? — Chris, Jul 18 '13 at 15:07
Sounds like you want a pipelined function that will programatically pipe rows (altered or not) depending on some business logic. See [here](http://stackoverflow.com/questions/5983821/how-to-create-oracle-stored-procedure-which-can-return-specific-entities-as-well/5993977#5993977) for an example — tbone, Jul 18 '13 at 15:16

score 1 · Accepted Answer · answered Jul 18 '13 at 14:38

what not try something simpler like :

declare

   NAME     VARCHAR2 (10);
   SALARY   VARCHAR2 (10);

begin

FOR cid IN ( select FNAME,SALARY FROM STAFF) LOOP

      NAME := cid.NAME;
      SALARY := cid.SALARY;

      DBMS_OUTPUT.
       put_line (
            NAME
         || ' | '
         || SALARY);
END LOOP;

END;

Stored Procedure Update a Cursor then Return the Result

1 Answers1