Oracle – Executing Generated Script from Output

oracle

I created a script that generates and outputs the required ALTER SYSTEM KILL SESSION commands that I need to execute.

main script: (without all the conditions, for better use here in SE)

SELECT 'ALTER SYSTEM KILL SESSION '||CHR(39)|| ao.SID ||',' || ao.SERIAL# ||CHR(39) || ' IMMEDIATE;' 
FROM v$session ao where (length(trim(ao.username)) = 5 
                and upper(substr(trim(ao.username),1,1)) = 'E'

output example: 

ALTER SYSTEM KILL SESSION '418,30413' IMMEDIATE;                                                                         
ALTER SYSTEM KILL SESSION '755,19371' IMMEDIATE;                                                                         
ALTER SYSTEM KILL SESSION '809,6715' IMMEDIATE;

How can I execute the generated ALTERs ?

I'm using Oracle SQL Developer 4.0.0.13

Best Answer

You'd copy and paste the commands and then run them. If you want to do this in a single step, you can write a small PL/SQL block

BEGIN
  FOR s IN (SELECT *
              FROM v$session ao 
             where (length(trim(ao.username)) = 5 
               and upper(substr(trim(ao.username),1,1)) = 'E')
  LOOP
    EXECUTE IMMEDIATE  'ALTER SYSTEM KILL SESSION '||
                         CHR(39)|| s.SID ||',' || s.SERIAL# ||CHR(39) || 
                         ' IMMEDIATE';
  END LOOP;
END;
Related Question