1

We have just upgraded an Oracle Database 11.1.0.7 from PSU6 to PSU24. We have set a AFTER SERVER ERROR trigger to track application errors a while ago and checked its history table.

Summary

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;

The procedure 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;

Questions

  1. Could you confirm that a query that is in v$sqltext is actually a query already executed successfully on the Oracle instance?
  2. Do you have any suggestion ?

Thanks.

  • You can use a login trigger to enable session tracing for the specific application/user. Is the grant direct, or via a role? Remember that grants via roles don't behave as most would expect in the context of PL/SQL execution. Can you connect via SQL*Plus as the app user and execute the procedure in the context that the application is calling it in? Do you have the source code for the application? I'll hazard a guess that it's a role grant problem. – Philᵀᴹ Dec 4 '15 at 11:14
  • I updated with the answers to your questions. We will try the LOGON Trigger trick. Do you have a code example ? – Jefferson B. Elias Dec 4 '15 at 11:21
  • There's one here oracle-wiki.net/startscriptlogontrgtrace - just need to change the username – Philᵀᴹ Dec 4 '15 at 11:43
  • Does the package internally calls to different package ? Also you can audit the package to see from where and when the package was called before the application raises the error. – vegatripy Dec 4 '15 at 12:04
  • The packages has only two dependencies on views and user have granted privileges on these views. – Jefferson B. Elias Dec 4 '15 at 12:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.