SQLPlus and multi-line input
Today I was experimenting with the great Sqitch to manage changes to an Oracle database, and I was encountering a lot of problems. It turned out thatsqitch uses sqlplus, the default Oracle client, to execute commands on the Oracle database.
However, I was unable to execute my text scripts, since sqitch was always reporting errors. Therefore I started digging the problem, executing the script directly into sqlplus.
See an example:
% cat ~/tmp/table.sql
create table foo
(
id number primary key
, description varchar2( 10 )
);
% sqlplus -s luca/mysecretPassword@my_oracle:1521/database < ~/tmp/table.sql
...
SQL> 2 3 4 SQL> SP2-0734: unknown command beginning ", descript..." - rest of line ignored.
SQL> SP2-0042: unknown command ")" - rest of line ignored.
Note the totally unrelated error about
unknoqn command. The problem is that sqlplus is unable to handle empty lines in a sane way.
Changing the original file removing the empty line makes it working:
% cat ~/tmp/table.sql
create table foo
(
id number primary key
, description varchar2( 10 )
);
% sqlplus -s luca/mysecretPassword@my_oracle:1521/database < ~/tmp/table.sql
...
SQL> 2 3 4 5
Table created.
After a little research, I found that there is a parameter named
sqlblanklines that is turned off by default, and that denies the presence of blank lines in the SQL input. Setting it to ON allows the script to run fine.
There are two possible solutions:
- set the parameter as first thing in every script (poor practice)
- set the parameter in
~/login.sql.
% cat ~/tmp/table.sql
set sqlblanklines ON;
create table foo
(
id number primary key
, description varchar( 10 )
);
% sqlplus -s luca/mysecretPassword@my_oracle:1521/database < ~/tmp/table.sql
...
SQL> 2 3 4 5
Table created.
The best approach is to create a
login.sql script, that must be contained in a directory pointed by $ORACLE_PATH (do not confuse with $ORACLE_HOME!), for example my script is as follows:
% cat ~/bin/oracle/login.sql
--
-- Custom sqlplus customization script
--
prompt Running LUCA login script
set sqlblanklines ON;
set sqlprompt "_user'@'_connect_identifier >"
Conclusions
As often, I findsqlplus a sub-optimal client for dealing with Oracle.
The fact, however, that a lot of tools expects it to be working to make their magics, and the fact that I don’t fully understand how it works, can cause a lot of impedence!