i am getting ora-03113:end of file on communication channel , after running a pl/sql procedure which creates number of tables/indexes all through dynamic queries(execute immediate).
I am running this pl/sql procedure from my own user and all the new tables are getting created properly but at some point ora-03113 is getting generated and the connection is terminated by oracle after this error. If i dont run this procedure then everything is working fine and my other procedures are running fine too. The only difference between this failing procedure and others is that i am using lots of "execute immediate" statements and DBMS_SQL.EXECUTE(v_cursorid) command to dynamically create tables.
The oracle server has recently been shifted from 8i to 9i and i am getting this error only after running this procedures on the new 9i server(standard edition). The same procedure used to run fine in 8i.
Pls suggest what's going wrong and how to solve this problem.
I've gotten dropped connections for annoying Oracle so many times now it no longer bothers me. I open another connection, try the same act again, and if it blows up again, I know I've hit a roadblock. I generally put it in reverse, turn the wheel a little and try to hit it from a different direction.
I would simply suggest breaking up your script to try to find exactly which command is annoying Oracle. Then see if you can do what you are attempting in a different way. Not very scientific, but probably the quickest way around the problem.
I could debug the entire pl/sql procedure and found that the
following delete statement is causing the oracle error: ora-03113.
delete FROM PROPERTIES_TEXT_LARGE a WHERE exists
(SELECT b.valueobjecttypeid, B.valueobjectid FROM PROPERTIES_OBJECTS b WHERE b.objecttypeid = 10217 AND b.valueobjecttypeid > 40000
and a.objecttypeid = b.valueobjecttypeid and a.objectid = b.valueobjectid);
This delete statement is a part of bulk deletes and almost 25-30 similar delete statements form the entire transaction. So if any of the delete stmts fails then the entire transaction rolls back. I was able to run the bulk deletes after commenting out this delete stmt. The unique property of this statement is that it has a field of type varchar2(4000) and at a given time we are deleting say only 20 records from this table. It seems there is some problem with the joining of the two tables in the delete stmt. If i delete from the same table without the join then it works fine but i cant do that all the time. I was using an "in" stmt inplace of the "where exists" but even after changing the "in" with "exists" the error still occurs.