Re: match columns on insert sub select

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/