Re: PL/SQL - is inserting of %ROWTYPE variable possible?

From: Martin Burbridge <pobox002_at_bebub.com>
Date: Fri, 10 Jan 2003 01:08:49 GMT
Message-ID: <Xns92FECC912A912pobox002bebubcom@204.127.199.17>

DA Morgan <damorgan_at_exesolutions.com> wrote in news:3E1C4FE3.73E34A28_at_exesolutions.com:

> Rauf Sarwar wrote:
> 

>> Bogien <bogien_at_xcom.pl> wrote in message
>> news:<3E1B57D3.DDBC8532_at_xcom.pl>...
>> > Hello,
>> >
>> > Suppose I have a table named "tab" with columns "a", "b", "c", "d",
>> > "e". Now, I declare a variable named "rec" of type "tab%ROWTYPE",
>> > which reflects single record of the table.
>> >
>> > Question:
>> > Can I insert such variable into the table in PL/SQL at once?
>> >
>> > So fat, I've been performing this task this way (doesn't look
>> > nice...): INSERT INTO tab (a, b, c, d, e) VALUES (rec.a, rec.b,
>> > rec.c, rec.d, rec.e);
>> >
>> > Is something like "INSERT INTO tab VALUES rec" possible?
>> >
>> > Thanks!
>> >
>> > PS. I use Oracle 9i, if it can be of any help.
>>
>> No.
>>
>> Regards
>> /Rauf Sarwar

> 
> And not only NO ... but change the table name. TAB is the name of a
> view owned by SYS.
> 
> Daniel Morgan

This is why version number and full version number is important though, because it is a new feature in 9iR2. So you can do this, whether its advisable or not is another matter, particularly if there is a SQL alternative.

SQL> select * from v$version;

BANNER

---

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production NLSRTL Version 9.2.0.1.0 - Production

SQL> declare
  2 emp_rec emp%rowtype;
  3 begin

  4     select * into emp_rec from emp where rownum = 1;
  5     emp_rec.empno := 999;
  6     emp_rec.ename := 'ROONEY';
  7     insert into emp values emp_rec;

  8 end;
SQL> / PL/SQL procedure successfully completed.

SQL> select * from emp where empno = 999;

 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ -------- --------- ------ --------- ------ ------ -------

   999 ROONEY CLERK 7902 17-DEC-80 800 20 SQL> Martin Received on Thu Jan 09 2003 - 19:08:49 CST