New Enhancements in SQL* PLUS

SQL* PLUS is a tool to edit the SQL command and format the SQL output. Here is the new enhancement in SQL*PLUS for oracle10g.

Enhancement 1
Prior to oracle10g if we describe the object that is invalid, the describe command will fail with error. But in oracle10g, DESCRIBE will try to validate the object first, and if the object is still invalid after validation, it gives the error message. If the validation is successful, then DESCRIBE command will also be successful.
Let us test this in oracle 10g & oracle9i and see the difference....

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

scott@orcl> create or replace procedure testproc is
2 v_cnt number;
3 begin
4 select count(*) into v_cnt
5 from temptable;
6 end;
7 /

Procedure created.

scott@orcl> desc testproc;
PROCEDURE testproc
scott@orcl> drop table temptable;

Table dropped.
scott@orcl> select status from user_objects
2 where object_name='TESTPROC';

STATUS
-------
INVALID
scott@orcl> create table temptable(no number);

Table created.

scott@orcl> select status from user_objects
2 where object_name='TESTPROC';

STATUS
-------
INVALID

scott@orcl> desc testproc;
PROCEDURE testproc


scott@orcl> select status from user_objects
2 where object_name='TESTPROC';

STATUS
-------
VALID

scott@orcl>

The above case, the procedure is recompiled when we describe the procedure...

Let us test the same scenario in oracle9i...
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> create or replace procedure testproc is
2 v_cnt number;
3 begin
4 select count(*) into v_cnt
5 from temptable;
6 end;
7 /

Procedure created.

SQL>
SQL> desc testproc;
PROCEDURE testproc

SQL> drop table temptable;

Table dropped.

SQL>
SQL> select status from user_objects
2 where object_name='TESTPROC';

STATUS
-------
INVALID

SQL> create table temptable(no number);

Table created.

SQL>
SQL> select status from user_objects
2 where object_name='TESTPROC';

STATUS
-------
INVALID

SQL> desc testproc;
ERROR:
ORA-24372: invalid object for describe


SQL> select status from user_objects
2 where object_name='TESTPROC';

STATUS
-------
INVALID

SQL> alter procedure testproc compile;

Procedure altered.

SQL> desc testproc
PROCEDURE testproc

SQL>
The above case, the procedure is not recompiled when we describe the procedure...

Enhancement 2

The glogin.sql, login.sql files are profile files used to customize our SQL*Plus environment when we log in SQL*Plus. The glogin.sql file is site profile file and located in $ORACLE_HOME/sqlplus/admin directory. The login.sql file user profile and is executed after the glogin.sql file. Prior to oracle10g, this two files(glogin.sql, login.sql) are executed one after other only when we restart the SQL*Plus. But in oracle10g, these two files are executed one after other for every connect as well as every restart of SQL*Plus.

Let us test this in oracle10g... Our goal is to display username and instance name in the sql prompt. Let me add this below entry in glogin.sql file.

column global_name new_value gname
set termout off
select lower(user) '@'
INSTANCE_NAME global_name
from V$INSTANCE;
set sqlprompt '&gname> '
set termout on

Let us restart the SQL*Plus.

SQL*Plus: Release 10.1.0.2.0 - Production on Sun Mar 29 11:04:41 2009

Copyright (c) 1982, 2004, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
scott@orcl>

Now again we will connect to another user and see SQL prompt is changing...

scott@orcl> connect training/training@orcl
Connected.
training@orcl>

so in Oracle10g, glogin.sql is executing every connect as well as every restart of SQL* Plus.

Let us test the same in oracle9i.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

dwadba@invent> connect sales@invent
Enter password: ************
Connected.
dwadba@invent>

so in Oracle9i, glogin.sql is not executing for every connect. It executes only when we start the SQL*Plus.

Enhancement 3

oracle10g supports white space in filenames when we use commands like SPOOL, SAVE, RUN.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

scott@orcl> spool 'c:/test file.txt';
scott@orcl> select sysdate from dual;

SYSDATE
---------
29-MAR-09

scott@orcl> spool off

In oracle9i, it does not support space for filenames...

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> spool 'c:/test file.txt';
SP2-0333: Illegal spool file name: "'c:/test file.txt'" (bad character: ' ')

Enhancement 4

The spool command stores query result in a file. In oracle10g, SPOOL command includes the APPEND extension to add the contents of the buffer to the end of the file.

Let us test this in oracle10g.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

scott@orcl> spool c:/test.txt
scott@orcl> select sysdate from dual;

SYSDATE
---------
29-MAR-09

scott@orcl> spool off
scott@orcl> spool c:/test.txt append
scott@orcl> select sysdate from dual;

SYSDATE
---------
29-MAR-09

scott@orcl> spool off
scott@orcl>

Let us test the same in oracle9i.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> spool c:/test.txt
SQL> select sysdate from dual;

SYSDATE
---------
30-MAR-09

SQL> spool off
SQL> spool c:/test.txt append
SP2-0333: Illegal spool file name: "c:/test.txt append" (bad character: ' ')