Re: How to define variables for ROW update with ROWID

Dereck L. Dietz wrote:
> Assume there is a table defined roughly as:
>
> TABLE
> column_1
> column_2
> column_3
> column_4
> column_5
>
> I declare a cursor as below including a ROWID for use for later updating.
>
> CURSOR cr_cursor
> IS
> SELECT column_1,
> column_2,
> column_3,
> column_4,
> column_5,
> ROWID
> FROM table;
>
> My question is, how do I define my variables to be able to do an update
> similar to the one below:
>
> UPDATE table
> SET ROW = v_table
> WHERE ROWID = v_rowid;
>
> Other than to define one variable with a record structure of the table AND
> the rowid and two more: a record of the table structure and a rowid record
> what else is possible?

Here's one way:

TYPE parent_rec IS RECORD (
part_num dbms_sql.number_table,
part_name dbms_sql.varchar2_table);

p_rec parent_rec;

CURSOR c IS
SELECT part_num, part_name
FROM parent;

BEGIN
   OPEN c;
   LOOP
     FETCH c BULK COLLECT INTO p_rec.part_num, p_rec.part_name

Here is another:

TYPE PartNum IS TABLE OF parent.part_num%TYPE INDEX BY BINARY_INTEGER;
pnum_t PartNum;

TYPE PartName IS TABLE OF parent.part_name%TYPE INDEX BY BINARY_INTEGER;
pnam_t PartName;

BEGIN
   SELECT part_num, part_name
   BULK COLLECT INTO pnum_t, pnam_t
   FROM parent;

These two examples are extracted from:
http://www.psoug.org/reference/array_processing.html

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org