Home » SQL & PL/SQL » SQL & PL/SQL » Re: PL/SQL ; SQL plus
Re: PL/SQL ; SQL plus [message #36510] Sat, 01 December 2001 11:22
andrew once again
Messages: 1
Registered: December 2001
Junior Member
No, pl/sql can't loop and prompt the user on each loop.

Try this sql script which when run from sqlplus will generate the required scripts based on the user responses and then run the final script to drop the specified tables. Try in a dev environment first - understand what the script is doing...

rem zz_drop_tables.sql
rem written by AH Macey
rem This script runs from sqlplus and prompts the user to drop tables
rem in the current schema.
rem use at your own risk.
rem

SET HEADING OFF
SET ECHO OFF
SET FEEDBACK OFF
SET PAGESIZE 0
set verify off
set scan off
set linesize 200
set trimspool on
set termout off

-- generate a sript to prompt the user
spool temp1.sql
prompt spool temp2.sql
prompt set scan on

prompt COLUMN a new_val thisuser noprint
prompt COLUMN b new_val thisdb noprint
prompt SELECT USER a, SUBSTR (global_name, 1, 20) b FROM global_name;;

prompt PROMPT REM Currently connected to : &thisuser@&thisdb

column Temp1_Commands format a200
select
'prompt REM Ctrl-C to exit'||chr(10)||
'prompt REM The Table '||table_name||' is present on your account'||chr(10)||
'accept YN prompt "REM Do you want to DROP it? - Y or N [[default N]] : "'||chr(10)||
'select chr(10)||decode(upper(''&YN''), ''Y'', ''prompt Dropping table '||
table_name||'...''||chr(10)|| ''DROP table '||
table_name||' cascade constraints;''||chr(10),''prompt Not dropping table '||
table_name||'...'') from dual;'||chr(10) Temp1_Commands
from user_tables;
rem spooling off temp2.sql now...
prompt spool off
prompt rem running the final script to do the job now...
prompt accept dummy prompt "Press enter to DROP SPECIFIED TABLES NOW or Ctrl-C to exit (final warning)"
prompt set feedback on
prompt @@temp2.sql
spool off
set termout on
@@temp1.sql

----------------------------------------------------------------------
Previous Topic: Re: PL/SQL ; SQL plus
Next Topic: Re: MS SQL
Goto Forum:
  


Current Time: Mon Apr 06 04:57:54 CDT 2020