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

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

NB! I am running one more Advanced Oracle Troubleshooting training in 2018! You can attend the live online training and can download personal video recordings too. The Part 1 starts on 29th January 2018 - sign up here!

This entry was posted in Cool stuff, Oracle and tagged . Bookmark the permalink.

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

  1. Purav says:

    Thanks for this one. I was looking exactly for exception handling in sqlplus and sqlcode serves the purpose perfectly.
    Is there a way to similarly capture the error message i.e. sqlerrm ?


  2. Mette Juel says:

    Hi Tanel.

    Good post – Will this be the solution for having output from a host command put into a var as well? Or is there a better solution for this


  3. Christoph says:

    Clever, as usual.

Leave a Reply

Your email address will not be published. Required fields are marked *