I have a very large program (2000+ lines) and I want to try to break
it down into smaller pieces, make it more modular. I have a cursor
with 80 fields. While in the cursor loop, I want to call a procedure
and pass the whole cursor record. It will do some processing on the
record and then return the record to the calling procedure so that the
updated values can be used. How can I do this?

....Thanks

For Example:

CREATE OR REPLACE PROCEDURE proc1
IS
v_result NUMBER;
CURSOR emp_cursor
IS
SELECT * FROM rmp_table;

BEGIN
FOR emp_rec IN emp_cursor LOOP
IF emp_rec.termination_date > '01-jan-2004 THEN
proc2(emp_rec, v_result);
END IF;
IF v_result > 0 THEN
DBMS_OUTPUT.PUT_LINE(emp_rec.emp_no || ',' || emp_rec.last_name
....
END IF;
NEXT;
END;
/

PROCEDURE proc2(emp_rec IN OUT SomeTpyeOfRec, v_result OUT NUMBER)
IS
BEGIN
IF emp_rec.pension_type = 'DC' THEN
emp_rec.pension_type := 'Defined Contribution';
v_result := v_result + 1;
END IF;
IF emp_rec.ins_type = '1x' THEN
emp_rec.ins_type := 'One Times Salary';
v_result := v_result + 1;
END IF;
etc...
END;
/

Keith wrote:

Turn it into a package.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

keith_newsg (AT) yahoo (DOT) ca (Keith) wrote in news:f1c2be54.0411161206.457669c6
@posting.google.com:

There are several ways you can do this. Keeping your code just the way it
is, just replace your SomeTpyeOfRec with rmp_table%type.

You could also package this and pull the cursor declaration outside of
proc1, and then replace SomeTpyeOfRec with emp_cursor%type.

Watson (the pencil neck) Davis

On 16 Nov 2004 12:06:45 -0800, Keith wrote:

Sample procedure:

CREATE PROCEDURE xx_patrick_modproc(p_row IN OUT xx_patrick_test%ROWTYPE)
AS
BEGIN
p_row.name := 'modified';
END;

xx_patrick_test is a small table, but also could be a cursor consisting of
just one attribute (name).

You call it like this:

declare
begin
for rec1 in (select * from xx_patrick_test) loop
dbms_output.put_line('Before:'||rec1.name);
xx_patrick_modproc(rec1);
dbms_output.put_line('After:'||rec1.name)
end loop;
end;

Anyway you might already found an answer, because i realize this thread is
already almost a month old. Anyway, should have realized before starting to
type

Patrick