How to check if a table exist before creating [duplicate]

Question

I am setting up database from a remote database using the dblink

here are my scripts to create table and constraints..

 begin 
     
  dbms_utility.exec_ddl_statement@mydblink('CREATE TABLE MYTABLE1
   (ID NUMBER(5,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE , 
    CODE NVARCHAR2(15), 
    DESCRIPTION NVARCHAR2(125), 
    IS_ACTIVE NUMBER(1,0)   DEFAULT 1
   )') ;
   
  dbms_utility.exec_ddl_statement@mydblink('CREATE UNIQUE INDEX MYTABLE1_PK ON MYTABLE1 (ID) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)');
  
   end;

i would like to check if table exist before creating them and drop the table if it exist. How can we achieve this.mydblink is the dblink used to connect to a database from a remote database

How to check if a tables/constraint exist and drop them all.

Pugzly · Accepted Answer · 2023-06-09 14:34:08Z

0

You can try this.


declare
v_cnt number;
v_sql varchar2(100);
begin 
    select count(*) into v_cnt from user_tables where table_name = 'ABC';

    if v_cnt=0 then

      v_sql:='create table abc(col1 number)';
      dbms_output.put_line(v_sql);
      execute immediate v_sql;
end if;

end;

answered Jun 9, 2023 at 14:34

Pugzly

9441 gold badge10 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Himanshu Kandpal Over a year ago

Hi , you could do it by capturing the table ORA-00955 in the Error handler, if the table exists. Please check this link for an example. database.guide/…

Collectives™ on Stack Overflow

How to check if a table exist before creating [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related