Running DDL Statements on Remote Database

You cannot run any DDL statement on a remote databases via using our custom remote sqls.

CREATE TABLE EMP@DB1 ...

This causes an error. Instead of this you can use the Dynamic Sql to produce ddl statement to execute it when it is called.

For example, There are two DB. A_DB and B_DB. We write this function in A_DB:

CREATE OR REPLACE PROCEDURE dyn_sql( v_Sql_in VARCHAR2 )
IS
   curName INTEGER;
   iRet INTEGER;
BEGIN
   curName := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(curName, v_sql_in, DBMS_SQL.NATIVE);
   iret := DBMS_SQL.EXECUTE(curName);
   DBMS_SQL.CLOSE_CURSOR(curName);
END;

When you call this procedure as "exec dyn_sql('CREATE TABLE EMP (EMPNO INTEGER)');" in B_DB database, an EMP table will be created in A_DB database.