This is a couple of sub/funcs that I use as the core of scripts to run
SQLPLUS on a scheduled basis. The "Shell Exec" section, of the
SQLExec() function, is actually all that is needed, as long as you never
have problems and don't want descriptive logs :-).
The "Shell Exec" loop needs to get the stdout as it is produced, instead
of just at the end. SQLPLUS (when running as another user) seems to get
stuck if the stdout is not retrieved as it is produced. Additionally,
this script will also run as in the background as another user just fine
(which is useful if you are going to schedule it on a windows machine).
PARAMETERS: Function SQLExec( s_cmd_ln, s_password )
s_cmd_ln - is a string that matches a standard SQLPLUS command line.
s_password - is passed separately in addition to it being passed in
(s_cmd_ln) so that it can be replaced with ***** for logging.
Make sure you use cscript (instead of wscript) so that it runs as a
command line script.
Hope this helps...
Marc Wilson
Lightbridge, Inc.
Communication Technologies
Solution Integration Architect
'===============================================================================
Sub EchoLog( s )
oApp.echo(s)
oLogFile.WriteLine(s)
End Sub
'===============================================================================
Function SQLExec( s_cmd_ln, s_password )
dim o_shell,o_exec
dim i_ret_cd,dt_begin,dt_end,i_elapsed_sec,s_stdout,s_exec_status
'-----------------------------------------------------------------------------
dt_begin = Now()
'------------------------
'-- Exec StdOut Header --
'-----------------------------------------------------------------------------
EchoLog(DivMajor2)
EchoLog("SQLExec() - BEGIN")
EchoLog(DivMajor2)
EchoLog("EXEC_command : " & Replace(s_cmd_ln,s_password,"*****"))
EchoLog(DivMinor2)
EchoLog("EXEC_start : " & dt_begin)
EchoLog(DivMajor3)
EchoLog("EXEC_stdout : BEGIN")
EchoLog(DivMinor3 & vbCRLF)
'-----------------------------------------------------------------------------
'----------------
'-- Shell Exec --
'-----------------------------------------------------------------------------
set o_shell = WScript.CreateObject("WScript.Shell")
s_stdout = ""
set o_exec = o_shell.Exec(s_cmd_ln)
do while (o_exec.Status = 0)
oApp.Sleep 100 ' this is actually the script sleeping, not the
app specified in s_cmd_ln
s_stdout = s_stdout & o_exec.StdOut.ReadAll
loop
s_stdout = s_stdout & o_exec.StdOut.ReadAll
EchoLog(s_stdout)
'-----------------------------------------------------------------------------
'------------------------------
'-- Calulate Elapsed Seconds --
'-----------------------------------------------------------------------------
dt_end = Now()
i_elapsed_sec = Int(((dt_end - dt_begin) * 86400)*1000)/1000
'-----------------------------------------------------------------------------
'-----------------------------
'-- Calulate Process Status --
'-----------------------------------------------------------------------------
i_ret_cd = o_exec.ExitCode
if (i_ret_cd = 0) then
s_exec_status = "SUCCESS"
else s_exec_status = "FAILED"
end if
'-----------------------------------------------------------------------------
'------------------------
'-- Exec StdOut Footer --
'-----------------------------------------------------------------------------
EchoLog(DivMinor3)
EchoLog("EXEC_stdout : END")
EchoLog(DivMajor3)
EchoLog("EXEC_stop : " & dt_end)
EchoLog("EXEC_seconds : " & i_elapsed_sec)
EchoLog(DivMinor2)
EchoLog("EXEC_exit_status : " & s_exec_status)
EchoLog("EXEC_exit_code : " & i_ret_cd)
EchoLog(DivMajor2)
EchoLog("SQLExec() - END")
EchoLog(DivMajor2)
'-----------------------------------------------------------------------------
'---------------------------
'-- Destroy Local Objects --
'-----------------------------------------------------------------------------
set o_exec = nothing
set o_shell = nothing
'-----------------------------------------------------------------------------
SQLExec = i_ret_cd
End Function
'===============================================================================
Here is a snip of what calls SQLExec():
'-------------------------------------------------------------------------------
'-- CCDM - Full Day - ETL & Aggregation
'-------------------------------------------------------------------------------
sSQL_ScriptName = sSQL_ScriptDir & "<your_oracle_sql_script_filename_here>"
iRetCd = SQLExec(sSQL_Program &" "& sSQL_Login &" "& "@"&sSQL_ScriptName
&" "& sSQL_Params,sSQL_UserPassword)
if (iRetCd <> 0) then
call ProcEnd(iRetCd)
end if
'-------------------------------------------------------------------------------
edwinuy wrote:
>Hi all,
>
>Does anyone have sample Perl or VBScript/WScript that I can use for SQLServer and Oracle?
>
>Obviously need to run on Windows, not UNIX ....
>
>
>
>
>--------
>website: http://www.LazyDBA.com
>Please don't reply to RTFM questions
>Oracle documentation is here: http://tahiti.oracle.com
>To unsubscribe: see http://www.lazydba.com/unsubscribe.html
>To subscribe: see http://www.lazydba.com
>By using this list you agree to these terms:http://www.lazydba.com/legal.html
>
>
>
Oracle LazyDBA home page