Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Use of inserted data that has not been committed

Posted on 2013-05-22
3
Medium Priority
?
298 Views
Last Modified: 2013-06-07
I have multiple inserts and will not commit until all inserts are successful.

One of the table's values depends upon data in one of the table' s insert that has not been committed..

Is that data available in the table to use?  
For example I need to use one of the table's primary key as a foreign key  in another table whose data is being inserted.

Insert into table a value(primary key, name

I need to insert tablea primary key into tableb as a foreign key before table a insert has been committed.
Insert into table  b (tablea.primary key, nameb

commit
0
Comment
Question by:cookiejar
  • 2
3 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 39187994
as long as you've done the insert to A inside the same session as the insert to B  the the A/B relation can be evaluated correctly.

easiest method of evaluation here is to simply try it
0
 

Author Comment

by:cookiejar
ID: 39188196
Even though, I have not done a commit for Table A?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39188318
yes - but you don't have to trust me.

Just test it, it's easy to see for yourself

SQL> CREATE TABLE tablea
  2  (
  3      primary_key   NUMBER PRIMARY KEY,
  4      name          VARCHAR2(50)
  5  );

Table created.

SQL> CREATE TABLE tableb
  2  (
  3      child_key    NUMBER PRIMARY KEY,
  4      parent_key   NUMBER     REFERENCES tablea(primary_key),
  5      name         VARCHAR2(50)
  6  );

Table created.

SQL> INSERT INTO tablea
  2       VALUES (1, 'test parent');

1 row created.

SQL> REM Note I have not committed anything yet
SQL> INSERT INTO tableb
  2       VALUES (1, 1, 'test child - succeeds');

1 row created.

SQL> REM The child of uncommitted parent-1 succeeded
SQL> REM
SQL>
SQL> REM But the next one will fail because it has no parent-2
SQL> INSERT INTO tableb
  2       VALUES (2, 2, 'test child - fails');
INSERT INTO tableb
*
ERROR at line 1:
ORA-02291: integrity constraint (SDS.SYS_C0070303) violated - parent key not
found

Open in new window

0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question