Tuesday, January 11, 2011

Create DB Link in Other Schema

I have tried in several ways but failed and succeed using following set of sql (Thanks to Ritesh Raj Singh) :

SQL> grant create database link to schemauser ;

SQL> create or replace procedure schemauser.zcreatedblink as

execute immediate 'create database link DBLINK_NAME connect to TARGET_USER identified by TARGET_PASSWORD using ''TARGET_TNS''';


SQL> exec schemauser.zcreatedblink

SQL> revoke create database link from schemauser ;

SQL> drop procedure schemauser.createdblink;

Please inform if anybody has better idea.

1 comment:

  1. I think if you create this procedure dynamically it will be more powerful as in that case when need to create dblink for other user, you just need to call that procedure not to create same type procedure manually every time.