SQL*Plus Terminator Torture

“Leave that jar of Nutella alone, it’s got my name on it !”
The context in which Deb issued this injunction to me probably requires some explanation.
It was Friday evening.
Wales had just…well…come second in the latest installment of their eternal battle with the English through the medium of Rugby.
There was no alcohol left in the house.
And only one source of chocolate.
From the safety of the Cupboard under the stairs, to which I had retreated at kick-off – the Welsh do take their Rugby quite seriously – I wondered about my better half’s change of name.
Shorn of it’s chocolate hazelnut spread connotations, you might think that Nutella was quite an nice name for a girl.
It certainly seems appropriate if the “Girl” in question is slightly unhinged by a combination of wine and wounded national pride.

I was going to write something here about how Rugby players all look like the Terminator and use this as a way of introducting the topic at hand. However, I realise that this would simply be too contrived…even for me.
Instead, I’ll jump straight in…

The Nature of SQL*Plus

SQL*Plus is the command line interface for the Oracle RDBMS. As such, it supports three categories of statement :

  • SQL*Plus commands – which require no terminator
  • SQL commands – terminated by a “/” on a new line
  • PL/SQL blocks – containing statements that are terminated by “;”

…but that’s not the whole story as we are about to discover….

select 'Hasta La Vista' from dual
/

set serveroutput on size unlimited

begin
    dbms_output.put_line(q'[Ill be back...from PL/SQL]');
end;
/

select 'Just you wait until the World Cup!' 
from dual;

Here we can see an example of all three statement types – SQL, SQL*Plus and PL/SQL.
However the final SQL statement is a bit different. The terminator is not the newline “/” as with the first statement, but a “;”.

It is this small quirk that can cause some interesting things to happen and leave you scratching your head for a good while until you figure out exactly what’s happened.

Spot the Terminator

Consider the following, run as an unattended batch job :

select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') from dual;
declare
    e_not_exists exception; 
    pragma exception_init(e_not_exists, -942);
begin
    execute immediate 'drop table tenuous_links purge';
exception when e_not_exists then null;
end;
/
    
create table tenuous_links( arnie_quote varchar2(100));
/

Simple enough – drop the table ( ignoring the ORA-00942 error if it doesn’t exist in the first place), then create it again…


TO_CHAR(SYSDATE,'DD-MON-YYYYH
-----------------------------
21-FEB-2015 13:12:20


PL/SQL procedure successfully completed.


Table created.

create table tenuous_links( arnie_quote varchar2(100))
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> 

At first glance, it would seem that our PL/SQL block to drop the table didn’t work for some reason.
However, if we dig a bit deeper…

select to_char(created, 'DD-MON-YYYY HH24:MI:SS')
from user_objects
where object_name = 'TENUOUS_LINKS'
and object_type = 'TABLE'
/

TO_CHAR(CREATED,'DD-MON-YYYYH
-----------------------------
21-FEB-2015 13:12:20

So, the table was created at the time we ran our script. We know it still exists (and is not in the recycle bin) because it’s still in USER_OBJECTS.

If the table was dropped then re-created when we ran our script then why did we get the error ?

Let’s have a look at that CREATE TABLE statement again :

create table tenuous_links( arnie_quote varchar2(100));
/

Notice that “;” at the end of the first line ?
SQL*Plus takes this to mean “execute the last statement in the buffer”.
Then on the next line we have the same directive – expressed using a different syntax – i.e. “/”.

If we correct the script by removing the extraneous “;” all now works as expected :


select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') from dual;
declare
    e_not_exists exception; 
    pragma exception_init(e_not_exists, -942);
begin
    execute immediate 'drop table tenuous_links purge';
exception when e_not_exists then null;
end;
/
    
create table tenuous_links( arnie_quote varchar2(100))
/

TO_CHAR(SYSDATE,'DD-MON-YYYYH
-----------------------------
21-FEB-2015 13:30:48


PL/SQL procedure successfully completed.


Table created.

Whilst debugging a small script like this is relatively straight forward, you’re likely to be confronted with a much longer script in the real world and spend considerably more time tracking down the problem.

Culling the Terminators

At this point we have a number of questions :

  1. How and why does SQL*Plus allow two terminator characters ?
  2. Can we turn one of them off ?
  3. Can we standardise on one of them and if so, which ?

It’s probably worth taking a closer look at the two terminator characters as they behave slightly differently in
certain circumstances. For example…

create or replace function arnie
    return varchar2
as
begin
    return(q'[I'll be back!]');
end;
/

Function created.

However, we get a bit less success if we try :

create or replace function arnie
    return varchar2
as
    begin
        return(q'[I'll be back!]');
end;;
SQL> 
SQL> 
SQL> 

SQL*Plus doesn’t recognise the second “;” as a terminator. Putting it on a separate line fares no better.
From this then, we can infer that the “/” is mandatory when generating DDL for PL/SQL stored program units.

The next question is how we end up with two terminators, and whether we can switch one of them off.
Well, let’s take a look at the SQL*Plus settings, or one setting in particular :

SQL> show sqlterminator
sqlterminator ";" (hex 3b)
SQL> 

We can use this parameter to set the terminator to another single character…


SQL> set sqlterminator !

SQL> select 'Another Terminator reference'
  2  from dual
  3  !

'ANOTHERTERMINATORREFERENCE'
----------------------------
Another Terminator reference

SQL> 

SQL> select 'Another Terminator reference' from dual !

'ANOTHERTERMINATORREFERENCE'
----------------------------
Another Terminator reference

SQL> 

SQL> select 'Another Terminator reference!' from dual !

'ANOTHERTERMINATORREFERENCE!'
-----------------------------
Another Terminator reference!

SQL> 

…but not multiple characters…

SQL> set sqlterminator runbuffer
string "runbuffer" is too long. maximum size is 1 character.
SQL> 

Interestingly, we can also disable it altogether and then re-enable it…

SQL> set sqlterminator off
SQL> show sqlterminator
sqlterminator OFF
SQL> select sysdate
  2  from dual;
  3  
SQL> 
SQL> select sysdate
  2  from dual
  3  /

SYSDATE
------------------
20-FEB-15

SQL> set sqlterminator ;
SQL> select sysdate
  2  from dual;

SYSDATE
------------------
20-FEB-15

SQL> 

Whilst this can be quite useful, especially if you’re running scripts that contain only DDL for stored program units, it does feel a bit odd having to put the terminator on a new line.
Additionally, you may consider that standardising this would require some serious regression testing of any SQL scripts to make sure that they’re not using the “;” terminator, not to mention any scripts that get generated dynamically.

Missing Terminators

Just as vexing as having too many terminators is not having enough.

consider :

create or replace package the_terminator as
    function favourite_terminator
        return varchar2;
end;

create or replace package body the terminator as

    function favourite_terminator
        return varchar2
    is
    begin
        return q'[There's only one Arnie !]';
    end favourite_terminator;
end the_terminator;
/

Warning: Package created with compilation errors.

SQL> sho err
Errors for PACKAGE THE_TERMINATOR:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/1	 PLS-00103: Encountered the symbol "CREATE"

SQL> select object_type          
  2  from user_objects
  3  where object_name = 'THE_TERMINATOR'
  4  /

OBJECT_TYPE
-------------------
PACKAGE

SQL> 

Now consider this in a longer script ( which I’ve instrumented with prompt statements) :

prompt Recording Start Time

select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') from dual;

prompt Dropping TENUOUS_LINKS

declare
    e_not_exists exception; 
    pragma exception_init(e_not_exists, -942);
begin
    execute immediate 'drop table tenuous_links purge';
exception when e_not_exists then null;
end;
/

prompt Creating table TENUOUS_LINKS
    
create table tenuous_links( arnie_quote varchar2(100))
/


prompt Creating Function ARNIE

create or replace function arnie
    return varchar2
as
begin
    return(q'[I'll be back !]');
end;

prompt Creating Package THE_TERMINATOR

create or replace package the_terminator as
    function favourite_terminator
        return varchar2;
end;
/

create or replace package body the terminator as

    function favourite_terminator
        return varchar2
    is
    begin
        return q'[There's only one Arnie !]';
    end favourite_terminator;
end the_terminator;
/

This time, we’ve missed the terminator at the end of the function.
However, this causes the Package to error as well :

Recording Start Time

TO_CHAR(SYSDATE,'DD-MON-YYYYH
-----------------------------
21-FEB-2015 14:11:39

Dropping TENUOUS_LINKS

PL/SQL procedure successfully completed.

Creating table TENUOUS_LINKS

Table created.

Creating Function ARNIE

Warning: Function created with compilation errors.


Warning: Package Body created with compilation errors.

SQL> sho err
Errors for PACKAGE BODY THE_TERMINATOR:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0	 PL/SQL: Compilation unit analysis terminated
1/14	 PLS-00304: cannot compile body of 'THE_TERMINATOR' without its
	 specification

1/14	 PLS-00905: object MIKE.THE_TERMINATOR is invalid
SQL> select object_name, object_type
  2  from user_objects
  3  where object_name in ('ARNIE', 'THE_TERMINATOR')
  4  /

OBJECT_NAME		       OBJECT_TYPE
------------------------------ -------------------
ARNIE			       FUNCTION
THE_TERMINATOR		       PACKAGE
THE_TERMINATOR		       PACKAGE BODY

SQL> 

There are a couple of things to note here. First is that it may well be worth enhancing the instrumentation in the script by including SHOW ERRORS after each stored program unit creation statement. The second is that there is no “smoking gun” error for either too many terminators, or not enough.
With SHOW ERRORS in place, it becomes a bit easier to spot what’s going wrong :

prompt Recording Start Time

select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') from dual;

prompt Dropping TENUOUS_LINKS

declare
    e_not_exists exception; 
    pragma exception_init(e_not_exists, -942);
begin
    execute immediate 'drop table tenuous_links purge';
exception when e_not_exists then null;
end;
/

prompt Creating table TENUOUS_LINKS
    
create table tenuous_links( arnie_quote varchar2(100))
/


prompt Creating Function ARNIE

create or replace function arnie
    return varchar2
as
begin
    return(q'[I'll be back !]');
end;

show error

prompt Creating Package THE_TERMINATOR

create or replace package the_terminator as
    function favourite_terminator
        return varchar2;
end;
/

show error


create or replace package body the_terminator as

    function favourite_terminator
        return varchar2
    is
    begin
        return q'[There's only one Arnie !]';
    end favourite_terminator;
end the_terminator;
/

show error

When you run this script, the problem is a bit easier to spot :

Recording Start Time

TO_CHAR(SYSDATE,'DD-MON-YYYYH
-----------------------------
21-FEB-2015 14:17:03

Dropping TENUOUS_LINKS

PL/SQL procedure successfully completed.

Creating table TENUOUS_LINKS

Table created.

Creating Function ARNIE

Warning: Function created with compilation errors.

Errors for FUNCTION ARNIE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/1	 PLS-00103: Encountered the symbol "SHOW"

Warning: Package Body created with compilation errors.

Errors for PACKAGE BODY THE_TERMINATOR:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0	 PL/SQL: Compilation unit analysis terminated
1/14	 PLS-00304: cannot compile body of 'THE_TERMINATOR' without its
	 specification

1/14	 PLS-00905: object MIKE.THE_TERMINATOR is invalid
SQL> 

There is another SQL*Plus setting that you might consider to at least reduce some of the pain caused by this tangle of terminators.
Remember, the terminator is a directive for SQL*Plus to run what’s in it’s buffer. By default this is the last SQL or PL/SQL statement. SQL*Plus commands are not held in the buffer.
The CLEAR BUFFER command will, well, clear the contents of the buffer…

SQL> select 'I really must watch the movies again'
  2  from dual
  3  /

'IREALLYMUSTWATCHTHEMOVIESAGAIN'
------------------------------------
I really must watch the movies again

SQL> list
  1  select 'I really must watch the movies again'
  2* from dual
SQL> clear buffer
buffer cleared
SQL> list
SP2-0223: No lines in SQL buffer.
SQL> 

So, run a statement and we can then see it’s in the buffer by issuing the list command ( usually abbreviated to “l”).
After the CLEAR BUFFER, there’s nothing to see.

Conclusion

SQL*Plus facilitates the use of two terminator characters which behave in the same way…most of the time.
Whilst it’s possible to disable, or even change the “;”, it may not be practical to go forward using just the “/” as a standard.

Deb has just “asked” me to add a disclaimer to this post.
A certain amount of artistic licence was used in the first couple of paragraphs.
After all, there’s so much junk in the cupboard under the stairs that I’d never fit in.
The Shed, Deb assures me, is an entirely different matter.

Advertisements

One thought on “SQL*Plus Terminator Torture

Leave a Reply

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