Amazon books

Monday, June 10, 2013

RE-Create database link another schema in Oracle


There is a way to simple do that using dbms_sys_sql package.

--Change your username here MYUSER
DECLARE
  lv_stmt VARCHAR2(2000);

  PROCEDURE run_sql_as(p_user IN VARCHAR2, p_statement IN VARCHAR2) IS
    lv_userid NUMBER;
lv_cursor NUMBER;
lv_result NUMBER;
  BEGIN
-- Get USER_ID for specified user
SELECT user_id INTO lv_userid FROM dba_users WHERE username = p_user;

-- Open, parse, execute and close
lv_cursor := sys.dbms_sys_sql.open_cursor;
sys.dbms_sys_sql.parse_as_user(lv_cursor, p_statement, dbms_sql.native, lv_userid, TRUE);
lv_result := sys.dbms_sys_sql.execute(lv_cursor);
sys.dbms_sys_sql.close_cursor(lv_cursor);
  EXCEPTION
WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE('Failed to execute the specified statement for user: '||p_user);
 DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM, 1, 100));
  END;

BEGIN
  run_sql_as('MYUSER','DROP DATABASE LINK DBL_ZZZ');
  lv_stmt := 'CREATE DATABASE LINK "DBL_ZZZ"
   CONNECT TO "XXXUSER" IDENTIFIED BY VALUES ''0581EB2B81E82590B67687F1E6852E21C6BB0FFF991398A158''
   USING ''(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = host1.b2w)(PORT = 1522))
  (ADDRESS = (PROTOCOL = TCP)(HOST = host2.b2w)(PORT = 1522))
  (ADDRESS = (PROTOCOL = TCP)(HOST = host3.b2w)(PORT = 1522))
  (ADDRESS = (PROTOCOL = TCP)(HOST = host4.b2w)(PORT = 1522))
  (LOAD_BALANCE = yes)
  (CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = MYDB)
   (FAILOVER_MODE =
    (TYPE = SELECT)
    (METHOD = BASIC)
    (RETRIES = 180)
    (DELAY = 5)
   )
  )
 )''';
  run_sql_as('MYUSER',lv_stmt);
END;
/



Best Regards,
Paulo Portugal

No comments:

Post a Comment