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