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

Tanel Poder


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> @getplusparm linesize myvar
SQL> def myvar
DEFINE MYVAR           = "80" (CHAR)

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> @getplusparm sqlcode err
SQL> prompt Error code &err
Error code 942

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
save file &_tmpfile..set replace


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! ;)

  1. Check out my 2022 online training classes in a new format!
    Advanced Oracle SQL Tuning training. Advanced Oracle Troubleshooting training, Linux Performance & Troubleshooting training: Learn when it's convenient to you and get your training questions answered even months after taking the class!
  2. Get weekly updates by email or follow Social/RSS