Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-01-02
13
Medium Priority
?
4,122 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
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1300 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1300 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 78

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 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1300 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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1300 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 23

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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

916 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