How to execute multiple oracle queries using single connection?2019 Community Moderator ElectionGet the new record primary key ID from mysql insert query?Inserting into Oracle and retrieving the generated sequence IDAuto Increment for OracleHow to concatenate text from multiple rows into a single text string in SQL server?Inserting multiple rows in a single SQL query?Reset identity seed after deleting records in SQL ServerSQL Create dynamic columnDoes parent table get locked when DDL operation happens on a child table?Oracle SQL : Updating a column with SUM query of another tableSQL inserting data from tabl1 to tabl2Merging two tables which have same and new columnsjoining based on columns priorityHow to display ALL the Non-Null and ALL the Non-Empty records without mentioning ALL the column-name in the where clause using a MySql query?
Can "few" be used as a subject? If so, what is the rule?
Is VPN a layer 3 concept?
label a part of commutative diagram
Someone scrambled my calling sign- who am I?
What are the rules for concealing thieves' tools (or items in general)?
Unfrosted light bulb
How can an organ that provides biological immortality be unable to regenerate?
How are passwords stolen from companies if they only store hashes?
Pre-Employment Background Check With Consent For Future Checks
How to test the sharpness of a knife?
Do native speakers use "ultima" and "proxima" frequently in spoken English?
If I cast the Enlarge/Reduce spell on an arrow, what weapon could it count as?
What is the difference between something being completely legal and being completely decriminalized?
Did Nintendo change its mind about 68000 SNES?
PTIJ: Which Dr. Seuss books should one obtain?
Would this string work as string?
GPL v3 - Does freely distributed software that includes GPL licensed code also require sharing source?
How to remove space in section title at KOMA-Script
TDE Master Key Rotation
Determine voltage drop over 10G resistors with cheap multimeter
How can a new country break out from a developed country without war?
Can other pieces capture a threatening piece and prevent a checkmate?
Why are there no stars visible in cislunar space?
Writing in a Christian voice
How to execute multiple oracle queries using single connection?
2019 Community Moderator ElectionGet the new record primary key ID from mysql insert query?Inserting into Oracle and retrieving the generated sequence IDAuto Increment for OracleHow to concatenate text from multiple rows into a single text string in SQL server?Inserting multiple rows in a single SQL query?Reset identity seed after deleting records in SQL ServerSQL Create dynamic columnDoes parent table get locked when DDL operation happens on a child table?Oracle SQL : Updating a column with SUM query of another tableSQL inserting data from tabl1 to tabl2Merging two tables which have same and new columnsjoining based on columns priorityHow to display ALL the Non-Null and ALL the Non-Empty records without mentioning ALL the column-name in the where clause using a MySql query?
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.
sql database oracle
asked Mar 6 at 23:16
Vrushank DoshiVrushank Doshi
64741025
add a comment |
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.
sql database oracle
asked Mar 6 at 23:16
Vrushank DoshiVrushank Doshi
64741025
Possible duplicate: stackoverflow.com/questions/5558979/…
– alans
Mar 6 at 23:36
add a comment |
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.
sql database oracle
asked Mar 6 at 23:16
Vrushank DoshiVrushank Doshi
64741025
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.
sql database oracle
sql database oracle
asked Mar 6 at 23:16
Vrushank DoshiVrushank Doshi
64741025
asked Mar 6 at 23:16
Vrushank DoshiVrushank Doshi
64741025
asked Mar 6 at 23:16
Vrushank DoshiVrushank Doshi
64741025
asked Mar 6 at 23:16
Vrushank DoshiVrushank Doshi
64741025
asked Mar 6 at 23:16
Vrushank DoshiVrushank Doshi
64741025
64741025
Possible duplicate: stackoverflow.com/questions/5558979/…
– alans
Mar 6 at 23:36
add a comment |
Possible duplicate: stackoverflow.com/questions/5558979/…
– alans
Mar 6 at 23:36
Possible duplicate: stackoverflow.com/questions/5558979/…
– alans
Mar 6 at 23:36
Possible duplicate: stackoverflow.com/questions/5558979/…
– alans
Mar 6 at 23:36
add a comment |
1 Answer
1
active
oldest
votes
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 at 23:54
T.S.T.S.
10.2k103554
Don't do this. "RETURNING" is a non-standard SQL.
– battlmonstr
Mar 7 at 18:26
@battlmonstr Do you have better way to do the same thing ?
– Vrushank Doshi
Mar 7 at 19:10
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.
– battlmonstr
Mar 7 at 19:27
@VrushankDoshiReturningis 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 usingOUTPUT INSERTED. So what we do in code, we just sayobject.executeInsertand these non-standard SQL bits are abstracted to the caller.
– T.S.
Mar 7 at 19:31
@battlmonstr See comment ^^ above ^^. And then, what if this is Oracle12 and you just useidentity? 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.
– T.S.
Mar 7 at 20:00
|
show 4 more comments
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55033703%2fhow-to-execute-multiple-oracle-queries-using-single-connection%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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 at 23:54
T.S.T.S.
10.2k103554
Don't do this. "RETURNING" is a non-standard SQL.
– battlmonstr
Mar 7 at 18:26
@battlmonstr Do you have better way to do the same thing ?
– Vrushank Doshi
Mar 7 at 19:10
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.
– battlmonstr
Mar 7 at 19:27
@VrushankDoshiReturningis 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 usingOUTPUT INSERTED. So what we do in code, we just sayobject.executeInsertand these non-standard SQL bits are abstracted to the caller.
– T.S.
Mar 7 at 19:31
@battlmonstr See comment ^^ above ^^. And then, what if this is Oracle12 and you just useidentity? 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.
– T.S.
Mar 7 at 20:00
|
show 4 more comments
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 at 23:54
T.S.T.S.
10.2k103554
Don't do this. "RETURNING" is a non-standard SQL.
– battlmonstr
Mar 7 at 18:26
@battlmonstr Do you have better way to do the same thing ?
– Vrushank Doshi
Mar 7 at 19:10
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.
– battlmonstr
Mar 7 at 19:27
@VrushankDoshiReturningis 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 usingOUTPUT INSERTED. So what we do in code, we just sayobject.executeInsertand these non-standard SQL bits are abstracted to the caller.
– T.S.
Mar 7 at 19:31
@battlmonstr See comment ^^ above ^^. And then, what if this is Oracle12 and you just useidentity? 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.
– T.S.
Mar 7 at 20:00
|
show 4 more comments
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 at 23:54
T.S.T.S.
10.2k103554
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 at 23:54
T.S.T.S.
10.2k103554
answered Mar 6 at 23:54
T.S.T.S.
10.2k103554
answered Mar 6 at 23:54
T.S.T.S.
10.2k103554
answered Mar 6 at 23:54
T.S.T.S.
10.2k103554
10.2k103554
Don't do this. "RETURNING" is a non-standard SQL.
– battlmonstr
Mar 7 at 18:26
@battlmonstr Do you have better way to do the same thing ?
– Vrushank Doshi
Mar 7 at 19:10
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.
– battlmonstr
Mar 7 at 19:27
@VrushankDoshiReturningis 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 usingOUTPUT INSERTED. So what we do in code, we just sayobject.executeInsertand these non-standard SQL bits are abstracted to the caller.
– T.S.
Mar 7 at 19:31
@battlmonstr See comment ^^ above ^^. And then, what if this is Oracle12 and you just useidentity? 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.
– T.S.
Mar 7 at 20:00
|
show 4 more comments
Don't do this. "RETURNING" is a non-standard SQL.
– battlmonstr
Mar 7 at 18:26
@battlmonstr Do you have better way to do the same thing ?
– Vrushank Doshi
Mar 7 at 19:10
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.
– battlmonstr
Mar 7 at 19:27
@VrushankDoshiReturningis 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 usingOUTPUT INSERTED. So what we do in code, we just sayobject.executeInsertand these non-standard SQL bits are abstracted to the caller.
– T.S.
Mar 7 at 19:31
@battlmonstr See comment ^^ above ^^. And then, what if this is Oracle12 and you just useidentity? 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.
– T.S.
Mar 7 at 20:00
Don't do this. "RETURNING" is a non-standard SQL.
– battlmonstr
Mar 7 at 18:26
Don't do this. "RETURNING" is a non-standard SQL.
– battlmonstr
Mar 7 at 18:26
@battlmonstr Do you have better way to do the same thing ?
– Vrushank Doshi
Mar 7 at 19:10
@battlmonstr Do you have better way to do the same thing ?
– Vrushank Doshi
Mar 7 at 19:10
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.
– battlmonstr
Mar 7 at 19:27
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.
– battlmonstr
Mar 7 at 19:27
@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.
Mar 7 at 19:31
@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.
Mar 7 at 19:31
@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.– T.S.
Mar 7 at 20:00
@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.– T.S.
Mar 7 at 20:00
|
show 4 more comments
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55033703%2fhow-to-execute-multiple-oracle-queries-using-single-connection%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Possible duplicate: stackoverflow.com/questions/5558979/…
– alans
Mar 6 at 23:36