Featured SQLPLUS Tips and Tricks

Question

How many times do I just need a quick connection to Oracle DB, where SQLPLUS processes the job.

I guess when people start using Oracle, the first thing they are told is to install Toad or SQLDeveloper. However, sometimes you do not want to wait for these tools to load if you are performing simple queries.

I have a script that I run when I run my shell to 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 working with dates.

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

Does SQLPLUS use every day? Any tips?

Answer 1

You can use rlwrap to add readline support for sqlplus. Run sqlplus as follows:

 $ rlwrap -c sqlplus username@database 

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

Also, add this to your login.sql to set the width alignment of your terminal:

 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 work only with unix.

Answer 2

Yes, I use SQL Plus every day, preferring Toad or SQL Developer (although I also use SQL Developer to view the database).

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

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

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

2) Make an SQL query to show the database name so that I know where I am (thanks 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 settings similar to yours.

I also find that the Tom Kyte print_table procedure is very useful.

Answer 3

Remember that we can put these parameters in the login.sql script, which will be launched automatically when SQL * Plus starts. More details

The best thing is that with 10g this script runs every time we connect, and not just the first time we run 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> 

Answer 4

I use SQL * Plus exclusively for working with Oracle. Other answers already provide very convenient login.sql content.

This is my login.sql . I copied some suggestions from Tom Keith and William Robertson. You may find some things you want to use.

 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 

Answer 5

I like to use sqlplus offline.

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

where query.sql

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

So, I can get the information from the database in bat / script files on Windows or Unix.

Answer 6

I consider it appropriate to use SQL * Plus column variables in directives - for example, I often participate in a session and want to bind to a new file name to avoid overwriting another log that may already exist and do this (the first three statements via @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 

Then I will find a new magazine, sorting by time - you can always use some strategy other than random if you want.