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_at_204.127.199.17>
>> 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
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
8 end;
SQL> / PL/SQL procedure successfully completed.
Date: Fri, 10 Jan 2003 01:08:49 GMT
Message-ID: <Xns92FECC912A912pobox002bebubcom_at_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 Fri Jan 10 2003 - 02:08:49 CET