Creating table in Oracle then inserting multiple fors with select

Question

I am using Oracle 11G with Toad 10.6. I am trying to create a table, then insert rows from a select statement that will pull records from 1 primary table (Product - shown below), and several secondary tables (which I did not include) which are joined in the rest of the code to filter the results

create table mjhottemp 
(
CustID number (10),
CanvCD varchar2 (6),
CanvISS number (3)
);
COMMIT;
Insert into MJHOTTEMP
(custid, canvcd, canviss)
SELECT DISTINCT
   r.CUSTOMER_ID AS custid, r.CANVASS_CODE AS canvcd, r.CANVASS_ISSUE_NUM as canviss

FROM core.product r

When I run this, I get an error on the "Insert into MJHOTTEMP" line

ORA-00942: table or view does not exist

I see the table in the schema. Any ideas why this does not work?

what do you see when you run `select table_name from user_tables where upper(table_name) = 'MJHOTTEMP';`? — Boneist, Mar 16 '15 at 13:42
not related to your question, but the "commit" after the "create table" is not necessary. — Rajesh Chamarthi, Mar 16 '15 at 13:51
Data grid shows table_name in the column heading and MJHOTTEMP as the only row. — Mark Justice, Mar 16 '15 at 14:07
I did not think I needed the commit, but was trying everything I could think of — Mark Justice, Mar 16 '15 at 14:07

kevinskio · Accepted Answer · 2015-03-16T13:58:41.477

0

This is usually a permissions error. Verify that the user you are connecting as has select (at a minimum) on the core.product table.

GRANT SELECT ON core.product TO 'your_user';

I am assuming you are not using custom user functions or selecting from views or procedures owned by other users. In this case you may need to add

GRANT SELECT ON core.product TO 'your_user' WITH REFERENCES;

edited Mar 16 '15 at 13:58

answered Mar 16 '15 at 13:42

kevinskio

4,431
1
22
36

I know I have select permissions on that table, because I selected records from it in an earlier version of the query when I was verifying that I had the correct output. – Mark Justice Mar 16 '15 at 14:09
I found the problem with the help of your suggestion. I had renamed some tables that were part of the rest of the query, and did not update the references to those tables. This was purely user error. Sorry for taking up your time. – Mark Justice Mar 16 '15 at 14:51

score 0 · Answer 2 · answered Mar 16 '15 at 13:43

0

the error message regards this table: core.product

answered Mar 16 '15 at 13:43

Pavel Gatnar

3,987
2
19
29

I don't think it is a "Select" issue, because I was able to run the full select statement in a prelimiary version when I was verifying that I was getting the correct records. I only get the error when I try to insert the records into the MJHOTTEMP table. So are you saying I need Insert permissions on the core.product table just to insert records on the MJHOTTEMP table? – Mark Justice Mar 16 '15 at 13:56
maybe you need to grant INSERT permission to MJHOTTEMP – Pavel Gatnar Mar 16 '15 at 14:15
what about the other tables (which you don't include): 1. try select; 2. try insert into values – Pavel Gatnar Mar 16 '15 at 14:25
Can I grant myself those permissions? The other tables were part of the full select statement that I have run many times succesfully. – Mark Justice Mar 16 '15 at 14:28
1. try only the SELECT part of your INSERT statement, 2. try INSERT INTO MJHOTTEMP VALUES (...some dummy values...) – Pavel Gatnar Mar 16 '15 at 14:41
I found the problem with the help of your suggestion. I had renamed some tables that were part of the rest of the query, and did not update the references to those tables. This was purely user error. Sorry for taking up your time. – Mark Justice Mar 16 '15 at 14:51

Creating table in Oracle then inserting multiple fors with select

2 Answers2