SQL> CREATE or replace PROCEDURE scott.create_db_link AS
BEGIN
EXECUTE IMMEDIATE 'create database link LINK1 connect to scott identified by tiger using ''testdb''';
END create_db_link;
2 3 4 5
6 /
Procedure created.
SQL> show user
USER is "SYS"
SQL> exec scott.create_db_link
PL/SQL procedure successfully completed.
SQL> select * from dba_db_links where OWNER='SCOTT';
OWNER DB_LINK USERNAME HOST CREATED
------------------------------ -------------------- ------------------------------ -------------------- ---------
SCOTT LINK1 SCOTT testdb 04-NOV-11
SQL> drop database link scott.LINK1;
drop database link scott.LINK1
*
ERROR at line 1:
ORA-02024: database link not found
SQL> CREATE PROCEDURE scott.drop_db_link AS
BEGIN
EXECUTE IMMEDIATE 'drop database link LINK1';
END drop_db_link; 2 3 4
5 /
Procedure created.
SQL> exec scott.drop_db_link
PL/SQL procedure successfully completed.
SQL> select * from dba_db_links where OWNER='SCOTT';
no rows selected
SQL>
5 comments:
Really good one :-)
Very useful.
Many thanks.
find solutions here
http://solutions-ever.blogspot.com/2014/06/how-to-create-drop-db-link-in-oracle.html
Thanks, very handy..
Thanks... I was forgetting the exec.
Post a Comment