-
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.
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|