Strange behavious - inconsistencies between SQL in SQL*PLUS & SQL embedded in PL/SQL

I have a query which runs fine in SQL*Plus but which will not compile into a packaged procedure (claiming that the table can not be found):

  SELECT DISTINCT Folder_ID
  INTO l_RootID -- remove this line in SQL*Plus context   FROM ifssys.ifs_folder_items
  WHERE Folder_Name = 'Root Folder'
  AND TYPE = 'FOLDER';
-> PL/SQL: ORA-00942: table or view does not exist

(If I get rid of the "INTO" by using a cursor - making the code identical - I still get the same thing.)

I have tried "AUTHID CURRENT_USER" and "AUTHID DEFINER" in the package specification but these do not make any difference (as expected - it is not a run-time error).
I have tried granting "SELECT" on all IFSSYS tables and views, but this makes no difference (as expected).

I am starting to pull my hair out, and I have a horrible feeling I am missing something obvious, but I CAN'T SEE IT!

I am running iAS 9.0.2 and DB 9.0.1 (I am using the DB inside iAS - I know it is less than ideal, but I am working on a tight hardware budget and this is a development server).

Please can someone give me some pointers or a solution!

Many thanks in advance,

ETA Received on Wed Jul 02 2003 - 12:09:13 CEST