How to drop table with dblink

Question

How to drop a table if it exist in oracle with dbLink?I am here trying to connect to remote database using DBLink in oracle.I am using oracle sql developer.

DROP TABLE TableName@dbLinkName. This gives me error Unknown Command.

begin declare
    v_mytableobject_id
        NUMBER(10);
SELECT
    object_id INTO v_mytableobject_id FROM    user_objects@dblink
WHERE
    object_name = 'MYTABLE';

IF v_mytableobject_id IS NOT NULL THEN
    dbms_utility.exec_ddl_statement@dblink ( 'drop table MYTABLE' );
ELSE
    dbms_utility.exec_ddl_statement@dblink ( 'CREATE TABLE MYTABLE
   (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)
    
   )'
    );
    dbms_utility.exec_ddl_statement@dblink ( 'CREATE UNIQUE INDEX MYTABLE_PK ON MYTABLE (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 IF;

end;

Littlefoot · Accepted Answer · 2023-06-11 21:11:53Z

2

You can't drop table just like that:

SQL> drop table test@dbl_mike;
drop table test@dbl_mike
                *
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database

There's a workaround, though:

SQL> begin
  2    dbms_utility.exec_ddl_statement@dbl_mike('drop table test');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>

answered Jun 11, 2023 at 21:11

Littlefoot

144k15 gold badges42 silver badges65 bronze badges

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

2 Comments

coder11 b Over a year ago

I have the code tried above on the question. How do i check if a table exist before drop and create statement ..How do i combine ddl and dml statements

Littlefoot Over a year ago

Query USER_TABLES via database link to see whether it exists. Though, if you just try to DROP it (without any checking), that should also be OK because Oracle will simply do nothing (it'll inform you that table doesn't exist, but - disregard it).

Collectives™ on Stack Overflow

How to drop table with dblink

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related