How to fix 'name already being used' error in Oracle SQL?

Question

CREATE TABLE permission (
    user_id           INTEGER NOT NULL,
    device_id         VARCHAR2(20) NOT NULL,
    permission_type   VARCHAR2(20),
    permission_id     INTEGER NOT NULL
)
LOGGING;

ALTER TABLE permission ADD CONSTRAINT permission_pk PRIMARY KEY ( permission_id );

I'm getting the following error,

 Error report -
    ORA-00955: name is already used by an existing object
    00955. 00000 -  "name is already used by an existing object"
    *Cause:    
    *Action:

The table `permission` probably already exists. Drop it first. — Gordon Linoff, Apr 13 '19 at 01:15
to fix this choose other object name. in your case - other table name, bcause as you can see in your error report - IT EXISTS. you can also drop this table first and create new one with your code. — Maciej S., Apr 13 '19 at 01:15
Have you checked if permission or permission_pk already exist? — George, Apr 13 '19 at 01:10
Which statement generates the error? The first or the second? — , Apr 13 '19 at 07:02
see this https://stackoverflow.com/questions/1008248/how-do-i-use-create-or-replace — Nikhil S, Apr 13 '19 at 08:14

Dmitry Demin · Answer 1 · 2019-04-15T07:14:00.750

You can change the code.

   declare 
    duplicate_table EXCEPTION;
    PRAGMA EXCEPTION_INIT (duplicate_table , -955);
    begin

    execute  immediate 'CREATE TABLE permission (
        user_id           INTEGER NOT NULL,
        device_id         VARCHAR2(20) NOT NULL,
        permission_type   VARCHAR2(20),
        permission_id     INTEGER NOT NULL
    )
    LOGGING' ;

    exception
     WHEN duplicate_table  THEN
        dbms_output.put_line('Duplicate table');
        --null;

    end;


SQL*Plus: Release 12.2.0.1.0 Production on Mon Apr 15 10:10:01 2019

Copyright (c) 1982, 2017, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL> set serveroutput on;
SQL> declare
  2  duplicate_table EXCEPTION;
  3  PRAGMA EXCEPTION_INIT (duplicate_table , -955);
  4  begin
  5
  6  execute  immediate 'CREATE TABLE permission (
  7      user_id           INTEGER NOT NULL,
  8      device_id         VARCHAR2(20) NOT NULL,
  9      permission_type   VARCHAR2(20),
 10      permission_id     INTEGER NOT NULL
 11  )
 12  LOGGING' ;
 13
 14  exception
 15   WHEN duplicate_table  THEN
 16      dbms_output.put_line('Duplicate table');
 17      --null;
 18
 19  end;
 20  /

Duplicate table

PL/SQL procedure successfully completed.

How to fix 'name already being used' error in Oracle SQL?

1 Answers1