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  
	Set rsProc = .Execute
	If Err.Number  0 then
		msgbox "[ERROR]" & Err.Description
		msgbox "Executed Procedure Succesfully - " & .CommandText
	End If
End With
End Sub 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s