Create Database Links in another User's name

##########################
## Create Database Links in another User's name
##########################

Here we are going to create a private database link in MY_ACC user account by logging as SYS user.

DB link name is MY_RPT.

Db link will be created in database remote_db and its username is remote_user.

##########################
## DB Link With Tnsnames entry
##########################

declare
uid number;
sqltext varchar2(1000) := 'create database link MY_RPT connect to remote_user identified by password using 'remote_db'';
myint integer;
begin
select user_id into uid from dba_users where username like 'MY_ACC';
myint:=sys.dbms_sys_sql.open_cursor();
sys.dbms_sys_sql.parse_as_user(myint,sqltext,dbms_sql.native,UID);
sys.dbms_sys_sql.close_cursor(myint);
end ;
/

##########################
## DB Link Without Tnsnames entry
##########################

Here TNS entry is not present, so creating the database link using tns entry address.

declare
uid number;
sqltext varchar2(1000) := 'create database link MY_RPT connect to remote_user identified by password using ''(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host02-vip)(PORT = 1531))
    (ADDRESS = (PROTOCOL = TCP)(HOST = host03-vip)(PORT = 1531))
    (CONNECT_DATA =
      (SERVICE_NAME = remote_db)
    )
  )''';
myint integer;
begin
select user_id into uid from dba_users where username like 'MY_ACC';
myint:=sys.dbms_sys_sql.open_cursor();
sys.dbms_sys_sql.parse_as_user(myint,sqltext,dbms_sql.native,UID);
sys.dbms_sys_sql.close_cursor(myint);
end ;
/