CODEWITHSUNDEEP
sqloracle-databaseinsert
Chimeara
Chimeara

Reputation: 695

Can't access Oracle SQL Table

I am using an Oracle Database and am trying to add a table, but unfortunately I don't have full access to the database, I created my table creation script and ran it locally without any issue ( I can select, insert, delete etc), After passing the same script to get run on the remote server I got the log back saying there were no errors. However I can not access my new table.

I can see the table using 

SELECT *
FROM all_tables 
WHERE table_name = '[my New Table]'

However when I run 

SELECT * 
FROM [my New Table]

I get an error: [Oracle]ORA-00942: table or view does not exist. I assume it is an issue with the permissions but can't find any errors with my code 

GRANT SELECT, UPDATE, DELETE, INSERT ON "[USER WHO CREATES THE TABLE]"."[my New Table]" to [MY_ROLE];

Below is the SQL execution code:

PROMPT Connecting as the application schema owner [owner] to &&dbname
ACCEPT ownerpassword char format [format] prompt "Please enter [owner] password: " hide
CONNECT [owner]/&ownerpassword@&&dbname

@@pr_dr_all.sql --(drops table if exists)
@@pr_cr_tables_tso_tcom.sql --(creates the table)
@@pr_gr_tso_tcom.sql --(sets grants)
@@pr_cr_constraints_tso_tcom.sql --(sets constraints)

PROMPT Connecting as the application user [user] to &&dbname
ACCEPT userpassword char format [format] prompt "Please enter [user] password: " hide 
CONNECT [user]/&userpassword@&&dbname

@@pr_dr_syn.sql --(drops synonyms)
@@pr_cr_syn_tso_tcom.sql --(creates synonyms)

spool off

pr_cr_tables_tso_tcom.sq:

CREATE TABLE "[owner]"."[table name]"
  (
    "[column 1]" NUMBER NOT NULL ENABLE,
    "[column 2]" NUMBER,
    "[column 3]" VARCHAR2(200 BYTE),
    "[column 4]" VARCHAR2(200 BYTE),
    "[column 5]" NUMBER,
    "[column 6]" NUMBER
  )
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
  )
  TABLESPACE "USERS" ;

  CREATE SEQUENCE  [owner]."[table name]_SEQ"  MINVALUE 1 MAXVALUE 99999999999999999999 INCREMENT BY 1 START WITH 81 CACHE 20 NOORDER  NOCYCLE ;

    CREATE OR REPLACE TRIGGER "[owner]"."[table name]_TRG" BEFORE
      INSERT ON [table name] FOR EACH ROW WHEN (NEW.ID IS NULL) BEGIN
      SELECT [table name]_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
    END;
    /
    ALTER TRIGGER "[owner]"."[table name]_TRG" ENABLE;

pr_gr_tso_tcom.sql

GRANT SELECT, UPDATE, DELETE, INSERT ON "[owner]"."[table name]" to [role];
GRANT SELECT ON "[owner]"."[table name]_SEQ"  to [role];

pr_cr_syn_tso_tcom.sql

CREATE SYNONYM [table name]_SYN FOR "[owner]"."[table name]";
CREATE SYNONYM [table name]_SEQ_SYN FOR "[owner]"."[table name]_SEQ";

Upvotes: 0

Views: 4902

Answers (2)

Andrew Cruickshank
Andrew Cruickshank

Reputation: 91

If you are trying to run the query from a different schema to the one that the table is located on you will need to prefix the table name with the schema, e.g.

SELECT * FROM [schema that table is located on].[my New Table]

Otherwise you could create a synonym for the table name.

Upvotes: 1

user3055805
user3055805

Reputation: 21

in any case you have to set schema for creating table , insert .... if not oracle will choose the default schema.

first make sure your tables created in right schema, contact them and ask for schema.if you didn't set schema so it is possible they created in other schema,

if you just pass them a sql file and they run it with sqlplus or other tools, it's so much more possible to make error.

start your sql file with following lines :

alter session set current_schema=PUTSchemaNameHere;

spool /folder/NameOfLog.log
set termout off
set echo on 
set feedback on 
set timing on 
set define off

the above code will generate a log in specified folder with desired name, so in case of any error you can trace back the error. (Or make sure table created)

if you pass them command one by one make sure to put the schema before the table name, procedure name, ....

for example the following command will create a table in desired schema

create table PutSchemaNameHere.TableName (column1 number,column2 varchar2(50));

Upvotes: 2

Related Questions