Results 1 to 2 of 2
  1. #1
    Join Date
    May 2015
    Posts
    1

    Unanswered: Invalid identifier error in package but the column is valid

    Hello All,

    When I am executing Below select query, it is being executed properly & giving me desired output.

    SELECT sslock.userid , sslock.plantstructureentryid , sslock.audsid
    FROM sslock
    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
    AS

    PROCEDURE removeIllegalLocks
    IS

    CURSOR curIllegalLocks
    IS
    SELECT sslock.userid , sslock.planid, sslock.audsid
    FROM sslock
    WHERE
    sslock.audsid NOT IN (SELECT audsid FROM v$session) ;


    BEGIN

    FOR recIllegalLocks IN curIllegalLocks
    LOOP

    DELETE FROM sslock WHERE userid = recIllegalLocks.userid AND planid = recIllegalLocks.planid AND audsid = recIllegalLocks.audsid;

    END LOOP ;

    COMMIT;


    END removeIllegalLocks;

    END pkg_sslock;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •