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; COUNT(*) ---------- 2
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; SQL>
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 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:
F:\ora\sqldeveloper\sqldeveloper\bin>sdcli 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. http://t.co/Xfx01EexRf
— 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:
— 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.