SQL Command Not Properly Ended for EXECUTE IMMEDIATE-CodePudding

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.

CodePudding user response：

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;

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 rec_show_connections.sqlstatement;

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.