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

Home -> Community -> Usenet -> c.d.o.server -> Re: match columns on insert sub select

Re: match columns on insert sub select

From: John Russell <netnews6_at_johnrussell.mailshell.com>
Date: Thu, 07 Aug 2003 05:59:09 GMT
Message-ID: <3gq3jvoq8q6lcmios72fno2qo68nioh62t@4ax.com>


On Sun, 03 Aug 2003 17:07:52 +0200, Sybrand Bakker <gooiditweg_at_sybrandb.demon.nl> wrote:

>On Sun, 3 Aug 2003 13:30:54 +0200, "Umberto Quaia"
><umberto..quaia_at_tin.it> wrote:
>
>>I don't understand Erik's objection about adding a column in
>>source table.
>>If that column is not in dest table, it's simply not copied.
>
>
>In PL/SQL stored procedures the * is resolved at *compile time*.
>This means when you change the table the procedure won't work anymore.

The %ROWTYPE datatype in PL/SQL can shield you from changes in the meaning of "*" to some extent. For modest amounts of data, I'd look at a procedure like

Query source table (SELECT *).
Bulk collect into collection of <source table>%ROWTYPE. Bulk insert from collection into destination table.

(Requires 9.2 to be able to bulk insert from a table of records.)

John

--
Photo gallery: http://www.pbase.com/john_russell/
Received on Thu Aug 07 2003 - 00:59:09 CDT

Original text of this message

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