Vbsript to execute a Oracle PL/SQL Procedure

Rem Name of the PROCEDURE
Dim strProc  : strProc ="POPULATE_EMP_DATA"  
Rem Name of the DB SCHEMA
Dim strSchema : strSchema = "bhive_report"


'########## TARGET DB DSN
Dim TargetDSN : TargetDSN  = "devDSN"
Dim TargetDBuser : TargetDBuser = "devUSER"
Dim TargetDBpwd : TargetDBpwd = "devPASS"


Call runPROC(TargetDSN,TargetDBuser,TargetDBpwd,strProc,strSchema)


Rem Sub Routine to Execute Stored Procedure
Rem Generic accepts DSN name, username, password and procedure name.
Sub runPROC(vDSN,vUser,vPass,vProc,vSchema)
On Error Resume Next
Const adCmdStoredProc = 4
Dim strTargetConnection : strTargetConnection = "DSN=" & vDSN & ";uid=" & vUSER & ";pwd=" & vPASS&";"
Set objConn = CreateObject("ADODB.Connection")
objConn.Open strTargetConnection : Rem [ opening Target ORACLE connection ]
Set CmdStoredProc = CreateObject("ADODB.Command")
With CmdStoredProc 
Set .ActiveConnection=objConn
	.CommandText= vSchema & "." & vProc  
	.CommandType=adCmdStoredProc
	Set rsProc = .Execute
	If Err.Number  0 then
		msgbox "[ERROR]" & Err.Description
    Else
		msgbox "Executed Procedure Succesfully - " & .CommandText
	End If
End With
End Sub 

Advertisements