asked on

ORA-00942 table or view does not exist

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

SDutta

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;

seazodiac

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>;

a_twixt_in_the_tale

try removing all double quotes

:)
Don

averyb

ASKER

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.

SDutta

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.

konektor

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"

SOLUTION

a_twixt_in_the_tale

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DMichaelHarvey

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.

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

averyb

ASKER

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

SDutta

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 ?

seazodiac

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.

averyb

ASKER

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.

seazodiac

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.

averyb

ASKER

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?

ASKER CERTIFIED SOLUTION

seazodiac

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

averyb

ASKER

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.