Re: [PL/SQL] INSERT WITH ROWTYPE

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 8 Oct 2004 20:34:37 +0100
Message-ID: <004101c4ad6d$dabf09d0$6702a8c0@Primary>

Note also that you should never tell your developers about this feature.

The 'retype variable has to match the full table row definition - so if they start to use the syntax for inserts, they will soon start to use it for updates, and that means they will be

generating excessive undo and redo
updating primary keys and requiring pointless FK indexes to exist to avoid child-lock problems.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated Sept 19th

Original Message ----- From: "Powell, Mark D" <mark.powell_at_eds.com> To: <oracle-l_at_freelists.org> Sent: Friday, October 08, 2004 5:42 PM Subject: RE: [PL/SQL] INSERT WITH ROWTYPE

With version 8.1 and lower you must reference every column by name in the insert statement value clause:
values (vrow.col1, vrow.col2, ....)

With version 9 you can insert a rowtype as a single statement as with "values rowtype". Notice no parenthesis around the rowtype variable.

HTH -- Mark D Powell --

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Jose Manuel Quesada Sent: Friday, October 08, 2004 7:58 AM
To: oracle-l_at_freelists.org
Subject: [PL/SQL] INSERT WITH ROWTYPE

Hi all.
Environment: Oracle 8.1.7.4

Suppose I have a table MYTABLE with 40 columns.

Is there a way to code in PL/SQL an INSERT statement using a ROWTYPE variable.

Something like this:

DECLARE
vRow MYTABLE%ROWTYPE;
BEGIN

    ...
    ...
    ...

INSERT INTO MYTABLE vRow;
END;

Thanks

--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri Oct 08 2004 - 14:32:11 CDT