Solution Found - Dynamic Database Link Creation
Mike Blake, October 20, 2017 - 3:44 am UTC
Careful review of your response pointed me in the right direction. The issue was not the variables or all the needed quotes. It was in your use of EXEC EXECUTE IMMEDIATE.
For many many reasons I almost never user EXECUTE IMMEDIATE and have never used EXEC EXECUTE IMMEDIATE. Once I did that everything fell into place. Need to go back to the books.
Thanx again and a perfect solution.
October 21, 2017 - 1:10 am UTC
glad we could help
Follow Up Question
Gaurav Prasad, September 17, 2020 - 9:11 pm UTC
Would really appreciate your valuable guidance in this.
create or replace procedure "CREATE_DYMANIC_DB_LINK"
IS
BEGIN
EXECUTE IMMEDIATE 'CREATE DATABASE LINK "REPO_HOST_DB.WORLD" CONNECT TO "SCMOPS" IDENTIFIED BY "ScmOpsTools" USING ''(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=slc17yak.us.oracle.com)(Port=25784))(CONNECT_DATA=(SID=str25784)))''';
END;
On running this via PL/SQL Block:
begin
CREATE_DYMANIC_DB_LINK;
end;
I'm getting this Error :
ORA-01031: insufficient privileges
ORA-06512: at "SCMOPS.CREATE_DYMANIC_DB_LINK", line 8
ORA-06512: at line 2
ORA-06512: at "SYS.DBMS_SQL", line 1721
Can you please and suggest what could have gone wrond
September 18, 2020 - 4:13 am UTC
Go to our Resources section, and search for"Roles" in the Presentation section. You'll get a link about roles within a procedure.
This will explain the resource and describe the solution.