Chimeara Chimeara - 1 month ago 6
SQL Question

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

Answer

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.