DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: PLS-00225 error and Packages

  1. #1
    Join Date
    Jan 2006
    Posts
    2

    Unhappy PLS-00225 error and Packages

    Hello,

    i have created two packages that i want to both use together.
    But if i test my stored proc i have and PLS-00225 error :

    "PLS-00225: subprogram or cursor 'LOG' reference is out of scope" on line 146/column 8

    here is the line 146 of my stored proc:
    EXCEPTION
    WHEN UTL_FILE.INVALID_PATH THEN
    LOG.errorLog(
    'Error Error',
    SQLCODE,
    'Check your code',
    '-20001',
    'Check your code');

    And this is the stored proc in the LOG package

    SPEC :
    PROCEDURE errorLog(
    title_output VARCHAR2,
    sSQLCODE NUMBER,
    descr_output VARCHAR2,
    raiseCode VARCHAR,
    raiseDESC VARCHAR2
    );


    BODY :
    PROCEDURE errorLog(
    title_output VARCHAR2,
    sSQLCODE NUMBER,
    descr_output VARCHAR2,
    raiseCode VARCHAR,
    raiseDESC VARCHAR2
    )
    IS
    BEGIN
    dbms_output.put_line( title_output );
    dbms_output.put_line( sSQLCODE || descr_output);
    RAISE_APPLICATION_ERROR( raiseCode , raiseDESC);
    EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
    END errorLog;


    But if i put the stored proc's ownership on the call it works !!!???

    example :
    EXCEPTION
    WHEN UTL_FILE.INVALID_PATH THEN
    CRMDVL.LOG.errorLog(
    'Error Error',
    SQLCODE,
    'Check your code',
    '-20001',
    'Check your code');

    In that case it's all good !

    Can someone help me for this problem (no owner to indicate on the stored procedure)?

    Thanks a lot.

  2. #2
    Join Date
    Nov 2003
    Location
    Ohio
    Posts
    51
    The reference material states:

    pls-00225, subprogram or cursor reference '%S' is out of scope.

    Means that there is a problem resolving a object reference or variable name in the plsql program.
    Check for duplicate variable declarations, or objects on the sys, system and the user schema, for any possible conflicts.
    Select object_name, object_type, owner from dba_objects where object_name like '%'; should
    help you identify the different objects existent in your system and the different schemas with the same name and that may be causing the reference conflict.

    I also saw this out there on the web.
    http://www.edhanced.com/ask-mred/?q=node/view/53. So it may be that Oracle thinks you want to use the built in LOG function. By putting the userid in front, Oracle resolves the name to a different object.

    HTH,
    Pete
    ____________________
    Pete

  3. #3
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    Pete is right. I reproduced your error and it went away when I changed the package name to ERROR_PKG.

    btw, possibly it's just a dummy example but the "errorlog" procedure doesn't seem to log anything. And it seems to throw away any error stack you had, for example:

    Code:
    SQL> BEGIN
      2      RAISE_APPLICATION_ERROR(-20000, 'We have no bananas');
      3  EXCEPTION
      4      WHEN OTHERS THEN
      5          error_pkg.errorLog(
      6          'Error Error',
      7          SQLCODE,
      8          'Check your code',
      9          '-20001',
     10          'Check your code');
     11  END;
     12  /
    Error Error
    -20000Check your code
    BEGIN
    *
    ERROR at line 1:
    ORA-20001: Check your code
    ORA-06512: at "WILLIAMR.ERROR_PKG", line 17
    ORA-06512: at line 5
    The error stack contained "ORA-20000: We have no bananas" until you called errorlog, when you lost the original stack (SQLERRM).

    Also, why is "raisecode" VARCHAR2 and not INTEGER or PLS_INTEGER?

    I would make "ssqlcode" an integer as well since I don't think you will need decimal places.
    Last edited by WilliamR; 01-08-2006 at 03:21 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width