We have just upgraded an Oracle Database 22.214.171.124 from PSU6 to PSU24.
We have set a
AFTER SERVER ERROR trigger to track application errors a while ago and checked its history table.
We see the following error:
ORA-04043: object PACKAGENAME.PROCEDURENAME does not exist
Some other informations:
- The application for which this error is raised uses a different schema than the one in which the package is.
- The application is proprietary and we don't have access to the code.
- The application uses a synonym to get to that package.
- The application connects/disconnects every time, so it's difficult to start a trace...
- The package is valid
- Permissions are granted directly, not via roles
- The packages has only two dependencies on views and user have granted privileges on these views.
We tried different things :
- Grant permissions once more -> No changes
- DROP/CREATE the synonym -> No changes
- RECOMPILE the package-> No changes
- GRANT DBA to the user-> No changes
- Connect to Oracle instance with this user using SQL*Plus and try to run the procedure. -> It worked.
As tracing is difficult, we tried data mining on
v$sqltextand found one entry.
Edit 4/12/2015 2:31PM
We finally traced a session and isolated the executable which experience the error. Unfortunately, we have something we don't understand: the
AFTER SERVER ERROR trigger seems to be run before the actual execution of the originating package..
We found that the call to the procedure is done like this:
BEGIN PACKAGENAME.PROCEDURENAME(Param1,Param2) END;
We begin to wonder if the error caught by the server trigger is the right one. We get it back via
ora_server_error_msg function (see code below).
We also get the statement using
ora_sql_txt but it returns null.
Edit 2015-12-08 The server error trigger is the following:
create or replace TRIGGER "SYS"."VBO_DB_ERRORS_TRGG" AFTER SERVERERROR ON DATABASE begin sys.vbo_db_errors_proc(); end;
sys.vbo_db_errors_proc is the following one:
create or replace PROCEDURE "VBO_DB_ERRORS_PROC" IS PRAGMA AUTONOMOUS_TRANSACTION; muser varchar2(100); mosuser varchar2(100); mmachine varchar2(100); mprocess varchar2(24); mprogram varchar2(100); sql_text ora_name_list_t; mmsg varchar2(2000) := null; mstmt varchar2(2000) := null; chainetemp varchar2(4000) := 'inconnu'; BEGIN -- recuperation des infos de session select username, osuser, replace(machine,chr(0),''), process, program into muser, mosuser, mmachine, mprocess, mprogram from sys.v_$session where audsid = userenv('sessionid') and sid = (select sid from v$mystat where rownum <=1); -- recuperation du message d'erreur BEGIN for i in 1 .. ora_server_error_depth loop mmsg := mmsg || rtrim(ora_server_error_msg(i),chr(10)); end loop; EXCEPTION WHEN OTHERS THEN NULL; END; -- recuperation du texte BEGIN for j in 1 .. ora_sql_txt(sql_text) loop mstmt := mstmt || rtrim( sql_text(j), chr(10)); end loop; EXCEPTION WHEN OTHERS THEN NULL; END; insert into vbo_db_errors_log (error,stmt,timestamp,username,osuser,machine,process,program,quictx) values(mmsg,mstmt,sysdate,muser,mosuser,mmachine,mprocess,mprogram,chainetemp); commit; END;
- Could you confirm that a query that is in
v$sqltextis actually a query already executed successfully on the Oracle instance?
- Do you have any suggestion ?