This content has been marked as final. Show 4 replies
Yann39 wrote:I previously had a problem similar to this. (Un)fortunately, the issue appeared to solve itself...
Oracle 22.214.171.124.0, APEX 4.1.1.00.23.
I am trying to compile a procedure that access apex_application_page_regions to get the region source of a report :
I can't compile the procedure. After some time I get ORA-04021: timeout occured while waiting to lock object.
BEGIN -- get report region source SELECT region_source INTO l_source FROM apex_application_page_regions WHERE application_id = p_app_id AND page_id = p_app_page_id AND static_id = p_report; [...] END;
From v$session :
I am forced to ask developers to close all APEX instances to kill sessions, to be able to compile the procedure...
STATUS SERVER PROGRAM SQL_EXEC_START BLOCKING_SESSION_STATUS BLOCKING_INSTANCE BLOCKING_SESSION EVENT P1TEXT WAIT_CLASS STATE ACTIVE DEDICATED plsqldev.exe 07/02/2012 09:12:52 VALID 1 971 library cache pin handle address Concurrency WAITING
Is there any way to bypass this ?
Library Cache Pin Wait Event (within the context of APEX)
I saw your post, I think the issue does not really "solve itself", the problem disappear as soon as the blocking session ends or get killed.
I think we can passby it, for example by runing the blocking code inside an "execute immediate" statement, so it will not try any lock at compile time, but it's a bit a makeshift job...
I don't really understand why Oracle is trying to lock the object, as I only try to compile a procedure with a SELECT statement.
Have you tried dropping the object altogether?
Do other DB objects have a dependency on the object you are trying to compile?
What is the object incidentally - is it the body of a package, a standalone function/procedure or even a trigger?
edit: I see you've actually said its a procedure. Why not trying packaging it up to see if that makes a difference.
Edited by: Joel_C on 02-Jul-2012 11:58
It is just a simple procedure, out of a package because it is called from an APEX URL, and need to be public.
It gets the region source of a report, parse and execute it using DBMS_SQL, and write content to a CSV file.
However, I was wrong, the problem occured because I use some APEX_COLLECTIONS in the query, and it seems they are not considered when the procedure is called from the URL (or maybe when executed via DBMS_SQL). So the query returned all records (arround 1 million) instead of some undreds, it is why the first time I run it (this morning), it took more than 3 hours to complete ,and it was locked.
I don't saw it because I forgot to look at APEX_PUBLIC_USER sessions...
I can compile without any problem now, I simply have to be careful because I don't have privileges for killing sessions.
Thank you for trying to help Joel.