How to execute multiple oracle queries using single connection?

Question

I have 3 tables in my database and their schema looks something like,

Table1 (Col1 Pk, Col2), 
Table2 (Col3 Pk, Col1 FK, Col4)
Table3 (Col5 Pk, Col3 FK, Col6)

Here the values of primary key columns is auto incremented on every insert operation. I want to insert values in the above tables. With the above scenario i cannot insert into Table3 unless i have value of Col3 (foreign key to Table2), similarly insert into Table2 won't go through unless i have value for Col1 (foreign key to Table1)

Question: How can i insert into the above tables in a single database connection ?

I can always make 3 different calls and insert values, but how can i use the value from Table1 and use it to insert into Table2, likewise value for Table2 and use it to insert into Table3 in single connection ?

I thought of writing a stored procedure but not sure how can i fetch Col1 value and use it to insert into Table2.

Pls suggest some thoughts here. An example will also help.

Thank you.

Possible duplicate: stackoverflow.com/questions/5558979/…

alans
– alans

2019-03-06 23:36:52 +00:00
Commented Mar 6, 2019 at 23:36 — alans
– alans, Commented Mar 6, 2019 at 23:36

T.S. · Accepted Answer · 2019-03-06 23:54:40Z

2

I think, you're looking for this. Just use Returning to retrieve the PK value and use the value on next insert

declare
    v_col1_id number(10) := null;
    v_col3_id number(10) := null;
begin
    insert into T1 (col2) values ('xxx') RETURNING col1 INTO v_col1_id; -- col1 - PK
    insert into T2 (col1, col4) values (v_col1_id, 'yyy') RETURNING col3 INTO v_col3_id; -- col3 - PK
    insert into T3 (col3, col6) values (v_col3_id , 'zzz'); -- col5 - PK
end;
/

answered Mar 6, 2019 at 23:54

T.S.

19.6k11 gold badges70 silver badges96 bronze badges

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

9 Comments

battlmonstr Over a year ago

Don't do this. "RETURNING" is a non-standard SQL.

Vrushank Doshi Over a year ago

@battlmonstr Do you have better way to do the same thing ?

battlmonstr Over a year ago

I guess this is fine if you are ok to write a stored procedure anyway. Looking at solutions here they are all based on sequence / nextval anyway. Maybe auto-increment in general is not standard, and one have to use various proprietary extensions.

T.S. Over a year ago

@VrushankDoshi Returning is there for this. If you only used standard SQL, you would go nowhere. What you do, you write RDBMS-specific sql. You write standard when possible and when not possible, you separate. For example, in Sql Server same thing would be achieved by using OUTPUT INSERTED. So what we do in code, we just say object.executeInsert and these non-standard SQL bits are abstracted to the caller.

T.S. Over a year ago

@battlmonstr See comment ^^ above ^^. And then, what if this is Oracle12 and you just use identity? Trigger is more maintained. If this code needs to be executed via app, like .NET, you do as I said above. Abstract SQL calls, build sql generating objects or just use entity framework.

|

Collectives™ on Stack Overflow

How to execute multiple oracle queries using single connection?

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related