Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Getting PLS-00103 on sql block. Why?

Permutationally_challengedJan 19 2017 — edited Jan 20 2017

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

PL/SQL Release 12.1.0.2.0 - Production

CORE    12.1.0.2.0      Production

TNS for Solaris: Version 12.1.0.2.0 - Production

NLSRTL Version 12.1.0.2.0 - Production

We have a bad package that is causing phantom processes to build.  The change is coming to fix it, but is suppose take a few weeks to get finished.

In the mean time I'm trying to (learning pl/sql) write a block to "search and destroy" these phantom processes.

I built the code first to just SHOW what it was going to do.  And that part works.  But when I add in the actual execution, I'm getting the PLS-00103 error.

Desired result is to spool a file that shows what sessions will be killed for record, and then kills them.  Block will be put into a cron job to be called X times a day, and logging to the spool file.

Here is the code that works: 

SET serveroutput ON;

DECLARE

  v_sid     NUMBER;

  v_serial# NUMBER;

  

  CURSOR b_ses_cur IS

    SELECT sid, serial# FROM v$session WHERE module LIKE 'osc%';

BEGIN

  dbms_output.put_line('spool /tmp/phantom_sessions.lst');

  dbms_output.put_line('The following sessions will be killed.');

  FOR v_ses IN b_ses_cur

  LOOP

    dbms_output.put_line('alter system kill session '''||v_ses.sid||','||v_ses.serial#||''' immediate;');

    dbms_output.put_line('alter system disconnect session '''||v_ses.sid||','||v_ses.serial#||''' immediate;');

  END LOOP;

END;

/

when I add in an "EXECUTE IMMEDIATE" statement, and it's declaration, that's when I get the fault.

SET serveroutput ON;

DECLARE

  v_sid     NUMBER;

  v_serial# NUMBER;

  sqlTxt    VARCHAR2(1000);

 

  CURSOR b_ses_cur IS

    SELECT sid, serial# FROM v$session WHERE module LIKE 'osc%';

   

  sqlTxt := Q'{alter system kill session '''v_ses.sid,v_ses.serial#''' immediate; alter system disconnect session '''v_ses.sid, v_ses.serial#''';}'

BEGIN

  spool /tmp/phantom_sessions.lst

  dbms_output.put_line('The following sessions will be killed.');

  FOR v_ses IN b_ses_cur

  LOOP

    dbms_output.put_line('alter system kill session '''||v_ses.sid||','||v_ses.serial#||''' immediate;');

    dbms_output.put_line('alter system disconnect session '''||v_ses.sid||','||v_ses.serial#||''' immediate;');

    EXECUTE IMMEDIATE sqlTxt;

  END LOOP;

spool off

END;

/

Again, I'm trying to learn pl/sql....So regardless if there is a better WAY of doing it, I would like to figure this piece out.  Feel free to recommend better methodology, but please also help me figure this method out too.

Here is the full error.

ORA-06550: line 9, column 10:

PLS-00103: Encountered the symbol "=" when expecting one of the following:

   constant exception <an identifier>

   <a double-quoted delimited-identifier> table long double ref

   char time timestamp interval date binary national character

   nchar

06550. 00000 -  "line %s, column %s:\n%s"

*Cause:    Usually a PL/SQL compilation error.

*Action:

I looked up the syntax for the execute immediate and it's declaration.  The "=" does have to be ":="

So I'm confused.

Thanks in advance for any help.

This post has been answered by AndrewSayer on Jan 19 2017
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 17 2017
Added on Jan 19 2017
8 comments
3,412 views