Home > Uncategorized > Running DDL Statements on Remote Database

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.

Advertisements
Categories: Uncategorized
  1. Tonguç
    September 2, 2008 at 9:08 am

    DBMS_HOB veya SCHEDULER ile de karşı tarafta DDL çalıştırılabilinir.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: