Oracle – Forward Slash Contd… (PL/SQL)

Introduction

My friend was running an old PL/SQL script. The original developer seemed to have used another tool to run it, which was more pardoning with syntax than SQL*Plus. This exposed some new behaviors around (or lack of) forward slash.

The scripts were spooling into log files. For one of the scripts, he kept getting numbers!! Like:

201
202
203

It was puzzling, because we were not printing anything in the PL/SQL block except errors. After analyzing the code for a bit, we realized the script was missing the (REQUIRED) Forward slash after the PL/SQL block!!!

Here is a simple example:

test1.sql

declare
i number;
begin
select 1 into i from dual;
end;
<EOF>

I just included <EOF> here to show that’s the end of file.

Run the above script on command line as:

sqlplus <login> @test.sql

Now, you will see number 9 printed. If you keep pressing enter now, number will start incrementing. Unless you know what’s going on here, you could just fill up the screen with numbers quickly, like so:

9
10
11
12
13
…

At this point, if you know the problem, you will just press a “/” and off it will go. Or you can do a CTRL-C to kill the script!!

The above behavior is because the script file was missing “/” at the end of PL/SQL block! So, next time you code your PL/SQL block, make sure to include that blessed “/” at the end of the block!