Inserting data in multiple tables using Oracle stored procedure

Question

I have 4 tables say, table1, table2, table3 and table4, which are interrelated. Table1 will generate a primary key, that will be used in rest of the tables as reference key.

I have to insert multiple records in table 4 using this primary key. Since the requirement is the transaction should either commit successfully or it should rollback all the changes. That is the reason I thought of writing this in stored procedure. But got stuck, when I had to pass multiple rows data for table4.

Can anyone please suggest, how can I achieve this?

Thanks, in advance.

Yes, for all the tables, primary key will be auto incremented. — ABC
– ABC, Commented Oct 3, 2013 at 11:28

Saghir A. Khatri · Accepted Answer · 2013-10-03 11:25:58Z

2

i guess you want to do something like this

CREATE OR REPLACE PROCEDURE myproc
(
 invId IN NUMBER,
 cusId IN NUMBER
)
IS
    temp_id  NUMBER; 
BEGIN 
    INSERT INTO myTable (INV_ID) 
    VALUES (invId)
    returning id into temp_id;

    INSERT INTO anotherTable (ID, custID) 
    VALUES (temp_id, custId);  
END myproc;

answered Oct 3, 2013 at 11:25

Saghir A. Khatri

3,1346 gold badges48 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

ABC Over a year ago

Yes, right. This will work for first 3 tables, where I have to insert single records. But for 4th table I have to insert multiple records. I don't know how can I achieve that.

Saghir A. Khatri Over a year ago

i guess you already have the multiple values in parameters of stored procedure. you just have to use temp_id valriable. i will not lose values till stored procedure ends.

ABC Over a year ago

No. of parameters of stored procedure will be equal to the no. of columns of all the tables. But in case of 4th table where I have to insert multiple rows, the no. of values can not be fixed.

Saghir A. Khatri Over a year ago

if you please post the current stored procudure you are using for inserting in 4th table

ABC Over a year ago

I have not written any procedure to insert data in 4th table yet.

|

Collectives™ on Stack Overflow

Inserting data in multiple tables using Oracle stored procedure

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related