Re: Multi-table inserts and Oracle records

On Apr 16, 3:22 am, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> While reading "Oracle PL/SQL Programming" I came across the section on
> RECORDs being able to be
> used for DML operations such as INSERT and UPDATE.
>
> I easily understand the example(s) given for the single table INSERT and
> UPDATE but could not find
> an example for a multi-table insert and was wondering if a multi-table
> insert such as:
>
> INSERT FIRST
>
> WHEN ( table_code = '1' ) THEN
>
> INTO table_one
> ( field_one,
> field_two,
> field_three )
> VALUES
> ( field_one,
> field_two,
> field_three )
>
> WHEN ( table_code = '2' ) THEN
>
> INTO table_two
> ( field_one,
> field_two,
> field_three )
> VALUES
> ( field_one,
> field_two,
> field_three )
>
> SELECT field_one AS field_one,
> field_two AS field_two,
> field_three AS field_three
> FROM source_table;
>
> Could possibly be re-written as something like:
>
> INSERT FIRST
>
> WHEN ( table_code = '1' ) THEN
>
> INTO table_one VALUES field_rec
>
> WHEN ( table_code = '2' ) THEN
>
> INTO table_two VALUES field_rec
>
> SELECT field_one AS field_one,
> field_two AS field_two,
> field_three AS field_three
> FROM source_table;

Multi-table insert is SQL-only feature, you can't use PL/SQL records with it - data source for a multi-table insert can only be a subquery. Inserts using PL/SQL records implies that you first populate records somehow, which is obviously not possible within a multi-table insert.

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Mon Apr 16 2007 - 01:51:19 CDT