oracle-databaseplsqlexecute-immediate

SQL Command Not Properly Ended for EXECUTE IMMEDIATE


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.


Solution

  • 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.