I am trying to write a script that will end sessions. I get the SQL command not properly ended when trying to EXECUTE IMMEDIATE and I can't figure out what I am doing wrong and why I am getting the error message. I have removed the ; form the line cur_show_connection and that didn't work as well.
|| ''' IMMEDIATE' sqlstatement
Here is my full code
declare
cursor cur_show_connections is
SELECT 'ALTER SYSTEM DISCONNECT SESSION '''
|| SID
|| ','
|| SERIAL#
|| ''' IMMEDIATE;' sqlstatement
FROM v$session
WHERE machine = 'XXXXX';
sqlstr varchar2(3200) :='';
begin
for rec_show_connections in cur_show_connections loop
sqlstr := sqlstr || rec_show_connections.sqlstatement || chr(13) || chr(10);
--Tried here and this didnt work either
--EXECUTE IMMEDIATE sqlstr;
end loop;
dbms_output.put_line(sqlstr);
EXECUTE IMMEDIATE sqlstr;
--This seems to work hardcoding the values
--EXECUTE IMMEDIATE q'[ALTER SYSTEM DISCONNECT SESSION '425,7516' IMMEDIATE]';
end;
Any help would be appreciated.
Don't end your ALTER SYSTEM
command with a semi-colon. The EXECUTE IMMEDIATE
command will take care of that.
cursor cur_show_connections is
SELECT 'ALTER SYSTEM DISCONNECT SESSION '''
|| SID
|| ','
|| SERIAL#
|| ''' IMMEDIATE' sqlstatement
FROM v$session
WHERE machine = 'XXXXX';
You're also concatenating your commands into a single EXECUTE IMMEDIATE statement. That won't work the way you're doing it: EXECUTE IMMEDIATE can only run one command or PL/SQL block at a time.
for rec_show_connections in cur_show_connections loop
dbms_output.put_line(rec_show_connections.sqlstatement);
EXECUTE IMMEDIATE rec_show_connections.sqlstatement;
end loop;
OR
sqlstr = 'BEGIN ';
for rec_show_connections in cur_show_connections loop
sqlstr := sqlstr || dbms_output.put_line(rec_show_connections.sqlstatement) || '; ';
end loop;
sqlstr := sqlstr || ' END;'
EXECUTE IMMEDIATE sqlstr;
Depending on how many sessions you might need to disconnect, the first option is less likely to have issues with character string size limitations. PL/SQL has a 32K limitation that could affect how big a single PL/SQL concatenated block could get, potentially throwing an error if you tried to kill too many sessions at once.