How to skip blank lines in between SQL statements in SQL*Plus?

If a SQL script file has blank lines in between SQL statements one may see errors when executing the script in SQL*Plus so if one would like to ignore the blanklines in SQL*Plus one can ignore them by set blanklines on.

Example:
SQL> select object_name
2
SQL> from dba_objects;
SP2-0734: unknown command beginning “from dba_o…” – rest of line ignored.
SQL> set sqlblanklines on
SQL> select object_name
2
3 from dba_objects
4 where rownum < 3;

OBJECT_NAME
——————————————————————————–
ICOL$
I_USER1
– Disable blank lines in between SQL statements
SQL> set sqlblanklines off

Substitution Variable Examples

http://www.oracle.com/technology/support/tech/sql_plus/htdocs/sub_var9.html

How to include instance name in spool filename?

Using the example below one can include instance name in the spool filename when using SQL*Plus

SQL> set termout off
SQL> set feedback off
SQL> undefine curdate dcol
SQL> column dcol new_value curdate noprint
SQL> column dbcol new_value db noprint

SQL> select sys_context(#&39;userenv#&39;,#&39;db_name#&39;) dbcol, to_char(sysdate,#&39;YYYYMMDD_HH24MISS#&39;) dcol from dual;

SQL> set termout on
SQL> set feedback on

SQL> spool &db._&curdate._spool.txt

…
SQL> spool off

Clean up script with oratab

A simple script to delete trace files and arc logs from remote location, based on oratab instances.

#!/bin/ksh

export PATH=/usr/bin:$PATH
export USERNAME=`whoami`
export HOSTNAME=`hostname`

########################################
#  Copy TNS Names file to local disk   #
########################################
/bin/ksh /work/dba/monora/scripts/update_local_tns.ksh > /tmp/update_local_tns.log 2>&1
#
#      Clean up Oracle Export logs
#
find /work/dba/logs -name ‘*${HOSTNAME}_*’ -mtime +7 -exec rm {} \;
find /tmp -name ‘*${HOSTNAME}_*’ -mtime +7 -exec rm {} \;
find /work/dba/prod/backups -name ‘tmp_ora_${HOSTNAME}_*’ -mtime +3 -exec rm -r {} \;

for SID in `cat /etc/oratab|egrep ‘:N|:Y’|grep -v \*|cut -f1 -d’:'|egrep -v  ‘LISTENER|AGENT’`
do
find /oracle/EPW/${SID}/admin/udump -name ‘*.trc’ -mtime +13 -exec rm {} \;
find /oracle/EPW/${SID}/admin/cdump -name ‘core*’ -mtime +13 -exec rm -r {} \;
find /oracle/EPW/${SID}/admin/bdump -name ‘*.trc’ -mtime +13 -exec rm {} \;
find /oracle/EPW/${SID}/admin/adump -name ‘*.aud’ -mtime +13 -exec rm {} \;

## To delete ach logs from remote online location for dev/test/training env that are in arch log mode
find /oracle/EPW/arch/${SID} -name ‘${SID}*.arc’ -mtime +3 -exec rm {} \;
done

sqlplus: error while loading shared libraries: /u01/app/oracle/product/11.1.0/db_1/lib/libnnz11.so: cannot restore segment prot after reloc: Permission denied

Immediately after a successful RDBMS installation (perhaps even including a sample DB instance creation), sqlplus will not start:

[oracle@test ~]$ sqlplus ‘/as sysdba’
sqlplus: error while loading shared libraries: /u01/app/oracle/product/11.1.0/db_1/lib/libnnz11.so: cannot restore segment prot after reloc: Permission denied

The reason is that SELinux is running in “enforcing” mode.

You can check it on file /etc/pam.d/login

Oracle development has recommended the following workaround while they correct the problem:

Switch SELinux from the default “Enforcing” mode that it is running in, to the “Permissive” mode.

Commands, as root:
======================
getenforce       (returns “Enforcing”)
setenforce 0
getenforce       (returns “Permissive”)

This allows SELinux to continue running, and logging denial messages, but SELinux will not actually deny any operations. Once Development has resolved this issue, you can (and should) return SELinux to the default “Enforcing” mode as follows:

Commands, as root:
======================
setenforce 1
getenforce       (returns “Enforcing”)

How to enable command line history scrolling on SQL*Plus on unix?

Using the utility rlwrap one can perform command line scrolling on SQL*Plus similar to that is available on windows version of SQL*Plus. The source code for it can be downloaded from the following site: http://utopia.knoware.nl/~hlub/uck/rlwrap/. To build rlwrap executable needs a library readline is needed which can be downloaded from http://directory.fsf.org/project/readline/.

One of the requirements to build this to have a C compiler like gcc.

To build the readline library after it’s downloaded and untar.
# directory where the source code was untarred, you will need to rename the directory to “readline” as the rlwrap looks for the header file readline.h in a readline folder
$ cd /u01/code/readline
# This step builds the makefile and it also checks if you have the necessary tools (gcc) and inspects your current env. It logs the information in config.log
$ ./configure
# step to build the library libreadline.a
$ make -f Makefile
# verify the library is built
$ ls -l libreadline.a
-rw-r–r– 1 user dba 1732206 Dec 12 17:28 libreadline.a

# step to build rlwrap
$ cd /u01/code/rlwrap-0.30
# set CPPFLAGS which is used to set for C parser, if not set it will not will be able to find the headerfile readline.h which is used to find the version
$ export CPPFLAGS=-I/u01/code/readline
# This step builds the makefile and it also checks if you have the necessary tools (gcc) and inspects your current env. It logs the information in config.log
$ ./configure CFLAGS=-I/u01/code LDFLAGS=-L/u01/code/readline
# this step will build the rlwrap executable, if no errors then it will build the rlwarp in the src directory
$ make

Then you can copy it this executable to /usr/local/bin and add this directory the PATH env so you can use it.

Example: $ rlwrap sqlplus
Enter user-name: username
Enter password: *******

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL>

How to pass host, port, SID/Service name to connect to a database?

Using the following command one can pass hostname, port, servicename and SID and connect to a database without having an entry in tnsnames.ora

$ sqlplus username/password@hostname:port/SERVICENAME

OR

$ sqlplus username
Enter password: password@//hostname:port/SERVICENAME

OR

$ sqlplus /nolog
SQL> connect username/password@hostname:port/SERVICENAME

Set default number of bytes to display LONG and CLOB

To change default number of bytes to be displayed when selecting LONG and CLOB columns.
SQL> SET LONG 2000
SQL> SELECT long_column, clob_column FROM table_name;

How to terminate and rollback a SQL script when running in SQL*Plus when an error is encountered?

Adding the following line before running a SQL Script will terminate the SQL script from running and rollback the change when an error is encountered.

WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;

Example:
SQL> select * FROM test;

no rows selected

SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;
SQL> insert into test values ( 1 );

1 row created.

– error in the script it terminates the session
SQL> insert into test ( ‘aaaa’);
insert into test ( ‘aaaa’)
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

– upon error the session terminates upon encountering an error
Disconnected from Oracle Database ……

– check if the insert was committed
$ sqlplus user/password

SQL*Plus: Release 10.2.0.3.0 – Production on Tue Sep 2 11:44:08 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
– shows the insert was rolled-back
SQL> select * FROM test;

no rows selected

The other options available for WHENEVER SQLERROR are:
WHENEVER SQLERROR
{EXIT [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable]
[COMMIT|ROLLBACK]|CONTINUE [COMMIT|ROLLBACK|NONE]}

How to run SQL statements in SQL*Plus if you have ‘&’ as part of your data?

If you have ‘&’ as part of your data that is part of the string and needs to inserted/updated/deleted and you are using SQL*Plus, you would need to do the following as ‘&’ has a special meaning in SQL*Plus as by default SQL*Plus considers it as a variable.

E.g: SQL> select ‘xxxx &abc’ from dual;
In the example SQL*Plus will prompt to enter a value for variable abc

So to ensure ‘&’ is taken in it’s literal value and it doesn’t prompt, one can do one of the following if you need to run SQL scripts from SQL*Plus

1) SET DEFINE OFF — Turns off substitution of variables
2) SET DEFINE # — Change the substitution character to # or to a character that is not part of the script
3) Change the above sample SQL to the following
SQL> select ‘xxxx &’ || ‘abc’ from dual;