OraFAQ Forum: SQL & PL/SQL » ORA-00942 at select

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00942 at select (11gR2 on Windows Server 2008R2 64bit)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-00942 at select [message #471332]

Fri, 13 August 2010 07:51

larsebaer
Messages: 13
Registered: March 2009

Junior Member

Hello,
i have a strange behavior on my database.
When i do a

SELECT NAME, NAME_1 from NAMES where NAME = :1

i get an ORA-00942 table or view does not exist.

when i do a

SELECT NAME, NAME_1  from NAMES where NAME = :1

(additional space in front of "from")

i get a result.

Every time i try exact the first SQL i get the error.

... and the first select does not appear in the V$SQLAREA -view.

what is wrong in my database?

Best Regards

Lars

[Updated on: Fri, 13 August 2010 08:09] by Moderator

Report message to a moderator

Re: ORA-00942 at select [message #471333 is a reply to message #471332]

Fri, 13 August 2010 07:57

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

larsebaer wrote on Fri, 13 August 2010 08:51

Hello,
i have a strange behavior on my database.
When i do a

SELECT NAME, NAME_1 from NAMES where NAME = :1

i get an ORA-00942 table or view does not exist.

when i do a

SELECT NAME, NAME_1 from NAMES where NAME = :1
(additional space in front of "from")

i get a result.

Every time i try exact the first SQL i get the error.

... and the first select does not appear in the V$SQLAREA -view.

what is wrong in my database?

Best Regards

Lars

Where are you running this as that is not valid syntax for a SQL prompt? I'd like to see a full session of your commands and error. I am guessing that that is not the query that is running. Maybe you are building it and you "think" you have spaces in the correct place.

Report message to a moderator

Re: ORA-00942 at select [message #471353 is a reply to message #471333]

Fri, 13 August 2010 10:34

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What is your client program? This will not surprise me if you answer TOAD.

Use SQL*Plus, try the same and post your session.

Before, Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

Report message to a moderator

Re: ORA-00942 at select [message #471901 is a reply to message #471353]

Tue, 17 August 2010 14:46

larsebaer
Messages: 13
Registered: March 2009

Junior Member

Hi Michael,

originally i have this behavior in a VB6-Program. Then i reviewed the SQL-Code in the v$sqlarea-view. This sql i tested in T.O.R.A. . In SQL*PLUS i have a Problem with the :1 Variable SQL*PLUS does not like the :1.

For some other reason i have restarted the Oracle instance. I now have to check if the problem is still there, or if the restart has fixed it. If it is gone there is still the question: what was it?

Best Regards

Lars

Report message to a moderator

Re: ORA-00942 at select [message #471904 is a reply to message #471901]

Tue, 17 August 2010 14:55

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

If it is gone there is still the question: what was it?

How can we know, you destroyed the environment that would allow some tests. So, forget it and come back if this happen again.

Quote:

In SQL*PLUS i have a Problem with the :1 Variable SQL*PLUS does not like the :1.

In SQL*Plus you have to give the name of a variable you declared in it.

Regards
Michel

Report message to a moderator

Re: ORA-00942 at select [message #471907 is a reply to message #471904]

Tue, 17 August 2010 15:04

larsebaer
Messages: 13
Registered: March 2009

Junior Member

Hi Michael,

i know that i now have destroyed that environment, but there were reasons to do so.... Smile

Quote:

In SQL*Plus you have to give the name of a variable you declared in it.

yes and that was the point... when changing one thing on that statement, the error was gone. It occured only on this particular statement (with this :1).

When it happens again, i will post again.

Thank you so far and Best Regards

Lars

Report message to a moderator

Re: ORA-00942 at select [message #471908 is a reply to message #471907]

Tue, 17 August 2010 15:09

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If this happens then use the DUMP function to also dump the sql text in sqlarea, maybe you have a non-printable character in it.

Regards
Michel

Report message to a moderator

Re: ORA-00942 at select [message #471909 is a reply to message #471901]

Tue, 17 August 2010 15:20

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

larsebaer wrote on Tue, 17 August 2010 12:46

SQL*PLUS does not like the :1.

SQL*Plus expects variable names to start with a letter, not a number. The two different demos below show what happens when you try to use a variable named 1, then what happens when you run the same code using a variable named a.

with 1

SCOTT@orcl_11gR2> variable g_ref refcursor
SCOTT@orcl_11gR2> variable 1 varchar2(1)
SP2-0553: Illegal variable name "1".
SCOTT@orcl_11gR2> exec :1 := 'X'
SP2-0552: Bind variable "1" not declared.
SCOTT@orcl_11gR2> begin
  2    open :g_ref for
  3    select dummy
  4    from   dual
  5    where  dummy = :1;
  6  end;
  7  /
SP2-0552: Bind variable "1" not declared.
SCOTT@orcl_11gR2> print g_ref
ERROR:
ORA-24338: statement handle not executed


SP2-0625: Error printing variable "g_ref"
SCOTT@orcl_11gR2>

with a

SCOTT@orcl_11gR2> variable g_ref refcursor
SCOTT@orcl_11gR2> variable a varchar2(1)
SCOTT@orcl_11gR2> exec :a := 'X'

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> begin
  2    open :g_ref for
  3    select dummy
  4    from   dual
  5    where  dummy = :a;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> print g_ref

D
-
X

1 row selected.

SCOTT@orcl_11gR2>

Report message to a moderator

Re: ORA-00942 at select [message #472337 is a reply to message #471909]

Fri, 20 August 2010 01:04

larsebaer
Messages: 13
Registered: March 2009

Junior Member

Hi there,
this error seems to be described in ORACLE BUG 9577583

---------------------
ORA-942, or other unexpected errors, can occur when running in a setup
where multiple schemas have identical objects and users are only expected
to access objects in their own schemas.

The problem can cause a SQL built for a given user to incorrectly
have dependency / translation / access table entries for other
users objects:
- If there is no permission on those objects then various errors can occur.
- If the current user does have permission on those objects then SQL may
execute using objects from the wrong schema leading to wrong results
or logical data corruption.

This problem can also lead to various dumps when executing PLSQL
especially when the PLSQL is executed from RMAN.

Rediscovery Notes:
A library_cache dump of the problem cursor shows that the child
cursor has incorrect entries in the dependency table.
---------------------

We do have different schemas with identically objects.....

So i wait for Patch 11.2.0.2 .... (it schould fix this bug...)

Best Regards

Lars

Report message to a moderator

Re: ORA-00942 at select [message #472340 is a reply to message #472337]

Fri, 20 August 2010 01:15

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Thanks for the feedback.

Regards
Michel

Report message to a moderator

Re: ORA-00942 at select [message #472378 is a reply to message #472340]

Fri, 20 August 2010 04:40

larsebaer
Messages: 13
Registered: March 2009

Junior Member

.... and 11.2.0.2 for Windows x64 is scheduled sonewhere in Q4CY2010 by oracle....

so have a look after the database if you have this error - no patch before october...

Regards
Lars

Report message to a moderator

Re: ORA-00942 at select [message #472438 is a reply to message #472378]

Fri, 20 August 2010 09:46

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Thanks again.

Regards
Michel

Report message to a moderator

Previous Topic:	Set operations with Nested Tables of PL/SQL type.
Next Topic:	Outer table Join with not equal condition

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Sep 03 22:42:33 CDT 2025