Re: How to kill a session (Script)

EXECUTE IMMEDIATE will only work with a certain oracle version upwards (i think 8.0.??)

Try using this procedure in place (note the '_' in between execute immediate!)

Procedure Execute_Immediate(p_statement IN varchar2) is

• ========================================================
• 'Simple' Procedure to execute a given DDL command
• ========================================================

    v_cursor number;
    v_numrows number;
   --
   BEGIN

     v_cursor := DBMS_SQL.OPEN_CURSOR;
     DBMS_SQL.PARSE(v_cursor, p_statement, DBMS_SQL.V7);
     v_numrows := DBMS_SQL.EXECUTE(v_cursor);
     COMMIT;
     DBMS_SQL.CLOSE_CURSOR(v_cursor);

     WHEN OTHERS THEN
       DBMS_SQL.CLOSE_CURSOR(v_cursor);
      RAISE;

Mark

"Geoffrey" <g.dufour_at_swing.be> wrote in message news:<3c565eac$0$33510$ba620e4c_at_news.skynet.be>...
> Hi David,
>
> Thanks for your help.
>
> Here is the error I get :
>
> =======================================
>
> The following error has occurred:
>
> PROCEDURE MYUSER.TEST2
> On line: 3
> PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the
> following:
>
> := . ( _at_ % ;
>
> =======================================
> PROCEDURE test2 IS
>
> BEGIN
>
> execute immediate 'ALTER SYSTEM KILL SESSION '2,3'';
>
> END test2;
>
> =======================================
>
>
> "David A. Levy" <david.a.levy_at_rogers.com> a écrit dans le message de news:
> W3j58.4242$liz.3068_at_news2.bloor.is...
> > Hi Geoffrey,
> >
> > I think the problem you are having is that only DML and SELECT SQL
> > statements are allowed to be used directly in PL/SQL. So you can do
 INSERT,
> > SELECT etc, but not ALTER, CREATE etc. You have to use dynamic SQL. So
 this
> > procedure does NOT work:
> >
> > CREATE OR REPLACE PROCEDURE
> > PROCEDURE sample_alter AS
> > BEGIN
> > alter system enable restricted session;
> > END;
> >
> > whereas this one should (assuming the user owning the procedure has privs
 to
> > execute an ALTER SYSTEM in the first place)
> >
> > PROCEDURE SCOTT.sample_alter IS
> > BEGIN
> > execute immediate 'alter system enable restricted session';
> > END;
> >
> > If the user does not have the privs you will get messages like these:
> >
> > ERROR at line 1:
> > ORA-01031: insufficient privileges
> > ORA-06512: at "SCOTT.SAMPLE_ALTER", line 3
> > ORA-06512: at line 1
> >
> > hope this helps,
> > David.
> >
> >
Received on Wed Jan 30 2002 - 09:40:31 CET