Oracle DB user locks VBScript

Question

I have a simple VBScript to test connection to Oracle Database:

Dim strSQL, strDataSource, strUsername, strPassword

Set dbData = CreateObject("ADODB.Recordset")
Set dbConnection = CreateObject("ADODB.Connection")

dbConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source="MyDB";User ID="MyUser";Password="MyPassword";"
dbConnection.Open

strSQL = "SELECT 1 FROM DUAL"
dbData.Open strSQL, dbConnection
If (dbData.EOF) Then
    WScript.Echo "There are No records to retrieve"
Else
    WScript.Echo "There are records to retrieve."
End If

dbData.Close
dbConnection.Close

The script is querying the DUAL table, and if 1 row is retrieved. that's an indicator that the database is online.

Problem is, that sometimes the user account that I'm using with, get locked on the database, and I'm getting the following error output from the script:

ORA-28000: the account is locked

Is there any way to capture this error in VBScript?

Because I want to add another Echo in the script, in case the user is locked.

Did you try the standard VBS error handling? https://stackoverflow.com/questions/157747/vbscript-using-error-handling — Wernfried Domscheit, Apr 01 '19 at 06:59
VBS Error Handling cannot catch exceptions while runtime. And the Global Err Object is good as Errorlevels for batch. Both methods have same problem, where in a situation that you have multiple reasons for failure. You can't specify the one you actually want. — edwio, Apr 01 '19 at 09:12

score 0 · Accepted Answer · answered Apr 01 '19 at 09:35

For me this one works:

Set con = CreateObject("ADODB.Connection")
con.provider = "OraOLEDB.Oracle"

On Error Resume Next
con.Open "Data Source=" & MyDB, "MyUser", "MyPassword"
If Err.Number <> 0 Then 
    WScript.Echo "Error: " & Err.Description
    WScript.Quit
End If
Err.Clear
On Error GoTo 0

WScript.Echo "Connection successful"
con.Close

Output:

Error: ORA-28000: the account is locked

You can filter on error like

If InStr(1, Err.Description, "ORA-28000:", vbTextCompare) > 0 Then 
    WScript.Echo "Ask your Admin to unlook your account."
End If

Oracle DB user locks VBScript

1 Answers1