63

So many times I just need a quick connection to an Oracle DB, where SQLPLUS handles the job.

I imagine when people start using Oracle, the first thing they are told to do is to install Toad or SQLDeveloper. Even so, sometimes you don't want to wait for those tools to load, if you are performing some simple queries.

I have a script that I run when I start my shell so I get a better experience:

SET pagesize 2000
SET LONG 10000
SET linesize 1000
COLUMN last_name format a20
COLUMN total format 999,999,999
SET feedback ON
alter session set nls_date_format = 'yyyy-mm-dd hh:mi:ssPM';

I trimmed my "COLUMN" settings for this example, but basically, that helps the data fit on the screen.

Setting the date format really simplifies dealing with dates.

When the command window opens in Windows, I set the window layout properties so I can scroll, have a wider window, etc. and save the settings for future windows.

Does anybody else use SQL*Plus daily? Any tips?

wolφi
  • 8,091
  • 2
  • 35
  • 64
ericp
  • 611
  • 2
  • 11
  • 17
  • 1
    @ThiefMaster I agree this question does not fit the regular question format. But it didn't raise a debate at all. It's a very helpful and very constructive list of SQLPlus tips and tricks. So why close it? – Bastian Voigt Feb 04 '14 at 15:54
  • Re-closed with a better reason :) It should remain closed. But that does not mean it's deleted! – ThiefMaster Feb 04 '14 at 16:43
  • 4
    this close reason is nonsense too. maybe some opinion is involved in answers but these are all helpful tips on a subject that's hard to find. I don't understand what we are being protected from with these questions that are obviously helpful being closed. I wish there was a rule that questions with 5+ upvotes couldn't be closed – Sonic Soul Oct 28 '15 at 14:29
  • ...or maybe questions marked as favorite, say, 50+ times – zb226 Feb 06 '16 at 15:08
  • The question is clearly not a specific problem one might have and there can be no correct answer or solution to it, thus it doesn't fit the SE Q&A format! A better fit might be the new SO Documentation: http://stackoverflow.com/tour/documentation – Flygenring Sep 14 '16 at 20:07
  • The question ends in "Any tips?". He asks for help to customize sqlplus. This is something all long time users of sqlplus have struggled with from time to time. So this is interesting to most if not all sqlplus users. And as such should not be closed. – Kjetil S. Jul 15 '17 at 00:18

6 Answers6

55

You can use rlwrap to add readline support to sqlplus. Run sqlplus like this:

$ rlwrap -c sqlplus username@database

Now up/down will scroll through command history. Use ctrl-r to search backwards through history, etc. This makes sqlplus bearable.

Also, add this to your login.sql to set the linesize to whatever the width of your terminal is:

HOST echo "set linesize" $(stty -a|head -n1|cut -f7 -d' '|cut -f1 -d';') > .tmp.sql
@.tmp.sql
HOST rm -f .tmp.sql

Both of these tips only work on unix.

Ken Williams
  • 22,756
  • 10
  • 85
  • 147
Dana
  • 1,048
  • 8
  • 10
  • Thanks for the linesize trick. rlwrap I already was using. Both also works on Mac OSX, with the little difference you have to use `cut -f6` instead of `cut -f7` ( or use `HOST echo "set linesize" $(stty -a | head -1 | awk '{print $6}') > .tmp.sql` ) – Johanna Oct 05 '12 at 07:17
  • If you're on a Debian-based distro (Debian/Ubuntu/Mint etc) you can just do `apt-get install rlwrap` (tested on Ubuntu 14.04 and 16.04). – labyrinth May 09 '17 at 15:33
17

Yes, I use SQL Plus every day in preference to Toad or SQL Developer (though I also use SQL Developer to browse the database).

I have the following in my login.sql script (which SQL Plus runs automatically):

1) Replace default editor (Notepad) with one of my choice:

define _editor = "C:\Program Files\TextPad 5\TextPad.exe"

2) Make SQL prompt show database name so I know where I am (thanks to Tom Kyte for this):

COLUMN global_name new_value gname
SET TERMOUT OFF
SELECT LOWER(USER) || '@' || global_name||CHR(10)||'SQL> ' AS global_name
FROM   global_name;
SET SQLPROMPT '&gname'
SET TERMOUT ON

... plus other setting similar to yours.

I also find Tom Kyte's print_table procedure very useful.

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
10

Remember that we can put these settings in the login.sql script which will be run automatically whenever we start SQL*Plus. Find out more.

The neat thing about this is, that since 10g, this script is run every time we connect rather just the first time we fire up SQL*Plus...

SQL> conn apc
Enter password:
Connected.
Running login script

Session altered.

SQL> conn scott
Enter password:
Connected.
Running login script

Session altered.

SQL>
APC
  • 144,005
  • 19
  • 170
  • 281
  • Yes, the automatic running of login.sql after a reconnect is a nice improvement in 10g. But the "clear screen" commit hangs in that version, so for me it's either 9 or 11. – Rob van Wijk Sep 17 '09 at 20:02
  • I have gone straight from 9i to 11g, so I wsn't aware of that particular feature of 10g. – APC Sep 17 '09 at 21:08
  • the word "commit" should be "command" in my first comment – Rob van Wijk Sep 18 '09 at 09:22
8

I use SQL*Plus exclusively to work with Oracle. Other answers already give some very handy login.sql contents.

This is my login.sql. I copied some suggestions from Tom Kyte and William Robertson in there. Maybe you find some things you want to use as well.

set termout off
set serveroutput on size unlimited
set pagesize 50000
set linesize 135
set long 50000
set trimspool on
set tab off
def _editor = "C:\Progra~1\Notepad++\Notepad++.exe"

define gname=idle
column global_name new_value gname
select lower(user) || '@' ||
       substr(global_name,1,decode(dot,0,length(global_name),dot-1)) global_name
from (select global_name,instr(global_name,'.') dot from global_name);
set sqlprompt '&gname> '

alter session set nls_date_format = 'dd-mm-yyyy hh24:mi:ss'
/
var sid number
var serial# number
var tracefile VARCHAR2(200)

DECLARE
    v_audsid v$session.audsid%TYPE;
BEGIN
    SELECT sid, serial#, audsid
    INTO   :sid, :serial#, v_audsid
    FROM   v$session
    WHERE  audsid = SYS_CONTEXT('USERENV','SESSIONID');

    SELECT par.value ||
           CASE WHEN par.value LIKE '%/%' THEN '/' ELSE '\' END ||
           LOWER(th.instance) ||
           '_ora_' || LTRIM(TO_CHAR(pro.spid,'fm99999')) || '.trc' AS filename
    INTO   :tracefile
    FROM   v$process   pro
         , v$session   se
         , v$parameter par
         , v$thread    th
    WHERE  se.audsid = v_audsid
    AND    pro.addr = se.paddr
    AND    par.NAME = 'user_dump_dest';
END;
/

BEGIN
    IF :sid IS NULL THEN
        SELECT sid
        INTO   :sid
        FROM   v$mystat
        WHERE  rownum = 1;
    END IF;
END;
/

set termout on
set feedback off
exec DBMS_OUTPUT.PUT_LINE('Sessie: ' || :sid || CASE WHEN :serial# IS NULL THEN ' (no access to V$ tables)' ELSE ',' || :serial# END)
exec IF :tracefile IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE('Eventueel trace-bestand: ' || :tracefile); END IF
prompt
set feedback on
Alfabravo
  • 7,493
  • 6
  • 46
  • 82
Rob van Wijk
  • 17,555
  • 5
  • 39
  • 55
4

I like to use sqlplus in off-line.

sqlplus -S user/password @query.sql> file.txt

where query.sql is

set feedback off verify off heading off pagesize 0
...here goes a query...
quit;
/

So i can get info from the database in my bat/script files in windows or unix.

Jonathan
  • 11,809
  • 5
  • 57
  • 91
3

I find it is handy to use SQL*Plus column variables within directives - for example, I'm often in a session and want to spool to a new file name to avoid overwriting another log that may already exist and do this (first three statements through an @file):

SQL> column spr new_value spoolref
SQL> select user||'_'||abs(dbms_random.random) spr from dual;
SQL> spool &spoolref
         ... do work here ...
SQL> spool off

I'll then find the new log by sorting by time - you could always use some strategy other than the random number if you prefer.

dpbradley
  • 11,645
  • 31
  • 34