Solved

ORA-00942 table or view does not exist

Posted on 2004-10-14
17
8,864 Views
Last Modified: 2008-01-09
When I run this statement using SQL*Plus Worksheet
            INSERT INTO Rd.FIELD_DEVICE_DEFN_STL
            select "RD"."FIELD_DEVICE_DEFN"."OID",
                "RD"."FIELD_DEVICE_DEFN"."DEVICENAME",
               "RD"."FIELD_DEVICE_DEFN"."DEVICETYPE",
                   "RD"."FIELD_DEVICE_DEFN"."USERNOTES"
                   FROM "RD"."FIELD_DEVICE_DEFN"
            
I get this error
INSERT INTO "Richmond"."FIELD_DEVICE_DEFN_STL"
                                     *
ERROR at line 1:
ORA-00942: table or view does not exist

The asterisk is directly under the double quote before FIELD_DEVICE_DEFN_STL

I created the table Rd.FIELD_DEVICE_DEFN_STL from Rd.FIELD_DEVICE_DEFN by using Create Like in Enterprise Manager.

The select statement returns the rows that I want to insert into the other table.  I am only inserting about half of the columns from Rd.FIELD_DEVICE_DEFN into Rd.FIELD_DEVICE_DEFN_STL.

Based on some stuff I read here I granted a couple of privileges to this table to the user.

I have very little Oracle experience, so I use OEM to make changes.

Thanks
0
Comment
Question by:averyb
  • 5
  • 4
  • 3
  • +4
17 Comments
 
LVL 10

Expert Comment

by:SDutta
ID: 12308368
If both your tables are in the same schema RD then you don't need to prefix RD. to the table names. You mentioned "Richmond"."FIELD_DEVICE_DEFN_STL" in one place, if you are trying to insert into a different schema then you will need to get the grants for it. Connect as the schema owner and :
GRANT SELECT, INSERT ON <table_name> TO <user_name>;
Where user_name is the user from which you will run the insert statement.

If you just created the empty table FIELD_DEVICE_DEFN_STL in OEM for this purpose then you can just do this.

connect RD/password@sid
DROP TABLE FIELD_DEVICE_DEFN_STL;
CREATE TABLE FIELD_DEVICE_DEFN_STL AS SELECT OID, DEVICENAME, DEVICETYPE, USERNOTES FROM FIELD_DEVICE_DEFN;
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 12308569
You just need to DOUBLE QUOTES your Rd.FIELD_DEVICE_DEFN_STL, that's all.

try this:

INSERT INTO "RD"."FIELD_DEVICE_DEFN_STL"
         select "RD"."FIELD_DEVICE_DEFN"."OID",
              "RD"."FIELD_DEVICE_DEFN"."DEVICENAME",
             "RD"."FIELD_DEVICE_DEFN"."DEVICETYPE",
                 "RD"."FIELD_DEVICE_DEFN"."USERNOTES"
                 FROM "RD"."FIELD_DEVICE_DEFN";


if this is not working, try to check the privs on this table by :


select * from "RD"."FIELD_DEVICE_DEFN_STL";


if you still getORA-00942: table or view does not exist
that means you don't have select privs on this table,

you need to grant the select priv to this user:

by

grant select on "RD"."FIELD_DEVICE_DEFN_STL" to <the user run this insert sql>;
0
 
LVL 8

Expert Comment

by:a_twixt_in_the_tale
ID: 12308636
try removing all double quotes

:)
Don
0
 
LVL 4

Author Comment

by:averyb
ID: 12308935
Don--
Removing all double quotes returns
ORA-00947: not enough values

SeaZodian--
If everything has double quotes, then I get
ORA-00942: table or view does not exist

SDutta--
The select statement on the table works fine.  No rows are returned since the table is empty.  Your other solution should work.  I'll try it and let everyione know.
0
 
LVL 10

Expert Comment

by:SDutta
ID: 12309048
The CREATE TABLE AS SELECT ... should work for you.

But to solve your original problem with ORA-00942 you need to tell us what schema is your source (FIELD_DEVICE_DEFN) table, destination (FIELD_DEVICE_DEFN_STL) table and what user you are connected as when you run the INSERT.
0
 
LVL 9

Expert Comment

by:konektor
ID: 12309087
check how are your tables/columns were created :
select table_name from user_tables
select table_name, column_name from user_tab_colums

if there are all table names and column names uppercase, than u can use
select * from Test
select * from TEST
select * from test
select * from TeSt

but if u can see any lowercases there u have to use case sensitive names :
if select table_name from user_tables returns : Test
u have to use only this statment :
select * from "Test"
0
 
LVL 8

Assisted Solution

by:a_twixt_in_the_tale
a_twixt_in_the_tale earned 150 total points
ID: 12309127
> Don--
> Removing all double quotes returns
> ORA-00947: not enough values
ur select query doesnt return enuf columns. ie ur select query returns say 4 cols and ur table into which u r inserting prob has 8 columns

use the values keyword to specify which all columns into which u want to insert data, or insert null values for the cols u don't want

:)
Don
0
 
LVL 1

Expert Comment

by:DMichaelHarvey
ID: 12310233
I may be wayyyy off center here, but my experience with that error (each time) is that I have forgotten to grant select on table_name to user_role.

I have no problem when I select when logged in as the table owner, but when I'm logged in as joe user who has some user_role, it fails.  All because I forgot a grant.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 50 total points
ID: 12310771
I may be missing something.......

As everyone has already stated:  The problem is either with the quotes or the permissions.

Here's where I get confused:

In the original question: The provided insert statement is inserting into  Rd.FIELD_DEVICE_DEFN_STL

But the provided error message is inserting into "Richmond"."FIELD_DEVICE_DEFN_STL"

If the problem is truly inserting into a table in the "Richmond" schema:  konektor is on the right track.  

Adding double quotes to a schema/table/column in Oracle forces case sensitivity.  If you user "Richmond" was created w/o the quotes, then this is where the problem is.  In Oracle, create user Richmond...  is very different from: create user "Richmond"

As a general rule of thumb:  It's better to stay away from double quotes when creating objects in Oracle.
0
 
LVL 4

Author Comment

by:averyb
ID: 12311170
I am logging into the database with the same user name and password that our application uses.  The account has full access to all the tables in the schema RD.  I don't think there are any issues with the grants.  Could be wrong.

I will be doing daily data dumps of about a dozen tables and then ftp'ing them off site.  Not knowing much about Oracle I would rather stay away from dropping and creating the tables each time.  

Everything is in the same schema and everything is all caps.

Don--
I am leaning toward your solution.  I had this script working once, but I was exporting all the columns.  

I don't know the syntax for doing this.
" . . .use the values keyword to specify which all columns into which u want to insert data, or insert null values for the cols u don't want"

Inserting nulls in the unwanted columns is fine with me, I just don't know the command.

Thanks



0
 
LVL 10

Expert Comment

by:SDutta
ID: 12311246
Averyb,

You mentioned the following error :
INSERT INTO "Richmond"."FIELD_DEVICE_DEFN_STL"
                                     *
ERROR at line 1:
ORA-00942: table or view does not exist


Where does Richmond come into the picture ?
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 12311302
Averyb:

SQL worksheet has some limitation in terms of the string length and quoting.

I agree with DON,
you should remove all your quotes and RD since you are using the same user account, you don't need to prefix with schema user:

So run it with Just the COLUMN name and table name:

INSERT INTO FIELD_DEVICE_DEFN_STL
select FIELD_DEVICE_DEFNOID, DEVICENAME, DEVICETYPE, USERNOTES
FROM FIELD_DEVICE_DEFN;


If you get the "not enough values" error,
that means that You have Fewer columns in the SELECT than the table FIELD_DEVICE_DEFN_STL expects.

so check you query and table structure.
0
 
LVL 4

Author Comment

by:averyb
ID: 12311336
Slightwv,

I was shortening Richmond to RD before posting the  statements.  I forgot to do the same on the error message.  Everything really is Richmond, but I shortened it to RD for posting.

The information on the double quotes is well appreciated.  I never understood the difference.  

Based on comments here I have removed all double quotes from my statements as well as the redundant schema reference before the table name.

0
 
LVL 23

Expert Comment

by:seazodiac
ID: 12311353
remove the table name also.

see my post above for your modified version of sql statement:

what the heck, I repost here:

INSERT INTO FIELD_DEVICE_DEFN_STL
select FIELD_DEVICE_DEFNOID, DEVICENAME, DEVICETYPE, USERNOTES
FROM FIELD_DEVICE_DEFN;


If you get the "not enough values" error,
that means that You have Fewer columns in the SELECT than the table FIELD_DEVICE_DEFN_STL expects.

so check you query and table structure.
0
 
LVL 4

Author Comment

by:averyb
ID: 12311425
You and Don nailed it on the head.

I am only exporting some of the columns from the Table.  How do I populate the non-exported columns in the _STL table with nulls?

0
 
LVL 23

Accepted Solution

by:
seazodiac earned 300 total points
ID: 12311545
you just need to explicitly specify the columns you want to popluate in the _STL table.
Map the four columns in the SELECT with the columns in _STL table:
I will assume they have the same column names, if not please switch:


here is the sample:

INSERT INTO FIELD_DEVICE_DEFN_STL (FIELD_DEVICE_DEFNOID, DEVICENAME, DEVICETYPE, USERNOTES)
select FIELD_DEVICE_DEFNOID, DEVICENAME, DEVICETYPE, USERNOTES
FROM FIELD_DEVICE_DEFN;
0
 
LVL 4

Author Comment

by:averyb
ID: 12312604
SeaZodiac's example worked great, but Don was the first one to mention the root cause; so he got some points too.

The comment about forcing case-sensitivity was also worthwhile.

Thanks again.

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now