mc88 mc88 - 1 month ago 8
SQL Question

Oracle - script for immediate session kill (I get an error!)

I have written a simple script that will kill oracle session immediate:

DECLARE
v_kill VARCHAR2(32767);
BEGIN
FOR v_kill IN
(SELECT
'alter system kill session '''
||sid
||','
||serial#
||'''',
'immediate;'
FROM
v$session
WHERE
sql_id='sql_id_here'
)
LOOP
EXECUTE immediate v_kill;
END LOOP;
END;


Unfortunately I get an error which makes me stuck with this issue:

Error report -
ORA-06550: linia 18, kolumna 21:
PLS-00382: expression is of wrong type
ORA-06550: linia 18, kolumna 3:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:


Any help will be appreciated!

Answer

To use the cursor that way, you need to give al alias to the statement column and execute that:

BEGIN
    FOR v_kill IN
    (SELECT
            'alter system kill session '''
            ||sid
            ||','
            ||serial#
            || ''' immediate;' as statement
                    FROM
            v$session
                WHERE
            sql_id='sql_id_here'
    )
    LOOP
        dbms_output.put_line (v_kill.statement);
    END LOOP;
END;

Also, you do not need to declare a variable to handle the cursor.