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

error in executing trigger ddl

hi,

When executing trigger say TRG_XYZ  following error coming like

ORA-04042: procedure, function, package, or package body does not exist
grant execute on TRG_XYZ  to LMN_rolename_ROLE

can you please suggest. Any links, resources ideas highly appreciated. Thanks in advance
0
gudii9
Asked:
gudii9
  • 5
  • 3
5 Solutions
 
sdstuberCommented:
triggers don't have grants.

if lmn_rolename_role can modify your table that trg_xyz sits on then the trigger will fire.
0
 
sdstuberCommented:
in other words.  if the role has update grant on your table and your trigger is an update trigger then it will fire when anybody updates that table, including anybody that updates it via that role's privilege or via any other privileges (such as direct grants or other roles)
0
 
FVERCommented:
does the trigger TRG_XYZ contains a call to a procedure, function, package, or package body on which LMN_rolename_ROLE as not the execute grant ?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
sdstuberCommented:
you wouldn't get that error if it did.  You would get an error when you tried to create or compile the trigger.

The ORA-4042 error is because the "grant execute" command was issued against an object that can't have execute granted to it,  so Oracle looked for some other object of the same name but a valid type that it could grant execute on.
0
 
gudii9Author Commented:
Actually all the roles are empty as of now which were created using command
create role LMN_rolename_ROLE;
They are not pointing to any table, or package or function etc as of now.

Trigger code also pretty simple as of now looks like

DROP TRIGGER TRG_XYZ ;

CREATE OR REPLACE TRIGGER TRG_XYZ
   BEFORE INSERT
   ON abc_tableName
   FOR EACH ROW
BEGIN
   IF :NEW.lmn_id IS NULL
   THEN
      SELECT seq_lmn_id.NEXTVAL
        INTO :NEW.lmn_id
        FROM DUAL;
   END IF;
END;



Please advise. can I  ignore trigger errors as of now ?. Or what is the best work around to avoid these errors. These ddls were generated in the first place from the toad etc db client  tool by  generating ddls of entire schema.
0
 
sdstuberCommented:
those are fine

the problem was the grant

you can't do this...

grant execute on TRG_XYZ  to LMN_rolename_ROLE

and, even if you could,  you wouldn't want to.  It wouldn't make sense.
instead, try this...

grant insert on abc_tableName to LMN_rolename_ROLE

0
 
gudii9Author Commented:
I gave already select, update, insert, delete at the start of this script file like
grant select,insert,update,delete on abc_tableName to LMN_rolename_ROLE
even before running statement
grant execute on TRG_XYZ  to LMN_rolename_ROLE

So this case I can igore and remove
the below  statement right
grant execute on TRG_XYZ  to LMN_rolename_ROLE ?
Please advise
0
 
sdstuberCommented:
yes remove

grant execute on TRG_XYZ  to LMN_rolename_ROLE
0
 
gudii9Author Commented:
Thank you very much.  I appreciate it.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now