This discussion is archived
6 Replies Latest reply: Apr 6, 2013 6:38 AM by Hoek RSS

ORA-04045: errors during recompilation/revalidation of <procedure>

bobonacus Newbie
Currently Being Moderated
Hi,

Oracle Standard Edition 11.2.0.1 running on 64bit windows 2008 R2

I am getting this in my aplication at the moment, if I drop and recreate the procedure it then moves to the next procedure called in my app .... ie. there is something fundamentally wrong with the database!!

ORA-04045: errors during recompilation/revalidation of <procedure>
ORA-01405: fetched column value is NULL

There are no other errors in my alert log

Could it be a user has been dropped when they shouldn't? SYS and SYSTEM are both still there .... is there a script I can run to check?

Any other ideas?

Thanks
Robert
  • 1. Re: ORA-04045: errors during recompilation/revalidation of <procedure>
    Hoek Guru
    Currently Being Moderated
    Pretty exotic and apparently yet undocumented error message:
    http://docs.oracle.com/cd/E11882_01/appdev.112/e10830/appa.htm#sthref1728

    Do you have access to Metalink/MyOracleSupport?
    What application are you using?
  • 2. Re: ORA-04045: errors during recompilation/revalidation of <procedure>
    bobonacus Newbie
    Currently Being Moderated
    Hi,

    I do have myoracle support access but currently no TAR creation, just patch downloads

    The entire app uses mod_plsql and makes calls to htp.p ..... even when I change the procedure to begin null; end; it still gives the same error when compiling .... drop and recreate it then works!!
  • 3. Re: ORA-04045: errors during recompilation/revalidation of <procedure>
    Hoek Guru
    Currently Being Moderated
    mod_plsql?
    So, you're using Application Server?
    Perhaps this is of use then:
    "These authorize functions work in OAS, but do not work in 9iAS 1.0.2. When
    such a function is used this error message will be printed to the HTML page:

    Custom Authentication Failure. [testauth.authorize] oerr = 1405
    ORA-01405: fetched column value is NULL


    To make the authorize function work again in mod_plsql change it to look like
    this:

    function authorize return boolean is
    begin
    owa_sec.set_protection_realm ('Dummy Realm');
    return TRUE;
    end authorize;

    This means that You must specify a realm name in Your authorize functions
    for mod_plsql while You didn't have to when using OAS."
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:596269493096

    I'd like to point you to a more recent article, but for some reason Metalink keeps on crashing my browser at the moment....
  • 4. Re: ORA-04045: errors during recompilation/revalidation of <procedure>
    bobonacus Newbie
    Currently Being Moderated
    Yes, using Oracle OHS .... though the same system has been working for years and we have dozens of databases, all running the same application on the same single OHS with no issue


    removing the OHS from the equation I can't compile the procedure from sqlplus so it can't be an issue with the OHS. The issue only started last week, someone must have changed something in the database but I can't find out what. Comparing users it doesn't look like a missing user
    SQL>
    SQL> alter procedure database_anal_alert compile;
    alter procedure database_anal_alert compile
    *
    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-01405: fetched column value is NULL
    
    
    SQL> create or replace procedure database_anal_alert
      2   as
      3  begin
      4  null;
      5  end;
      6  /
    create or replace procedure database_anal_alert
    *
    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-01405: fetched column value is NULL
    
    
    SQL> drop procedure 
    
    Procedure dropped.
    
    SQL> create or replace procedure database_anal_alert
      2   as
      3  begin
      4  null;
      5  end;
      6  /
    
    Procedure created.
  • 5. Re: ORA-04045: errors during recompilation/revalidation of <procedure>
    bobonacus Newbie
    Currently Being Moderated
    ... cont ....

    and I can then recompile the procedure with the exact original code with no problem
  • 6. Re: ORA-04045: errors during recompilation/revalidation of <procedure>
    Hoek Guru
    Currently Being Moderated
    Have you already read the following docs on Metalink:
    [Article ID 1293027.1]
    [Article ID 800347.1]
    ?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points