SQL Plus

SQL*Plus is the [[Oracle Database]]'s supplied interactive SQL client. In later versions of Oracle, for interactive SQL queries, SQL Developer has a better GUI. However, to run scripts and run SQL as part of batch scripts, SQL Plus is your man.

==Setting Up SQL Plus==

SQL Plus has set of options that can be used to tailor the output. You can set these up to allow you to display things on screen or to create batch files.

===Create a file with only data===

To remove all headings, feedback etc. from the output. If you have just started SQL Plus:

<pre>
set pages 0
set feedback off
</pre>

===Writing out "LONGs"===

To increase the length of a LONG column displayed in SQL Plus:

<pre>
set long 55000
set arrays 1
set maxdata 60000
</pre>

===Changing the Prompt===

To put the database name into the prompt in SQL Plus (prior to Oracle 10g):

<pre>
column global_name new_value gname

select name || '> ' global_name from v$database;

set sqlprompt '&gname'
</pre>

This is made a little simpler in 10g and higher:

<pre>
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "
</pre>

===Editing Your SQL===
Use your favourite text editor to edit files - for instance - TextPad - by altering the SQL Plus system variable:

<pre>
define _editor=C:\Progra~1\TextPa~1\Textpad.exe
</pre>

===Show Output from PL/SQL===
To see output from PL/SQL (and see as much as you can):

<pre>
/* Pre 10gR2 client */
set serveroutput on size 1000000
</pre>

<pre>
/* Post 10gR2 client */
set serveroutput on size unlimited
</pre>

===Set a Spool Filename with Database Name and Timestamp===

Set a SQL*Plus column by a database query and use this as part of the name of the file:

<pre>
column colFileSuffix new_value FileSuffix

select name || '_' || to_char(sysdate,'YYYYMMDD_HH24MISS') colFileSuffix
from v$database;

spool ReportFile_&&FileSuffix
...commands...
spool off
</pre>