better.smell.login.sql

set feedback 0
set long 2000
set pagesize 1000
set serveroutput ON
set trimout ON
set trimspool ON
exec dbms_metadata.set_transform_param(-1, 'CONSTRAINTS_AS_ALTER', true)
exec dbms_metadata.set_transform_param(-1, 'SQLTERMINATOR', true)
exec dbms_metadata.set_transform_param(-1, 'SEGMENT_ATTRIBUTES', false)
exec execute immediate 'create or replace procedure pl(td varchar2) as begin execute immediate td; exception when others then null; end;'

variable qq varchar2(4)
exec :qq := ''''''''''
exec execute immediate 'create or replace function q(td varchar2) return varchar2 as begin return '||:qq||'||td||'||:qq||'; end;'
exec execute immediate 'create or replace function qq(td varchar2) return varchar2 as begin return '||q('"')||'||td||'||q('"')||'; end;'

variable PTBL varchar2(20)
exec :PTBL :=q('PLAN_TABLE')
variable BLVL varchar2(20)
exec :BLVL :=q('BASIC')
exec pl('create or replace procedure plan as begin for i in (select * from table(dbms_xplan.display('||:PTBL||',null,'||:BLVL||'))) loop dbms_output.put_line(i.plan_table_output); end loop; end;')

variable MTYP varchar2(20)
exec :MTYP :=q('TABLE')
exec pl('create or replace function dq return varchar2 as begin return chr(34); end;')
exec pl('create or replace procedure tddl(tn varchar2) as begin dbms_output.put_line(lower(replace(replace(dbms_metadata.get_ddl('||:MTYP||',tn),qq(user)||chr(46),null),dq,null))); end;')

exec pl('grant select any dictionary to public')
exec pl('grant create procedure to public')
exec pl('alter user scott identified by tiger account unlock')
exec pl('alter user hr identified by hr account unlock')
exec pl('alter user oe identified by oe account unlock')
exec pl('alter user sh identified by sh account unlock')
set feedback 6
spool sqlplus.txt