SQL Plus Formatting

- Using rlwrap tool for Command Line History and Editing in SQL*Plus and RMAN on Linux

- SQL PLUS format 
# rpm -Uvh http://linuxdownload.adobe.com/linux/x86_64//flash-player-npapi-27.0.0.130-release.x86_64.rpm
# rpm -Uvh http://dl.fedoraproject.org/pub/epel/7/x86_64/r/rlwrap-0.42-1.el7.x86_64.rpm
$ echo "alias sqlplus='rlwrap sqlplus' " >> /home/oracle/.bash_profile

$ echo "alias rman='rlwrap rman' " >> /home/oracle/.bash_profile

$ vi $ORACLE_HOME/sqlplus/admin/glogin.sql
SET SERVEROUTPUT ON
SET HEADING ON
SET ECHO ON
SET PAGESIZE 50000
SET PAGES 0
SET LINESIZE 80
DEFINE _EDITOR = gedit
SET COLSEP "|"
SET CONCAT "."
SET CONCAT "!"
SET NEWPAGE NONE
SET TAB OFF
SET NULL **NULL**

RHEL/CentOS/OEL 6 32 bit 

http://dl.fedoraproject.org/pub/epel/6/i386/rlwrap-0.41-1.el6.i686.rpm

RHEL/CentOS/OEL 6  64 bit
http://dl.fedoraproject.org/pub/epel/6/x86_64/rlwrap-0.41-1.el6.x86_64.rpm

RHEL/CentOS/OEL 5 32 bit 
# rpm -Uvh http://dl.fedoraproject.org/pub/epel/5/i386/rlwrap-0.41-1.el5.i386.rpm

RHEL/CentOS/OEL 5  64 bit
# rpm -Uvh http://dl.fedoraproject.org/pub/epel/5/x86_64/rlwrap-0.41-1.el5.x86_64.rpm

# su - oracle
$ vim .bash_profile
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
$ source .bash_profile

very helpful and handy tool

- SQL Plus Environment setting :

SQL> show all;

SQL> show wrapwrap : lines will be wrapped

SQL> set wrap off

SQL> show wrapwrap : lines will be truncated

SQL> set wrap on

SQL> show wrapwrap : lines will be wrapped

SQL> set wrap off
SQL> set linesize 132

SQL> set wrap off linesize 132

sqlplus scott/tiger
set feedback off
set markup html on spool on
alter session set nls_date_format='mm-dd-yyyy';
spool objects.xls
select object_name,object_type,created from user_objects ;
spool off
set markup html off spool off


SQL> set feedback off
SQL> set markup html on spool on
SQL> alter session set nls_date_format='mm-dd-yyyy'
<br>
2 ;
<br>
SQL&gt; spool objects.xls
<br>
SQL&gt; select object_name,object_type,created from user_objects ;
<br>
<p>
<table border='1' width='90%' align='center' summary='Script output'>
<tr>
<th scope="col">
OBJECT_NAME
</th>
<th scope="col">
OBJECT_TYPE
</th>
<th scope="col">
CREATED
</th>
</tr>
<tr>
<td>
PROCEMP1
.. .. ..
<p>
SQL&gt; spool off
<br>
SQL&gt; set markup html off spool off
<br>
<br>
SQL>
Open the objects.xls file in excel. The output will be as below.

SET NEWPAGE NONE
SET PAGESIZE 0
SET SPACE 0
SET LINESIZE 16000
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET TERMOUT OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET COLSEP |

spool &1..txt

@@&1

spool off
exit