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

"Ethel Aardvark" <bigjobbies_at_hotmail.com> wrote in message news:1a8fec49.0307020209.66063c4e_at_posting.google.com...
> 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
I won't comment on whether it is obvious as we have all been caught with this one.

You must rember that when running direct SQL queries [say in SQL*Plus] the Oracle server
 will use the priviliges assigned to your session roles, however, when the quesies are inside a
package the roles will not be used. You will need to grant SELECT on IFSSYS.IFS_FOLDER_ITEMS
to the user who is compiling the package.

Cheers Received on Thu Jul 03 2003 - 00:43:38 CEST