Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-01-02
13
Medium Priority
?
3,936 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 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 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 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 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 77

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

705 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