Integrate Os Command With Sqlplus
Integrate Os Command With Sqlplus
Enhance SQLPLUS with OS command
Do you feel we do need OS command just like grep, awk even perl, though the sql related functions of sqlplus are powerful? sqlplus-os integration component - host.sql is the utility addresses these needs.
Highlight
- Under sqlplus prompt environment
- Pipe the output of sqls and commands of sqlplus into OS command input
- Display the output direct to sqlplus prompt
- Almost no harm to the sqlplus environment (except spool , termout , verify, echo - you can adjust yourself)
Example:
-- Original output 01:37:09 SYS(PROD)> desc dual Name Null? Type ----------------------------------------------------- -------- ------------------------------------ DUMMY VARCHAR2(1) -- Using @host2 "desc dual" "grep D" -- Explain: redirect output of "desc dual" into "grep D" input 01:36:54 SYS(PROD)> 01:36:54 SYS(PROD)> @host2 "desc dual" "grep D" 01:37:09 SYS(PROD)> set echo off DUMMY VARCHAR2(1) 01:37:09 SYS(PROD)> -- Using @host2 "desc dual" "head -n 2" -- Explain: redirect output of "desc dual" into "head -n 2" input 01:50:06 SYS(PROD)> 01:50:06 SYS(PROD)> @host2 "desc dual" "head -n 2" 01:50:31 SYS(PROD)> set echo off Name Null? Type ----------------------------------------------------- -------- ------------------------------------ 01:50:31 SYS(PROD)>
host.sql
set echo off set termout off set verify off set time off -- host.sql -- osqlfuse package FUS component -- @author f.yang -- all rights reserved -- http://oracle-abc.wikidot.com -- -- @usage -- from sqlplus -- SQL> @host "sql/sqlplus command" "os command" -- from os shell -- echo @host '"desc dual"' '"grep D"' | sqlplus -S / as sysdba -- -- @example -- from sqlplus -- SQL> @host "desc dual" "grep DUMMY" -- DUMMY VARCHAR2(1) -- from os shell -- echo @host '"desc dual"' '"grep D"' | sqlplus -S / as sysdba -- DUMMY VARCHAR2(1) spool off spool '.osqlfuse.host.cmd.tmp' prompt &1 spool off spool '.osqlfuse.host.out.tmp' @.osqlfuse.host.cmd.tmp spool off host cat .osqlfuse.host.out.tmp | &2 set verify on set termout on set time on set echo on
hosttmp.sql
host cat .osqlfuse.host.out.tmp
Attention:
- Error of command
you can check the error output of middle step SQL/SQLPLUS with @hosttmp
- affection of spool
for the restriction of sqlplus substitution variable functions, I have to use spool function. As the result, the script make the spool setting to off.