Re: Fine point on rowtype and update

• Post for FREE via your newsreader at post.usenet.com ****

Hi Roger

"Roger Redford" <dba_222_at_yahoo.com> wrote in message news:a8c29269.0408190511.23012c72_at_posting.google.com...

> I'm using a rowtype, rather than declaring variables
> for each field.
>
>
> Is it possible to update the entire row, with the
> rowtype variable? That is, without listing each
> field?
>
> I tried this, but I got compilation errors.
> (From Oracle documentation.)
>
> Update record
> Set row = r_record
> Where record_id = P_Record_ID;
>
>
> Errors for PROCEDURE
> clsLINE/COL ERROR
> -------- ---------------------------------------------------------
> 73/2 PL/SQL: SQL Statement ignored
> 74/7 PLS-00417: unable to resolve "ROW" as a column

As of 9.2 it is possible... here a working example:

DECLARE
  vRecord emp%ROWTYPE;
BEGIN
  SELECT * INTO vRecord FROM emp WHERE empno=4711;

  vRecord.ename:='MUELLER';
  vRecord.job  :='SALESMAN';
  vRecord.sal  :=1000;

  UPDATE emp SET ROW=vRecord WHERE empno=4711; END; Chris

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

• Usenet.com - The #1 Usenet Newsgroup Service on The Planet! *** http://www.usenet.com Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=