SQL & PL/SQL

Hi,

I came across an interesting problem last week. Fortunately, I was able to solve it or find an acceptable workaround for myself. But wanted to get some clarification from the experts. So posting it here.

Also, I am new to Orcle, so please excuse any shortcomings in the post.

My data model has following tables-

TABLE_PARENT (ID, other columns)
TABLE_CHILD (ID, other columns, PARENT_ID_FK)

Here, ID is the primary key column for the respective tables; PARENT_ID_FK is the foreign key referencing the ID column from the TABLE_PARENT.

I created a stored procedure programmatically (using MS Excel) to insert records in the two tables. The stored procedure has insert statements for an indefinite number of records in the parent table and for every such record, there's a corresponding record inserted in the child table. Here's the sample code.

BEGIN
/*first record*/
parent_id := MY_SEQUENCE_GENERATOR.NEXTVAL;
INSERT INTO TABLE_PARENT(ID, other columns) VALUES (parent_id, other values);
INSERT INTO TABLE_CHILD(ID, other values,parent_id );

/*second record*/
parent_id := MY_SEQUENCE_GENERATOR.NEXTVAL;
INSERT INTO TABLE_PARENT(ID, other columns) VALUES (parent_id, other values);
INSERT INTO TABLE_CHILD(ID, other values,parent_id );

/*third record*/
parent_id := MY_SEQUENCE_GENERATOR.NEXTVAL;
INSERT INTO TABLE_PARENT(ID, other columns) VALUES (parent_id, other values);
INSERT INTO TABLE_CHILD(ID, other values,parent_id );

/*and so on*/
END

When I run this stored procedure, I keep getting following exception intermittently-

ORA-02291: integrity constraint violated-parent key not found tips.

My thinking is that it comes because the insert statements are executing ahead of turn of the parent_id assignment statement. And this is happening possibly because of some parallelism that is taking place during the execution of the stored procedure, or, some sort of optmization that the DB server does (though erroneously) when it compiles the stored procedure.

I tried out everything that I could think of but it didn't go away. Finally, when I executed the same set of statements as a PL-SQL block, it worked fine.

To understand it better, I am looking for clarification on the following questions.

1) Why does the exception come with stored procedure but not with PL-SQL block? Is my reasoning given above correct (parallelism or some sort of optimization coming into play)?
2) If it is due to parallelism, how to run a Oracle stored procedure with defree of prallelism set to 1?
3) If it is due to optimization done by the compiler, how to instruct the compiler to not do any such optimization. Also, in any case, isn't it an error to optimize but lose program semantics?
4) Another question related to the same piece of work I have is to use transactions in the PL-SQL block, I had to explicitly COMMIT/ROLLBACK it in the code. In whatever references I had read, it was said that by default the transaction begins with BEGIN statement and commits with END. Also, it seems to work with a Stored Proedure though. So is it that a PL_SQL block needs explicity call to COMMIT/ROLLBACK to achive transactions but stored procedures do not?


Any inputs/clarifications will be much appreciated.

Thank you
Neelesh