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

Delete Temp Folders using Vbscript

'########################################################
'# Title: Script to Delete Temp Folders in Computer		#
'# Date: 18 December, 2012								#	
'# Author: Saqib Mujtaba,WT								#
'########################################################
Option Explicit
On Error resume Next
Dim objFSO,strWinFolder,WshShell,i,objLogFile
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objLogFile = objFso.OpenTextFile("Delete.Log",2 ,True)
objLogFile.WriteLine now & "[================ LOGGING STARTED ================]"
Set WshShell = CreateObject("WScript.Shell")
Set strWinFolder = objFSO.GetSpecialFolder(0)
Dim arrPath(2)
arrPath(0)="C:Temp"
arrPath(1)=strWinFolder & "Temp"
For i=0 To 1
	Call deleteFolders(CStr(arrPath(i)))
	Call deleteFiles(CStr(arrPath(i)))
Next 

Call DeleteLog(strWinFolder)
Call DeleteDMP(strWinFolder)
Call EnableRecycler(1)
Call deleteFolders("C:RECYCLER")
Call EnableRecycler(0)
Call UnknownProfiles()
Call Diskspace()
objLogFile.WriteLine now & "[================ LOGGING ENDED ================]"
objLogFile.close
Set objFSO = Nothing

MsgBox "PROCESS COMPLETED !! - Thanks"

'##############################|** Function Part **|#################################

'########################################################
'# Title: Sub Routine to delete all the Folders			#
'# Parameter: Folder Name (String)						#	 
'# Author: Saqib Mujtaba								#
'########################################################
Sub deleteFolders(strPathFOLD)
On Error resume Next
Dim objFolder,TEMP_NAME
TEMP_NAME=""
If objFSO.FolderExists(strPathFOLD) Then
	Set objFolder = objFSO.GetFolder(strPathFOLD)
	objLogFile.WriteLine now & " [ DELETING FOLDERS IN " & strPathFOLD & " ]"
		For Each ObjFolder In ObjFolder.SubFolders
			TEMP_NAME=objFolder.Name
			Err.Clear
			objFSO.DeleteFolder objFolder,2
			If Err.Number=0 Then
				objLogFile.WriteLine now & " [ DELETED FOLDER - " & TEMP_NAME & " ]"
			Else
				objLogFile.WriteLine now & " [ FOLDER NOT DELETE - " & TEMP_NAME & " - "& Err.Description & " ]"
			End If
		Next
		TEMP_NAME=""
	Set objFolder=Nothing
Else
	objLogFile.WriteLine now & " [ FOLDER DOES NOT EXIST - " & strPathFOLD & " - "& Err.Description & " ]"
End If
End Sub


'########################################################
'# Title: Sub Routine to delete all the Files 			#
'# Parameter: Folder Name (String)						#	
'# Author: Saqib Mujtaba								#
'########################################################
Sub deleteFiles(strPathFLS)
On Error resume Next
Dim objFolder,objFile,TEMP_NAME
TEMP_NAME=""
If objFSO.FolderExists(strPathFLS) Then
Set objFolder = objFSO.GetFolder(strPathFLS)
objLogFile.WriteLine now & " [ DELETING FILES IN " & strPathFLS & " ]"
	For Each objFile In ObjFolder.Files
		TEMP_NAME=objFile.Name
		Err.Clear
		objFSO.DeleteFile objFile,2
		If Err.Number=0 Then
			objLogFile.WriteLine now & " [ DELETED FILES - " & TEMP_NAME & " ]"
		Else
			objLogFile.WriteLine now & " [ FILE NOT DELETE - " & TEMP_NAME & " - "& Err.Description & " ]"
		End If
    Next
    TEMP_NAME=""
Set objFolder=Nothing
Else
	objLogFile.WriteLine now & " [ FOLDER DOES NOT EXIST - " & strPathFLS & " - "& Err.Description & " ]"
End If 
End Sub


'########################################################
'# Title: Sub Routine to delete all the Files 			#
'# Parameter: Folder Name (String)						#	
'# Author: Saqib Mujtaba								#
'########################################################
Sub DeleteLog(strPathLog)
On Error resume Next
Dim objFolderBase,ObjFolder,objFile,TEMP_NAME
TEMP_NAME=""
If objFSO.FolderExists(strPathLog) Then
	Set objFolderBase = objFSO.GetFolder(strPathLog)
	For Each ObjFolder In objFolderBase.SubFolders
		If Left(LCase(ObjFolder.Name),12) = LCase("$NtUninstall") Then
			TEMP_NAME=objFolder.Name
			Err.Clear
			objFSO.DeleteFolder ObjFolder,2
			If Err.Number=0 Then
				objLogFile.WriteLine now & " [ DELETED FOLDER - " & TEMP_NAME & " ]"
			Else
				objLogFile.WriteLine now & " [ FOLDER NOT DELETE - " & TEMP_NAME & " - "& Err.Description & " ]"
			End If
			TEMP_NAME=""
		End If
	Next

	For Each objFile In objFolderBase.Files
		If Left(LCase(objFile.Name),2)=LCase("KB") And Right(LCase(objFile.name),3)="log" then
	        TEMP_NAME=objFile.Name
	        Err.Clear
	        objFSO.DeleteFile objFile,2
	        If Err.Number=0 Then
				objLogFile.WriteLine now & " [ DELETED FILES - " & TEMP_NAME & " ]"
			Else
				objLogFile.WriteLine now & " [ FILE NOT DELETE - " & TEMP_NAME & " - "& Err.Description & " ]"
			End If
			TEMP_NAME=""
	    ElseIf Left(LCase(objFile.Name),5) = LCase("msxml") And Right(LCase(objFile.name),3)="log" Then
	        TEMP_NAME=objFile.Name
	        Err.Clear
	        objFSO.DeleteFile objFile,2
	        If Err.Number=0 Then
				objLogFile.WriteLine now & " [ DELETED FILES - " & TEMP_NAME & " ]"
			Else
				objLogFile.WriteLine now & " [ FILE NOT DELETE - " & TEMP_NAME & " - "& Err.Description & " ]"
			End If
			TEMP_NAME=""
	    ElseIf Left(LCase(objFile.Name),5) = LCase("Setup") And Right(LCase(objFile.name),3)="tmp" Then
	    	TEMP_NAME=objFile.Name
	    	Err.Clear
	    	objFSO.DeleteFile objFile,2
	    	If Err.Number=0 Then
				objLogFile.WriteLine now & " [ DELETED FILES - " & TEMP_NAME & " ]"
			Else
				objLogFile.WriteLine now & " [ FILE NOT DELETE - " & TEMP_NAME & " - "& Err.Description & " ]"
			End If
			TEMP_NAME=""
	    ElseIf Left(LCase(objFile.Name),3) = LCase("mem") And Right(LCase(objFile.name),3)="dmp" Then
	    	TEMP_NAME=objFile.Name
	    	Err.Clear
	    	objFSO.DeleteFile objFile,2
	    	If Err.Number=0 Then
				objLogFile.WriteLine now & " [ DELETED FILES - " & TEMP_NAME & " ]"
			Else
				objLogFile.WriteLine now & " [ FILE NOT DELETE - " & TEMP_NAME & " - "& Err.Description & " ]"
			End If
			TEMP_NAME=""
	    End If 
    Next
    Set objFolderBase=Nothing
    Else
		objLogFile.WriteLine now & " [ FOLDER DOES NOT EXIST - " & strPathLog & " - " & Err.Description & " ]"
	End If
End Sub


'########################################################
'# Title: Sub Routine to delete all the dmp Files		#
'# Parameter: Folder Name (String)						#	
'# Author: Saqib Mujtaba,WT - Automation Engineering	#
'########################################################
Sub DeleteDMP(strPathDMP)
On Error resume Next
Dim objFile,objFolderBase,TEMP_NAME
strPathDMP = strPathDMP & "PCHealthERRORREP"
If objFSO.FolderExists(strPathDMP) Then
	Set objFolderBase = objFSO.GetFolder(strPathDMP)
	For Each objFile In objFolderBase.Files
		TEMP_NAME=objFile.Name
		Err.Clear
		If Right(LCase(objFile.name),3)="dmp" Then
	    	objFSO.DeleteFile objFile,2
	        If Err.Number=0 Then
				objLogFile.WriteLine now & " [ DELETED FILES - " & TEMP_NAME & " ]"
			Else
				objLogFile.WriteLine now & " [ FILE NOT DELETE - " & TEMP_NAME & " - " & Err.Description & " ]"
			End If
		End If
		TEMP_NAME=""
    Next
    Set objFolderBase=Nothing
    Else
		objLogFile.WriteLine now & " [ FOLDER DOES NOT EXIST - " & strPathDMP & " - " & Err.Description & " ]"
	End If
End Sub



'########################################################
'# Title: Sub Routine to Enable/Disable C:/Recycler 	#
'# Parameter: boolValue(Boolean)						#	
'# Author: Saqib Mujtaba								#
'########################################################
Sub EnableRecycler(boolValue)
On Error resume Next
Dim strComputer, objReg, strKeyPath, ValueName, strValue 
Err.Clear
Const HKEY_CURRENT_USER = &H80000001
strComputer = "."
Set objReg = GetObject("winmgmts:\" & strComputer & "rootdefault:StdRegProv")
strKeyPath = "SoftwareMicrosoftWindowsCurrentVersionExplorerAdvanced"
objReg.CreateKey HKEY_CURRENT_USER, strKeyPath
ValueName = "SuperHidden"
objReg.SetDwordValue HKEY_CURRENT_USER, strKeyPath, ValueName, boolValue
	If Err.Number=0 And boolValue=0 Then
		objLogFile.WriteLine now & " [ RECYCLER ENABLED ]"
	ElseIf Err.Number=0 And boolvalue=1 Then
		objLogFile.WriteLine now & " [ RECYCLER DISABLED - " & Err.Description & " ]"
	End If
Set objReg = Nothing 
End Sub


'########################################################
'# Title: SubRoutine to find Unknown Profiles & Delete	#
'# Parameter: Null										#	 
'# Author: Saqib Mujtaba, WT - Automation Engineering	#
'########################################################
Sub UnknownProfiles()
On Error resume Next
Dim strhive,strComputer,objWbem,objRegistry,strKeyP,lngRtn,arrRegKeys,strKey,objSID 
strComputer="."
Const HKEY_USERS = &H80000003
Const HKEY_LOCAL_MACHINE = &H80000002
Set objWbem = GetObject("winmgmts:")
Set objRegistry = GetObject("winmgmts://" & strComputer & "/root/default:StdRegProv")
strhive=HKEY_LOCAL_MACHINE
strKeyP="SOFTWAREMicrosoftWindows NTCurrentVersionProfileList"
lngRtn = objRegistry.EnumKey(strhive,strKeyP, arrRegKeys)    
For Each strKey In arrRegKeys
   If UCase(strKey) = ".DEFAULT" Or UCase(Right(strKey, 8)) = "_CLASSES" Then
   Else
      Set objSID = objWbem.Get("Win32_SID.SID='" & strKey & "'")
      If TRIM(objSID.AccountName)="" Then
			objLogFile.WriteLine now & " [ UNKNOWN PROFILE FOUND ]"
			strKeyP = strKeyP &""& strKey
			Err.Clear
			Call DeleteRegistryKey(strhive,strKeyP)
			If Err.Number=0 Then
				objLogFile.WriteLine now & " [ UNKNOWN PROFILE DELETED - KEY: " & strKey & " ]"
			Else
				objLogFile.WriteLine now & " [ UNKNOWN PROFILE NOT DELETED - KEY: " & strKey & " - "& Err.Description & " ]"
			End If
		End If     
   End If
Next
Set objSID = Nothing
Set objWbem = Nothing
Set objRegistry = Nothing
End Sub



'########################################################
'# Title: Sub Routine to delete registry key			#
'# Parameter: Folder Name (String)						#	 
'# Author: Saqib Mujtaba, WT - Automation Engineering	#
'########################################################
Sub DeleteRegistryKey(ByVal sHive, ByVal sKey)
Dim aSubKeys, sSubKey, iRC, oRegR, strComputer
strComputer = "." 
Set oRegR = GetObject("winmgmts:{impersonationLevel=impersonate}!\" & strComputer & "rootdefault:StdRegProv")
On Error Resume Next
iRC = oRegR.EnumKey(sHive, sKey, aSubKeys)
	If iRC = 0 And IsArray(aSubKeys) Then
		For Each sSubKey In aSubKeys
			If Err <> 0 Then
				Err.Clear
				Exit Sub
			End If
			DeleteRegistryKey sHive, sKey & "" & sSubKey
		Next
	End If
oRegR.DeleteKey sHive, sKey
If Err.Number=0 Then
	objLogFile.WriteLine now()&" [ KEY DELETED - " & sHive & ""& sKey & " ]"
Else
	objLogFile.WriteLine now()&" [ KEY NOT DELETED - " & sHive & ""& sKey & " - "& Err.Description &" ]"
End If
Set oRegR= Nothing
End Sub



'########################################################
'# Title: Sub Routine to check disk space			 	#
'# Parameter: Null										#	
'# Author: Saqib Mujtaba,WT							    #
'########################################################

Sub Diskspace()
On Error Resume Next
Dim strFile1, strFile2, objFSO1,   outputFile,strFolder
Dim strComputer, objWMIService,colDisks, objDisk, Temp_Data
Err.Clear
Set objFSO1 = CreateObject("Scripting.FileSystemObject")
strFile2 = "Disk_Spaces.csv"
Set outputFile = objFSO1.OpenTextFile(strFile2,2 ,True)
outputFile.writeline "Drive,Size,Free Space,%age Free"
objLogFile.WriteLine now()&" [ DISK SPACE CALCULATING ]"
	strComputer = "."
	Set objWMIService = GetObject("winmgmts:" & "\" & strComputer & "rootcimv2")	 
	Set colDisks = objWMIService.ExecQuery ("Select * from Win32_LogicalDisk Where DriveType = 3",,48)
	For Each objDisk in colDisks
		Temp_Data=""
	  	Temp_Data =  objDisk.DeviceID & "," & Round(objDisk.Size / 1073741824,2) & " GB," & Round(objDisk.FreeSpace/1073741824,2) & _
	           " GB," & Int( 0.5 + 100 * objDisk.FreeSpace / objDisk.Size)
		outputFile.writeline Temp_Data
		objLogFile.WriteLine now()&" [ DISK SPACE STORED IN - " & strFile2 & " ]"
		Exit for	
	Next
outputFile.Close
Set objFSO1 =Nothing
End Sub

Powered by WordPress.com.

Up ↑