Search code examples
oracle-databaseplsqlinsertcreate-table

Creating table in Oracle then inserting multiple fors with select

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?

Solution

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