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
