Uso de accept e prompt

[Sarchigana 0]

E ai galera.....

 

 

me deem uma ajuda fiz o seguinte código e agora gostaria de colocar uma prompt pro cara colocar a opçaão de limpar as tabelas....

mas td q tentei ate agora deu erro....

vou colocar aqui o codigo original e gostaria q vcs me dessem uma ajuda com isso...

 

obrigado....

 

clear screen

begin

 

FOR R IN (SELECT OBJECT_NAME, OBJECT_TYPE

FROM USER_OBJECTS

WHERE OBJECT_TYPE IN ('TABLE','VIEW')

AND

(OBJECT_NAME LIKE '[bCHMS]%[_]%[0-9]'

OR OBJECT_NAME LIKE '%[M,N][0-9]%[C,F,G,I][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

OR OBJECT_NAME LIKE 'TABINT%[0-9]'

OR OBJECT_NAME LIKE 'TABMV%[0-9]'

OR OBJECT_NAME LIKE 'TABTEMP%_[0-9]'

OR OBJECT_NAME LIKE 'TABTMP%_[0-9]'

OR OBJECT_NAME LIKE 'TTN_%'

OR OBJECT_NAME LIKE 'TT_%'

OR OBJECT_NAME LIKE '%##TT_%'

OR OBJECT_NAME LIKE '[V][^T]%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

OR OBJECT_NAME LIKE '[V][T][_]%'))

 

LOOP

EXECUTE IMMEDIATE 'DROP ' || R.OBJECT_TYPE || ' ' || R.OBJECT_NAME;

END LOOP;

 

END;

/

exit ;

[Motta 645]

FAÇA UMA PROCEDURE ...

 

CREATE OR REPLACE PROCEDURE LIMPA (POBJ IN VARCHAR2)

IS

 

FOR R IN (SELECT OBJECT_NAME, OBJECT_TYPE

FROM USER_OBJECTS

WHERE OBJECT_TYPE IN ('TABLE','VIEW')

AND

(OBJECT_NAME LIKE = POBJ)

 

LOOP

EXECUTE IMMEDIATE 'DROP ' || R.OBJECT_TYPE || ' ' || R.OBJECT_NAME;

END LOOP;

 

END;

 

--

 

BEGIN

LIMPA('TABELA1');

END;