I am encountering a strange problem with my Oracle 9i DB whereby I get the ORA-06508 program unit being called cannot be found.
I have narrowed this down to a procedure in a package. Whilst I cannot call this procedure succesfully from another procedure getting this error I CAN call it from sql plus directly!!?
Furthermore, restarting the db instance fixes the problem for a little while before it returns again.
All packages are compiled perfectly.
Help appreciated.
Ali
Is your procedure and the calling procedure are created under the same schema? Can you also paste the information how you are calling the procedure(with the details of the package name, procedure name and the calling procedure name)?
yes they are in the same schema...like I say, there is no problem when the database is started, but after using the database for a while, these errors start.
I have tested this cycle a number of times and it is repeatable. It is almost like a memory problem??
This is strange. If you are sure that your code is perfect in calling the procedure then can you increase the SHARED_POOL_SIZE of your database and try again.
How big is your SGA? It seems that you are suggesting that the SGA is filling up and that this procedure gets aged out, and eventually I can't get put back into the SGA. Do you pin alot of packages in the SGA? Without knowing the details I would think that if your SGA were bigger this might not happen. Otherwise more information is needed.
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 235999352
also, i noticed that PLSQL_V2_COMPATIBILITY is FALSE at the moment...the Oracle 9i action for the error I get suggests either the code is not compiling (no) or there is a compatibility problem (maybe).
Afterall, I have upgraded from 8i to 9i in the past few days. I am still puzzled by the way it works for a while though!?
Hi...
Try to KEEP the procedure in your shared pool using dbms_shared_pool package. It may work.
Joe
When you get the error do you get any dump files from the database? Can you put the database in trace mode and run for a while, maybe you will get more information on what's happening. You can also create a logging procedure to insert records into a table, using autonomous transaction to see if the procedure is failing after it get instantiated.
If nothing else works you may want to rerun the catalog scripts.
i've looked in the alert log and nothing bad is in there. I am not familiar with putting the db into trace mode and where those logs would appear...is this an easy thing to do?
Did the size of my SGA look too small for large packages?
Thanks all.
This could be a problem with the DBMS_SQL package. Try to run the catproc.sql as sys and see if you are still having a problem.
To find the value of your sga log into sqlplus and type show sga.