The script in my last post has some errors in the use of dbms_sql
The correct version is:
declare
newline char(1) := chr(10); /* Newline command */
string varchar2(1000);
c integer;
l_db_link user_db_links.db_ink%type;
cursor each_c is select db_link
from user_db_links;
begin
open each_c;
loop
fetch each_c into l_db_link;
exit when each_c%NOTFOUND;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse ( c , 'select sysdate from dual@' ||
l_db_link ,
dbms_sql.native );
c := dbms_sql.close_cursor;
string := 'conn hr/hr@' || l_db_link || newline || '
@c:\script1.sql;' ||
newline || ' @c:\script2.sql;' ||
newline || ' @c:\script3.sql;' ||
insert into temptbl (command) values (string);
when others
dbms_output.put_line ( '--The databadase pointed by ' ||
l_db_link || ' is down' );
end;
end loop;
end;
Received on Fri Dec 23 2005 - 02:49:25 CST
