05-14-15, 18:24 #1Registered User
- Join Date
- May 2015
Unanswered: Invalid identifier error in package but the column is valid
When I am executing Below select query, it is being executed properly & giving me desired output.
SELECT sslock.userid , sslock.plantstructureentryid , sslock.audsid
WHERE sslock.audsid NOT IN ( SELECT audsid FROM v$session );
But whenever I am using same select query as a cursor in a package, then while compiling it is giving "ORA-00904: "audsid": invalid identifier".
Below I am giving the exact package. Both package & the table are from the same schema. Problem query is highlighted with bold & italic manner.
the query is running fine when we run it independently. But the moment I use it in the below package it is giving Invalid identifier error at the place of Delete statement.
create or replace
PACKAGE BODY pkg_sslock
SELECT sslock.userid , sslock.planid, sslock.audsid
sslock.audsid NOT IN (SELECT audsid FROM v$session) ;
FOR recIllegalLocks IN curIllegalLocks
DELETE FROM sslock WHERE userid = recIllegalLocks.userid AND planid = recIllegalLocks.planid AND audsid = recIllegalLocks.audsid;
END LOOP ;
05-15-15, 10:15 #2Registered User
Provided Answers: 1
- Join Date
- Aug 2003
- Where the Surf Meets the Turf @Del Mar, CA
privilege acquired via ROLE does not apply within named PL/SQL procedures
GRANT SELECT ON V_$SESSION TO THIS_USER;
issue GRANT above when logged onto the DB as the SYS user.You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Good judgement comes from experience. Experience comes from bad judgement.