12c Release 2, set feedback enhancement in SQL PLus

There’s a nice little touch to the “set feedback” command in SQL PLus in 12.2. There is a new “only” clause in the SET FEEDBACK command, so you can run queries but the resulting rows are not shown in the display. They are still queried, fetched and “returned”, but just rendered to the screen. Here’s an example

$ sqlplus hr/hr

SQL*Plus: Release Production on Tue Mar 14 22:59:15 2017

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

Last Successful login time: Sat Mar 11 2017 01:59:20 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release - 64bit Production

SQL> select * from regions;

---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

So that’s normal behaviour. Let’s now use the new ONLY option.

SQL> set feedback only 
SQL> select * from regions;

4 rows selected.

So why would you want that ? Well, sometimes you just want to run the query so that you can use a subsequent DBMS_XPLAN.DISPLAY_CURSOR call to see the true execution plan. Or perhaps, you just to want to examine some options with regard to the fetch performance. For example, here’s a demo of fetching from a large table called EMP

SQL> show arraysize
arraysize 10

SQL> set timing on
SQL> select * from emp;

1753088 rows selected.

Elapsed: 00:00:26.27

So that took 26 seconds, with an arraysize of 10. Let’s see if we can do better than that – we’ll bump up the arraysize to 200

SQL> set arraysize 200
SQL> select * from emp;

1753088 rows selected.

Elapsed: 00:00:04.65

Wow, thats pretty cool. Six times faster just by tweaking the batch size of the fetch. Surely then we can just keep bumping it up.

SQL> set arraysize 5000
SQL> select * from emp;

1753088 rows selected.

Elapsed: 00:00:04.43

Apparently not. There is a “sweet spot” for arraysize, and diminishing returns on performance when you go higher and higher (at the cost of consuming memory and resources on your client machine to drag all those rows down at once). But this post isn’t about arraysize, it’s merely a conduit for the nice cool feature SET FEEDBACK ONLY which lets us now test out such things without having to see all the rows presented back.

Are your SQL Plus scripts going to ‘ell ?

Excuse the hideous pun in the title.  All will become clear shortly.

Often we use SQL Plus to run scripts on a scheduled basis in our database.  The “problem” is, they need to connect to that database.

Here’s a simple script I want to run:


and here’s my very sophisticated Smile batch file (which could just as easily be a Unix shell script) to run it:


In my case, I’m planning on using a wallet to not reveal the password (see here for details ).

But what if my wallet is wrong or missing?  Or if I’m explicitly coding a username / password and either are incorrect ?

Then this happens when I run my batch file:


Yes my connection failed…but notice that the script did not complete.


That’s no good…from an observer’s perspective, it is still “running”.

So here’s a quick tip.  Whenever you launch SQL Plus from within a shell or script, always include the -L parameter (“minus elle”)


If you can’t connect, SQL Plus will exit and at least you’ll get some feedback that all is not OK.

So use minus elle, to stop your scripts from going to hell Smile

Amped on Amper

This “problem” rates in the top 10 all time viewed questions on AskTom, and it demonstrates the importance of reading the Concepts guide with Oracle

SQL> create table CARTOONS ( name varchar2(30));

Table created.

SQL> insert into CARTOONS values ('Tom & Jerry');
Enter value for jerry:

And the question comes in: “How can I insert an ampersand into my table?”

And the correct response is – the database treats ampersand exactly the same as any other character.  Which of course, just confuses the heck out of people, because they are staring at their screen, being asked for a value for “Jerry” and it’s obvious that ampersands are not the same as any character.

So what’s going on here ?  There is an excellent section in the Concepts Guide which talks about the way we actually communicate to the database, and that is via a client program.

The client program passes our requests to the database, and awaits it’s response.  The client program is not the database, it is a means of communicating to the database.  And whatever client program you are using, it may have features designed to assist you (which in fact may just be confusing you).

Whether it be SQL Plus, or SQL Developer, or TOAD, or (insert any tool here), there’s a good chance it has a facility to allow the end user to provide input during the execution of a SQL statement.  (More accurately, its asking you for input before it sends the statement for execution).  And for many tools, the ampersand is the special character that indicates “pause for input”.

So in the SQL Plus example above, it’s just a case of telling the client tool that the ampersand is not to be used for this purpose:

SQL> set define off
SQL> insert into CARTOONS values ('Tom & Jerry');

1 row created.

SQL> set define &

Just remember. This has nothing to do with the database.

SQL*Plus quick tip

SQL Plus does not appear to be bothered by the direction of slashes, ie “/” or “\” in its scripts

Hence as long as you have a “C:\tmp” folder on your Windows drive (or wherever your SQL Pus binaary is installed/running from), you can do:

spool /tmp/blah

and it will work on Unix and Windows without alteration.

(This also works for SQLcl as well by the way)

SQL*Plus hints and tips

I just posted a video on how I get the most out of my SQL*Plus usage.  You can check it out here

I reference various elements of my login.sql file throughout the video – so here’s login.sql file – feel free to utilise any parts of it for your benefit.

col column_name     format a30
col sql_text        format a64
col segment_name    format a30 trunc
col tablespace_name format a20 trunc
col program         format a30 trunc
col what            format a50 word_wrapped
col plan_plus_exp   format a100

set lines      130
set pages      99
set trimspool  on
set exitcommit OFF
set arraysize  100
REM set serverout on

set termout off

define gname = 'SQL'
column global_name new_value gname

define IS12 = ""
define NOT12 = ""
col x new_value IS12
col y new_value NOT12

with rel as 
( select substr(regexp_replace(banner,'^.*Release '),1,2) rel
  from v$version
  where rownum = 1 )
  case when rel = '12' then '--' end y,
  case when rel != '12' then '--' end x
from rel;

set verify off

select lower(user) || '@'||
&&IS12 case when sys_context('USERENV','CON_NAME') != 'CDB$ROOT' then lower(sys_context('USERENV','CON_NAME'))
&&IS12 else
&&IS12 end
chr(10)||'SQL' global_name
from v$instance;

set sqlprompt '&&gname> '
host title &&gname

set verify on
set termout on

col profile     format a30
col object_name format a30
col table_name  format a30
col view_name   format a30
col username    format a30
col user_name   format a30

exitcommit … or your career down the drain

I recently posted a little video on the ‘exitcommit’ option in SQL Plus, adding a theatrical drama to present a worst case scenario of potentially destroying a Production system.  Take 3 mins to have a chuckle here.


My predecessor Natalka Roshak tweeted an interesting followup that I thought I would share, namely, if you’re a regular SQL Plus user, you should definitely take the time to check out the documentation on ‘exitcommit’ and its relationship to other related variables within SQL Plus.

Because you probably want to make sure you have got everything set just right !



Avoiding the COMMIT bomb!

I’m an Oracle dinosaur, so I like using SQL Plus.  Its simple, fast, comes with every version and installation and platform, and I’m very familiar with it.  (And who knows, it might still be at the forefront of the Oracle development teams!  http://www.slideshare.net/hillbillyToad/sqlcl-overview-a-new-command-line-interface-for-oracle-database )

But there is one important thing I always take care of when I’m using SQL Plus, and it’s easiest to explain with an example.

You start off by wanting to delete a couple of rows from a critical table as part of a patching process.  You type this:


Uh oh….. I forgot the WHERE clause.  That’s not so good…

Now, if I keep my cool, I can let that command finish and then type rollback, and the damage is limited to potentially blocking other users for a little while.

But of course, what normally happens, is that you see your command, your jaw drops to the floor, and you grab your mouse, race up to the top left or right corner and click to close that window


Phew !  Crisis averted – your statement never finished, so Oracle will happily clean up after you and roll all the deletions back.

But that is a very  very risky thing to do … Because what if JUST WHEN YOU CLICK, the delete completes, and the screen looks like this:


Well… by default, when you exit SQL Plus, it will commit any outstanding changes.  And since your delete has completed… you just wiped out your table, and you’re off to LinkedIn looking for a new place to work 😦

There is a better way.  In your login.sql file for SQL Plus, make sure you always have the following:

set exitcommit off

You never, ever want to be committing (with any tool) unless you explicitly request it.