Knowledge Base : Create DBLinks in schema

20 Juni 16, 2022

How to create a database link in a different user schema

As SYS user, it is not possible to create a database link for another user.

If you want to maintain a different schemas database links, and you don’t have the literal password of this user, since you usually maintain it’s objects directly as SYS, you can use DBMS_SQL to go forward.

This procudure creates links in a different schema:

create or replace procedure repo_link_creator(
owner varchar2,
uid number,
lname varchar2,
login varchar2,
pass varchar2,
adress varchar2)
as
sqltext varchar2(4000) := 'create Public? database link LinkName?
connect to Login? identified by "Pass?"
using Adress?';
i integer;
BEGIN
if owner = 'PUBLIC'
then
sqltext := replace(sqltext, 'Public?', 'PUBLIC');
else
sqltext := replace(sqltext, 'Public?', '');
end if;
sqltext := replace(sqltext, 'LinkName?', lname);
sqltext := replace(sqltext, 'Login?', login);
sqltext := replace(sqltext, 'Pass?', pass);
sqltext := replace(sqltext, 'Adress?', adress);
i := sys.dbms_sys_sql.open_cursor();
sys.dbms_sys_sql.parse_as_user(i, sqltext, dbms_sql.native, uid);
sys.dbms_sys_sql.close_cursor(info);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR: Failed to execute the link creation for: '||lname);
DBMS_OUTPUT.PUT_LINE('SQL tried was: '||sqltext);
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM, 1, 100));
END repo_link_creator;

See also: Oraganism: How to Create a Database Link in Another User’s Schema, Creating database links for another schema