Hi All,

I am having a sql file which will extract the synonym list based on user input. It will execute once and it will come out. IS it possible to run the sql multiple times based on user input until control +c is pressed.
[what I meant is user will input schema name first-->generate output file, then it should ask for schema name again--> generate output.. It should continue until control -c is pressed.]Appreciate your help on this. Below is the code.



set echo off
set verify off
set feedback off
prompt This script allows you to build a SQL*Plus script file
prompt which will recreate all PUBLIC synonyms referencing
prompt objects in a specified schema.
prompt
prompt To abort execution, press ctrl-C.
prompt
accept SynRefsOwner char prompt 'Schema >'
set linesize 132
set pagesize 0
set termout off
set trimspool on
set trimout on

--Spool the output the file requested by the user.
--spool &SynScriptFileName
spool &SynRefsOwner._synonyms.sql

select 'create public synonym '
|| synonym_name
|| ' for '
|| table_owner || '.' || table_name
|| ';'
from dba_synonyms
where table_owner = upper('&SynRefsOwner')
and owner = 'PUBLIC'
UNION
select '--No public synonyms were found referencing the schema '''
|| upper('&SynRefsOwner')
|| '''.'
from dual
where not exists (
select *
from dba_synonyms
where table_owner = upper('&SynRefsOwner')
and owner = 'PUBLIC'
);

--Turn spooling off to close the file.
spool off

--Reset session settings back to their defaults.
set verify on
set feedback 6
set linesize 80
set termout on
set pagesize 24
set trimspool off
set trimout on
set echo on
Thanks