Power to Build

Home » CodeProject » Oracle SQL*Plus – Forward slash contd… (PL/SQL)

Oracle SQL*Plus – Forward slash contd… (PL/SQL)

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, 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!


Comments, please?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: