I have a complex pl/sql process where I select a single row from a table. Then, depending on a number of complex conditions, I may need to update a few columns in that row, OR, I may need to use that row to create a new row, copying all values from the original row, but then changing a few values and inserting the new row.
I want this to work in the future without needing to change the code if new columns are added to the table.
To achieve this, I'd really like to do this:
<pre> Declare
Mytab_row mytable%rowtype;
Mytab_rid rowid;
Begin
Select M.*, M.rowid into mytab_row, mytab_rid
where ....</pre>
To be able to insert the "modified copy" row easily, I need to do:
<pre> mytab_row.key_val := '...';
mytab_row.col_name_n := 'new values, etc.';
Insert into mytable values mytab_row;</pre>
When updating some values, I need to update several columns in the table. Sometimes, the process updates all the columns named in the update statement, and sometimes, only a subset of the named columns are changed. But to simplify it, I want to use a single update:
<pre> mytab_row.col_name_a := 'updated value';
mytab_row.col_name_b := 'another value';
-- depending on conditions, additional columns are changed ...or not.
-- Then using a single update statement:
Update mytable
set col_name_a := mytab_row.colname_a,
col_name_b := mytab_row.colname_b,
col_name_c := mytab_row.colname_c,
etc...
where rowid = mytab_rid;</pre>
I hope the above examples demonstrate the need for both the rowtype record plus the rowid.
Unfortunately, these don't work:
This gives
PL/SQL: ORA-00913: too many values on the Insert:
<pre> declare
cursor C1 is select A.rowid, A.* from mytable A;
mytab_row C1%rowtype;
Begin
Insert into mytable values mytab_row;
End;</pre>
This gives:
PLS-00494: coercion into multiple record targets not supported on the Select:
<pre> Declare
mytab_row mytable%rowtype;
mytab_rid rowid;
Begin
Select A.*, B.rowid into mytab_row, mytab_rid
From mytable A, mytable B
where A.key1_val = 'AAA'
and A.key2 = '123'
and B.rowid = A.rowid;
End;</pre>
It appears I need to give up trying to use the rowid in the update, and just use the primary key values in the where clause of the Update statement. ...unless someone has a better suggestion.
Thanks in advance.