Cascade copy in oracle

Question

I would like to make a cascade copy. I have a MODEL and the specification of this MODEL is stored in 4 tables and one MODEL is identificated by MODEL_ID in the tables. The MODEL_ID of the MODEL is primary key and the MODEL_ID is increased one by adding new MODEL.

How can I make the MODEL's copy?

Thanks.

I have no clue what you are asking. You want to copy a set of rows from some tables with a shared PK, and replace with a new PK value? Please read this on how to ask a question - http://stackoverflow.com/questions/how-to-ask . — OldProgrammer, Sep 19 '13 at 15:34
Yes, I want to copy a set of rows from some tables with a shared PK, and replace with a new PK value. — user2342549, Sep 19 '13 at 15:41
Then you need to create a script to generate a new PK, read the existing rows from the 4 tables, and do an insert with the new PK. — OldProgrammer, Sep 19 '13 at 15:42

score 0 · Accepted Answer · answered Sep 25 '13 at 13:06

0

Assuming you have to copy a model identified by MODEL_ID=old_model_id first of all you have to create a new MODEL_ID (for example stored in a variable called new_model_id), for example using a sequence (called SEQ_ID_MODEL in below code, but you could use another technique to create a new ID).

Then you have to write an insert command for each of the 4 tables retrieving the model to copy with the old id (old_model_id) and inserting using the new one(new_model_id):

PROCEDURE P_COPY_MODEL
(
  old_model_id  IN NUMBER
)IS

new_model_id number;

begin
    select SEQ_ID_MODEL.nextval into new_model_id from dual;

    insert into table1(MODEL_ID, COLUMN_1, COLUMN_2, ..., COLUMN_N) 
    select (new_model_id, COLUMN_1, COLUMN_2, ..., COLUMN_N)
    from table1 where MODEL_ID = old_model_id;

    insert into table2(MODEL_ID, COLUMN_1, COLUMN_2, ..., COLUMN_M) 
    select (new_model_id, COLUMN_1, COLUMN_2, ..., COLUMN_M)
    from table2 where MODEL_ID = old_model_id;

    --and so on for table3 and table4

end;

answered Sep 25 '13 at 13:06

Andrea

11,801
17
65
72

if I have another variable, what I use to select correctly the model, what I want to copy, how can I give string value to this variable, I tried this: Modulation VARCHAR2(8 BYTE); Modulation := '32QAM'; but this didnot work...can you check that? – user2342549 Oct 04 '13 at 10:18
@user2342549 Do you mean for declaring and setting a new variable? Correct syntax should be: `Modulation VARCHAR2(8); Modulation := '32QAM';` Remember to put variable declaration (instruction `Modulation VARCHAR2(8);`) between `IS` and `BEGIN` keywords at the beginning of the stored procedure – Andrea Oct 04 '13 at 14:02
yes, but I got error and the new model isn't created, the warning message: [Warning] ORA-24344: success with compilation error 4/26 PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character (1: 0): Warning: compiled but with compilation errors – user2342549 Oct 04 '13 at 14:22
and how can i give value for old_model_id? – user2342549 Oct 07 '13 at 10:05

Cascade copy in oracle

1 Answers1