Subject:            SQL: SCRIPT FOR CREATING DATABASE LINKS
Author:             RADRANLY
Last Revision Date: 31 January   1996
________________________________________________________________________________


REM
REM                 SCRIPT FOR CREATING DATABASE LINKS
REM
REM This script must be run by 'SYS'.
REM
REM This script is intended to run with Oracle7.
REM
REM Running this script will in turn create a script to build all the
REM database links in the database.  This created script is called
REM 'create_db_links.sql'.
REM
REM Since a DBA cannot create a private database link on behalf of a user,
REM this script will contain various connect clauses before each create
REM statement.  In order for the database links to be created under
REM the correct schema, it must connect as that individual.  Therefore, before
REM executing the script, you must add each user's password to the connect
REM clause.  Duplicate connect clauses can be eliminated being sure that the
REM database link is being created under the correct schema.
REM
REM The PUBLIC database links will require a connect as 'SYS'.  However, this
REM username can be changed to any user with the DBA role or with the
REM 'CREATE PUBLIC DATABASE LINK' system privilege.
REM
REM The spooled output is ordered by the database link owner, a PUBLIC database
REM link has 'PUBLIC' as it's owner.
REM
REM Only preliminary testing of this script was performed.  Be sure to test
REM it completely before relying on it.
REM

set verify off
set feedback off
set termout off
set echo off
set pagesize 0

set termout on
select 'Creating database link build script...' from dual;
set termout off

create table dl_temp (lineno NUMBER, grantor_owner varchar2(20),
                    text VARCHAR2(800));

DECLARE
   CURSOR link_cursor IS select   u.name,
                                  l.name,
                                  l.userid,
                                  l.password,
                                  l.host
                         from     sys.link$ l, sys.user$ u
                         where    l.owner# = u.user#
                         order by l.name;
   lv_owner    sys.user$.name%TYPE;
   lv_db_link  sys.link$.name%TYPE;
   lv_username sys.link$.userid%TYPE;
   lv_password sys.link$.password%TYPE;
   lv_host     sys.link$.host%TYPE;
   lv_string     VARCHAR2(800);
   lv_user       VARCHAR2(255);
   lv_connect    VARCHAR2(255);
   lv_text       VARCHAR2(800);

   procedure write_out(p_string VARCHAR2) is
   begin
      insert into dl_temp (grantor_owner,text)
                        values (lv_owner,p_string);
   end;

BEGIN
   OPEN link_cursor;
   LOOP
      FETCH link_cursor INTO lv_owner,
                             lv_db_link,
                             lv_username,
                             lv_password,
                             lv_host;
      EXIT WHEN link_cursor%NOTFOUND;
if (lv_owner = 'PUBLIC') then
      lv_string := ('CREATE PUBLIC DATABASE LINK '||
                lower(replace(lv_db_link,'.WORLD','')));
else
      lv_string := ('CREATE DATABASE LINK '||
                lower(replace(lv_db_link,'.WORLD','')));
end if;
      if (lv_username is not null) then
         lv_user := ('CONNECT TO '||lower(lv_username)||
                   ' IDENTIFIED BY '||lower(lv_password));
      end if;
      if (lv_host is not null) then
         lv_connect := ('USING '''||lv_host||''''||';');
      end if;
   lv_text := lv_string || ' ' || lv_user || ' ' || lv_connect;
   write_out(lv_text);
   lv_user := ' ';
   lv_connect := ' ';
   END LOOP;
   CLOSE link_cursor;
END;
/

spool create_db_links.sql
break on downer skip 1
col text format a60 word_wrap

select   'connect ' || decode (grantor_owner, 'PUBLIC', 'SYS', grantor_owner)
         || '/' downer,
         text
from     dl_temp
order by downer
/
spool off

drop table dl_temp;

exit