Link to home
Start Free TrialLog in
Avatar of MRPETERCARROLL
MRPETERCARROLL

asked on

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;
----*
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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.
Avatar of MRPETERCARROLL
MRPETERCARROLL

ASKER

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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
MMS_BASE is the user that owns the trigger.
>>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;
>>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;
--/
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.