Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: [PL/SQL] INSERT WITH ROWTYPE

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

  1. generating excessive undo and redo
  2. 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

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US