Solved

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

Posted on 2013-01-02
13
3,747 Views
Last Modified: 2013-10-27
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
Comment
Question by:MRPETERCARROLL
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 325 total points
ID: 38736767
Make sure you grant execute to the trigger owner explicitly and not through a role.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 38736778
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
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 38736824
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:MRPETERCARROLL
ID: 38736951
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
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 325 total points
ID: 38736962
>>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
 

Author Comment

by:MRPETERCARROLL
ID: 38737105
MMS_BASE is the user that owns the trigger.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38737224
>>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
 

Author Comment

by:MRPETERCARROLL
ID: 38737414
>>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
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 325 total points
ID: 38737438
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
 

Author Comment

by:MRPETERCARROLL
ID: 38737472
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
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 325 total points
ID: 38737488
>>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
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39603601
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question