Infrastructure at your Service

Franck Pachot

The future of SQL*Plus and some oddities

By Franck Pachot

Rumors are coming after OOW14. Oracle is working on a new SQL*Plus version. Not only a new version. It’s a completely new development. Here is what we know currently about the future of sqlplus an sqldeveloper. But I start with some very old stuff.


sqlplus is an old tool. With many features that look as oddities today.

You want to see some oddities? Good idea because it can be error-prone. Without knowing them, an error when commenting out a line can be very problematic:

SQL> create table MY_DUAL ( DUMMY varchar2(1) );
Table created.

SQL> insert into MY_DUAL values ('X');
1 row created.

SQL> /*I've inserted only one row, right?*/
1 row created.

SQL> select count(*) from MY_DUAL;


Yes, my comment without a space after the ‘/*’ executed the previous command again, as does the ‘/’

Another one? Imagine that by mistake you comment a line using the linux comments ‘#’

SQL> # drop table MY_DUAL;
Table dropped.

The command was executed. The ‘#’ executes the command. What is the reason for that? Imagine I’m typing a long pl/sql and I want to run a describe in order to check table columns. I’m in 1985 so I just can’t open a new window.

SQL> declare
  2   cursor c is select * from SCOTT.EMP where
  3  # -- what are the columns of EMP ?
  3  # -- ok let's get them:
  3  # desc SCOTT.EMP
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

  3    mgr is null;
  4  begin
  5   null;
  6  end;
  7  .
SQL> l
  1  declare
  2   cursor c is select * from SCOTT.EMP where
  3    mgr is null;
  4  begin
  5   null;
  6* end;

Great, I was able to run a command without disturbing the statement I was writing. Cool feature in 1985. Risky feature in 2014. Still there for compatibility. Up to you to disable it with:

SQL> set sqlprefix off
SQL> #drop table MY_DUAL;
SP2-0734: unknown command beginning "#drop tabl..." - rest of line ignored.

SQL Developer

SQL Developer was a nice evolution. I use it a lot. I don’t know enough all the possibilities. For example I discovered only very recently that there is a command line access to SQL Developer which has a few features:

Available features:
cart: Database Cart Batch Tasks
dba: Basic Batch DBA Tasks
format: SQL Format Task
migration: Database Migration Tasks
reports: Basic Batch Reporting Tasks
unittest: Unit Testing Batch Tasks

More info about it on jeff Smith blog

SQL Developer 4.1

At Oracle Open World, Jeff Smith (Product Manager of SQL Developer) not only organized the Golden Gate Run, but also unveiled a few SQL developer 4.1 new features. It’s not only a tool for developers, but you can do many DBA or monitoring tasks from it.

His presentation is here

A new SQL*Plus?

If you follow Kris Rice tweets you have probably seen a few teasers about a new sqlplus coming. Yes, they are completely rewriting sqlplus.
Here is an example:

A little more of a preview of what we’re building.

— krisrice (@krisrice) October 31, 2014

What we can expect?

  • Auto completion (proposes table names in the same way as linux bash proposes file names)
  • Statement history in the same way we do with our DMK
  • simple command to get the create statements, such as: ‘DDL EMP’ to show the ‘CREATE TABLE EMP …’ statement

Another teaser screenshot:

How about this one? What else should be there? #orclapex

— krisrice (@krisrice) October 31, 2014

Because, as a consultant, I’m often going to new environments where I have to adapt to the customer tools, I prefer to use the tools that are easily available. sqlplus is always there. sqldeveloper is easy to install (just unzip and it connects through jdbc).SQL Developer is shipped in ORACLE_HOME snce 11g. It’s not the latest version but you will be happy to find it if you’re not allowed to install anything. And I’m not talking only about customer environments, but if you have to take a hands-on certification exam you will also appreciate 😉

A small update:

it’s not a rewrite of sqlplus but rather using the sqldeveloper sqlplus engine.

One Comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Franck Pachot
Franck Pachot

Principal Consultant / Database Evangelist