Sqlplus is my second home, part 4: Getting sqlplus parameter value into a variable

Tanel Poder

2007/11/06

I’m having some busy times, so can’t blog anything more serious than another sqlplus trick (which likely has value only to some hardcore sqlplus geeks though).

Ever wanted to load a sqlplus parameter (like linesize, pagesize or arraysize) into a sqlplus define variable?

This can sometimes be helpful for customizing your everyday DBA scripts to output (or not output) some columns based on linesize. Or you may want to use the SQL error code somewhere in your script.

We’ll it’s doable with the getplusparm.sql script:

SQL> def myvar
SP2-0135: symbol myvar is UNDEFINED
SQL>
SQL> @getplusparm linesize myvar
SQL>
SQL> def myvar
DEFINE MYVAR           = "80" (CHAR)
SQL>

The above example reads sqlplus parameter “linesize” to a define “myvar”.

The example below uses the sqlcode sqlplus parameter to capture the error code of last command sent to database ( 0 if success ):

SQL> drop table xyz;
drop table xyz
           *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL>
SQL> @getplusparm sqlcode err
SQL>
SQL> prompt Error code &err
Error code 942
SQL>

Note that the script creates few temporary files into local directory, you may want to modify the file locations from default for your use (and include an instance┬áname or some variable to avoid race conditions when running multiple sqlplus’es concurrently).

And this is the script text… if you find a way for doing it simpler in sqlplus, let me know :)

set termout off

def _tmpfile=getplusparm

spool &_tmpfile..tmp
show &1
spool off

spool &_tmpfile..chg
prompt c/&1/def &2/
prompt c/&2 /&2=/
spool off

get &_tmpfile..tmp nolist
@&_tmpfile..chg
save file &_tmpfile..set replace

@&_tmpfile..set

set termout on

While this is probably my most useless post written during my short blogging career, I was satisfied just to find out that such thing is doable. So, all sqlplus geeks out there, enjoy! ;)