Hi,
Thanks for the feedback. I am told that by using a function to return the sequence instead of directly accessing the sequence exploits PL/SQL's optimization process. However would you consider that "unsafe"?

The reason I am asking is that I was trying to resolve an issue with efficiently creating a copy of master/detail record.


So setting up just some basic data:

Create Sequence xxmaster_s;  
Create Table xxmaster  
As  
      Select xxmaster_s.nextval id, 'Dummy' || To_char (Level, 'fm00') val  
        From Dual  
  Connect By Level <= 10;  
  
Create Table xxdetail  
As  
 Select Mod(Level,10) + 1 master_id, 'Dummy Detail' || To_char (Round(Dbms_Random.Value(1,Level)), 'fm00') val_dtl  
 From Dual Connect By Level <= 100;  

I now want to copy those master records 1-10 and their corresponding detail records, but assign them new sequences. So a "loops" way of doing this would be:

 
Declare  
  l_new_id xxmaster.id%Type;  
Begin  
  For i In (Select id, val From xxmaster) Loop  
    Insert into xxmaster(id, val) Values (xxmaster_s.nextval,i.val)  
    Returning id Into l_new_id;  
      
    Insert Into xxdetail(master_id, val_dtl)  
    Select l_new_id, d.val_dtl  
      From xxdetail d  
     Where d.master_id = i.id;  
  End Loop;  
End;  

But I find it somewhat inefficient - especially if we are considering large volumes of data in xxmaster because we're looping through and inserting one record at a time, then executing the insert of detail records for each.

I am not criticising at all, but I find it interesting that the OP asked this question in the OTN forum, then didn't fully trust the answer and came here for a second opinion.

The OTN thread is here: https://community.oracle.com/thread/3994872

I would not trust the OTN solution for two reasons:
- first, SQL not being a procedural language Oracle is free to call functions if and when it chooses, so there is no clear guarantee the behavior will not change.
- second, if there is a foreign key constraint from the detail rows to the master rows then I have seen the constraint fail from time to time when doing INSERT ALL.

Chris' solution avoids both potential obstacles.

Another solution would be a pipelined table function that read the master and detail rows but used the sequence in a procedural manner to get new master ids. This would work if the data were being copied to another table, but fails with a "mutating table" error when adding new rows to the original table.

In most cases, where the source data is not in the target table, I would be tempted by the pipelined table function since it does everything in one statement without requiring any additional columns. To avoid the error on the foreign key constraint, I would sort the output of the pipelined table function to insert all the master rows first.

Best regards, Stew

Hi,
The reason for coming here was more to get clarification on the function optimization and whether that could be relied upon. As stated though, it could (in theory) change at any time.

The solution Chris proposed does seem to be the simplest, and if anything provides a useful audit trail for where the records were copied from.

Thanks

Step 1.
Insert into the master select new_SEQ, OTHER_cols from master table

Step 2.
Insert into detail
select a.new_id,b.val_dtl
from(select max(id) as new_id,
,min(id) as old_id
,val
from master
group by val
)a
join detail b
on a.old_id=b.master_id

If its a master table i expect there would be some columns which form a natural key.

If val is not unique, there could be a set of columns which are unique in the master table that would form the natural key of the master table.

The OP can confirm if this is the case.

Hi,
I was trying to achieve something else today which is similar to this but slightly different - inserting a number of very similar rows into a single table with an identity column defined. Obviously I could have done this with multiple insert statements, however my first attempt was with INSERT ALL - which failed with a primary key error.

I set up a very simple example here: https://livesql.oracle.com/apex/livesql/s/gfq045vbmgdnsapiwzns0guoz

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> set echo on
SQL> @a
SQL> create table a(b number generated always as identity, c number, constraint a_pk primary key (b));

Table created.

SQL>
SQL>
SQL> insert all into a(c) values (lvl)
  2             into a(c) values (lvl*10)
  3  select level lvl from dual connect by level <= 2;
insert all into a(c) values (lvl)
*
ERROR at line 1:
ORA-00001: unique constraint (KEYMEJ.A_PK) violated


SQL>
SQL> drop table a;

Table dropped.

SQL>
SQL> -- Do it the long way
SQL>
SQL> create table a(b number, c number, constraint a_pk primary key (b));

Table created.

SQL>
SQL> create sequence a_s;

Sequence created.

SQL>
SQL> create trigger a_pk_trg before insert on a for each row
  2  begin
  3    :new.b := a_s.nextval;
  4  end;
  5  /

Trigger created.

SQL>
SQL> insert all into a(c) values (lvl)
  2             into a(c) values (lvl*10)
  3  select level lvl from dual connect by level <= 2;

4 rows created.

SQL>
SQL> select * from a;

         B          C
---------- ----------
         1          1
         3          2
         2         10
         4         20

SQL>
SQL> drop table a;

Table dropped.

SQL> drop sequence a_s;

Sequence dropped.

SQL>

Now I know there are a number of workarounds that can be used, however I was surprised to find that this failed as it did - I was expecting the identity column to be populated in a similar fashion to how a trigger works (i.e. for each row inserted into the database). Clearly that isn't the case though.

Is this intended/expected functionality??

Thanks