Solved

Use of inserted data that has not been committed

Posted on 2013-05-22
3
286 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 500 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

685 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