• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4572
  • Last Modified:

ORACLE PL/SQL PLS-00302 COMPONENT 'AAA' MUST BE DECLARED ERROR

I have an Oracle user called MMS_BASE in that schema the following trigger is created:

CREATE OR REPLACE TRIGGER MMS_BASE.BIZL_&&TableName
BEFORE INSERT ON &TableName FOR EACH ROW
BEGIN
   sys.ONW.NEWRECORDLOCK('MMS_BASE'||'.'||'&TableName'||'.'||:NEW.KEY);  
END;

However it is created with the following error, (the table name supplied is VOC):
SQL> SHOW ERRORS
Errors for TRIGGER MMS_BASE.BIZL_VOC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/4      PL/SQL: Statement ignored
2/8      PLS-00302: component 'ONW' must be declared

The package ONW is installed in the sys user (I know that some will say this is not a good practise), and appropriate grants have been given on ONW.

If an identical package (renamed ONW1) is installed in MMS_BASE, then the trigger is created without errors.

NEWRECORDLOCK is a procedure within ONW, and can call a function called GETLOCKID. The code for NEWRECORDLOCK and GETLOCKID located in ONW is below. Can anyone suggest how I can use the ONW package located in the sys user?

FUNCTION GETLOCKID(KEYVAL_IN VARCHAR2) RETURN NUMBER
IS BEGIN
RETURN DBMS_UTILITY.GET_HASH_VALUE(KEYVAL_IN,0,1024);-- Lower 1024?
END;
----*
PROCEDURE NEWRECORDLOCK(KEYVAL_IN VARCHAR2)
IS
   LOCK_ID NUMBER;
   RESOURCE_BUSY EXCEPTION;
   PRAGMA EXCEPTION_INIT(RESOURCE_BUSY,-54);
BEGIN
   LOCK_ID :=
      ONW.GETLOCKID(KEYVAL_IN);
   IF ( dbms_lock.REQUEST(LOCK_ID,DBMS_LOCK.X_MODE,0,TRUE) <> 0 )--Connect as SYS and "GRANT EXECUTE ON DBMS_LOCK TO user"
   THEN
      RAISE RESOURCE_BUSY;
   END IF;
END NEWRECORDLOCK;
----*
0
MRPETERCARROLL
Asked:
MRPETERCARROLL
  • 5
  • 4
  • 2
  • +1
4 Solutions
 
slightwv (䄆 Netminder) Commented:
Make sure you grant execute to the trigger owner explicitly and not through a role.
0
 
MikeOM_DBACommented:
Package "ONW.NEWRECORDLOCK" is not a system (SYS) package.

Execute this query to find out who owns that package:
SELECT *
  FROM dba_objects
 WHERE object_name LIKE 'O%'
   AND object_type LIKE 'PACKAGE%'
/

Open in new window

:p
0
 
MikeOM_DBACommented:
PS: The owner of that package (being SYS or not) needs to grant execute to MMS_BASE or whomever needs to use it, also user needs to be able to execute the "SYS" packages used by that package.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
MRPETERCARROLLAuthor Commented:
The package ONW is a package that I have created and installed in the SYS user. I have tried granting execute privileges to MMS_BASE, to PUBLIC but to no avail.
0
 
slightwv (䄆 Netminder) Commented:
>>execute privileges to MMS_BASE, to PUBLIC but to no avail.

PUBLIC is a ROLE.  Grant execute explicity to the user that owns the trigger.
0
 
MRPETERCARROLLAuthor Commented:
MMS_BASE is the user that owns the trigger.
0
 
slightwv (䄆 Netminder) Commented:
>>MMS_BASE is the user that owns the trigger.

OK, then grant execute to it:

as SYS:
grant execute on to ONW to MMS_BASE;
0
 
MRPETERCARROLLAuthor Commented:
>>MMS_BASE is the user that owns the trigger.

OK, then grant execute to it:

as SYS:
grant execute on to ONW to MMS_BASE;

Granting execute to MMS_BASE doesnt make any difference. Below is the complete package. When installed in MMS_BASE (with all references to ONW changed to ONW1), then the trigger compiles fine, when installed in sys, and referenced as sys.ONW I get the compile errors.

--=-=-=-=--
CREATE OR REPLACE PACKAGE ONW AS
---*
FUNCTION GETLOCKID(KEYVAL_IN VARCHAR2) RETURN NUMBER;
---*
PROCEDURE NEWRECORDLOCK(KEYVAL_IN VARCHAR2);
END;
/
----*
CREATE OR REPLACE PACKAGE BODY ONW AS
----*

----*
FUNCTION GETLOCKID(KEYVAL_IN VARCHAR2) RETURN NUMBER
IS BEGIN
RETURN DBMS_UTILITY.GET_HASH_VALUE(KEYVAL_IN,0,1024);-- Lower 1024?
END;
----*
PROCEDURE NEWRECORDLOCK(KEYVAL_IN VARCHAR2)
IS
   LOCK_ID NUMBER;
   RESOURCE_BUSY EXCEPTION;
   PRAGMA EXCEPTION_INIT(RESOURCE_BUSY,-54);
BEGIN
   LOCK_ID :=
      ONW.GETLOCKID(KEYVAL_IN);
   IF ( dbms_lock.REQUEST(LOCK_ID,DBMS_LOCK.X_MODE,0,TRUE) <> 0 )--Connect as SYS and "GRANT EXECUTE ON DBMS_LOCK TO user"
   THEN
      RAISE RESOURCE_BUSY;
   END IF;
END;
----*
END ONW1;
/
GRANT EXECUTE ON ONW TO MMS_BASE;
--/
---*EXIT;
--/
0
 
slightwv (䄆 Netminder) Commented:
I'll see if I can find anything else that jumps out at me.

I would also confirm what you posted above:  You should NEVER create objects in the SYS schema.  If you took that advice, then is the problem resolved since you can run it local?
0
 
MRPETERCARROLLAuthor Commented:
I'll see if I can find anything else that jumps out at me.

I would also confirm what you posted above:  You should NEVER create objects in the SYS schema.  If you took that advice, then is the problem resolved since you can run it local?

The reason, apparently, that the package is installed in sys is that the main application looks for the package there as it only needs to be installed once, and there can be many schemas that can use it (in excess of 30 in some cases), and it made the management easier.

When installed locally, it does compile and work ok.

Thanks very much for your help on this.
0
 
slightwv (䄆 Netminder) Commented:
>>and there can be many schemas that can use it (in excess of 30 in some cases)

Create an owning schema like 'PKG_OWNER', create it there.  Should be no different than qualifying SYS as the owner.

You can even create a SYNONYM (PUBLIC if you must but I'm against using public for anything since it is a security risk) for the rest of the calling schemas.
0
 
Steve WalesSenior Database AdministratorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now