By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,321 Members | 1,206 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,321 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
bi********@hotmail.com (Ethel Aardvark) wrote in message news:<1a**************************@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

Is ifs_folder_items a public synonym by any chances? If it is, then
there is no reasons to attach a schema name to it.

- Jusung Yang
Jul 19 '05 #2

P: n/a
"Ethel Aardvark" <bi********@hotmail.com> wrote in message
news:1a**************************@posting.google.c om...
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
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.