0

I'm trying to check if table exists and if not, then I want to create it. But I'm still getting this error:

Error report -
ORA-06550: řádka 30, sloupec 28:
PL/SQL: ORA-00942: tabulka nebo pohled neexistuje
ORA-06550: řádka 30, sloupec 3:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.

My code is:

SELECT COUNT(*) into cnt FROM dba_tables WHERE owner = 'CENTRUMADMIN' and table_name = 'AUD$_BACKUP';
DBMS_OUTPUT.PUT_LINE(cnt);
IF (cnt <= 0) THEN
EXECUTE IMMEDIATE 'CREATE TABLE CENTRUMADMIN.AUD$_BACKUP AS select * from SYS.AUD$ where 1=2';
DBMS_OUTPUT.PUT_LINE('Vytvorena tabulka AUD$_BACKUP ve shematu CENTRUMADMIN:');
END IF;

This code is inside my procedure for cleaning audit trail.

Can anyone pls help? Thanks in advance!

user3682425
  • 21
  • 1
  • 5
  • 2
    seems that your code doesnt have access to view `dba_tables` –  May 28 '14 at 07:08
  • But if I try this under the same user: SELECT COUNT(*) FROM dba_tables WHERE owner = 'CENTRUMADMIN' and table_name = 'AUD$_BACKUP'; it returns 0 as expected. – user3682425 May 28 '14 at 07:12
  • 1
    Duplicate is for a different `dba_*` view, but the same problem; privileges granted via a role don't apply in a procedure. You might be able to use `all_tables` instead if you can't get the explicit grant; but then the table creation might be an issue too. – Alex Poole May 28 '14 at 07:17
  • But I was just testing it a running under sys... – user3682425 May 28 '14 at 07:24
  • If I create the table AUD$_BACKUP, everything is working fine. I get error when table doesn't exists. – user3682425 May 28 '14 at 07:30
  • OK, so the error isn't in the code you showed. Are you trying to select from or insert into the new table in the same procedure? If you are then it's a duplicate of a different question, and you have to do that dynamically as well. What is line 30? Creating tables on the fly is usually not necessary and often a bad idea. Doing anything as SYS is certainly a bad idea. – Alex Poole May 28 '14 at 07:49
  • Possible duplicate of [Creation of table and insertion within the same procedure in pl/sql](http://stackoverflow.com/q/21411737/266304), based on the comments. Please show the code the error is actually reported against to clarify the issue. – Alex Poole May 28 '14 at 08:28
  • Just to figure out exactly where the error is coming from, can you try to change your CREATE TABLE to something like CREATE TABLE CENTRUMADMIN.AUD$_BACKUP(col1 number)? – Craig May 28 '14 at 13:59

1 Answers1

0

As I read in your comments you said you get the error only when the table AUD$_BACKUP does not exist. which means you get you error here:

EXECUTE IMMEDIATE 'CREATE TABLE CENTRUMADMIN.AUD$_BACKUP AS select * from SYS.AUD$ where 1=2';

I guess you just dont have permissions for sys.AUD$ in you schema. please notice that PL/SQL code can use role privileges. which means, even if you have "dba" role on your schema, it wont work. so you should run the following grant command:

Alter user myuser select on sys.AUD$

good luck.

Nimrod
  • 370
  • 2
  • 11