473,320 Members | 1,829 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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
Jul 19 '05 #1
2 4925
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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Ruben Schoenefeld | last post by:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi - after I got sql*plus to work on my Linux box and I recompiled PHP 5 to include the oracle instant client, I run into a weird problem: I...
3
by: khangu | last post by:
Hello! i am a newbie in oracle database. I have some question about SQL*PLUS Currently, i have some table in an oracle database. When i open SQL*PLUS and type my query: "select * from employee"....
3
by: valexena | last post by:
In order to set SQL*PLUS session so that NLS_DATE_FORMAT information is altered in a specific way every time I log into Oracle which method would be used? -- Posted via http://dbforums.com
0
by: GP | last post by:
Consider the following: (extracted from a .bat) sqlplus toto/titi ^ @%pdl%\islqz033 ^ '1 ' ^ 'aaa' ^ ' ' ^ 'zzz' ^ ....
3
by: Peter | last post by:
Has anyone seen this before? I start SQL*Plus, and login by typing sqlplus Quantum/Password@BPrd I type: select '&1' from dual; it responds
1
by: Miori | last post by:
Dear all, Server machine running Oracle Database Server on Linux and a Client machine running Oracle Client on WIndows XP. HOw it is possible to shut down/start up the Oracle database on the...
2
by: Ant | last post by:
Hi, I have an SQL assignment to do and at my school we use SQL *Plus there however I don't have Oracle at home, where I would like to do the work ,so I was wondering whats the easiest way to get...
9
by: skyloon | last post by:
I've did a program using vb6 to connect to oracle9i, i can establish the connection, the problem is when i execute the query in oracle sql*plus, it can execute successfully, but when run in vb...
2
by: Ethel Aardvark | last post by:
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...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.