Oracle: Check to see if a value is numeric

One of the functions that MS SQL has that Oracle does not have by default (which can be frustrating at times) is a function that checks to see if a value is numeric. After playing around a bit and digging through oracle in line function documentation I finally found something that would pass for this functionality.

I wanted something that would provide a result of 0 (is not numeric) or 1 (is numeric). Below is a SQL statement that I used during th e"playing" around process.

SELECT
    CASE WHEN nvl(length(translate(trim('111d'), ',.+-0123456789', ' ')),0) > 0 THEN 0 ELSE 1 END NonNumeric,
    CASE WHEN nvl(length(translate(trim('1111'), ',.+-0123456789', ' ')),0) > 0 THEN 0 ELSE 1 END Numeric
FROM DUAL

The idea behind this code is that first we take a string value (or field) and use the translate function to remove all of the numeric values and characters that are used with numeric values with nothing. Around that value I used the length function to return the length of "translated" value. I then evaluate the length. If it is larger than zero then I return a value of 1, becuase any value with a length greater than zero has a non-numeric character in it. Otherwise it returns a value of 1 (which means that the value is a numeric value).

I attempted to put together syntax to create a function called ISNUMERIC and pasted it below. I do not have an Oracle database on this server or have access to an Oracle in this environment to be able to run this code to make sure that it executes properly. I believe it will work though it has not been tested.

CREATE OR REPLACE FUNCTION ISNUMERIC (VALUE IN VARCHAR2)
    RETURN NUMBER
AS
BEGIN
  IF nvl(length(translate(trim(VALUE), ',.+-0123456789', ' ')),0) > 0
  THEN
    RETURN 0
  ELSE
    RETURN 1
  END IF
END ISNUMERIC;

Utility to check to see if a database is alive

A while back I had a need to come up with a quick and easy solution to be able to check whether a database was available via a command line utility.  The client had a software package that had the ability to run a command line application and read the results and record that to monitor the up time for database.

I decided to go with a simple VB solution using Visual Studio 2005.  I am sure there are better ways to accomplish the same thing though time was of the essence and I needed a workable solution and only had a day to do it.

I started off creating a new Visual Basic console project in Visual Studio.  I deleted the default module that was part of the project when it was created and created a new module named MainModule.  I added a new class to the module to manage the database connections.

I started with the connection class.  I needed to be able to handle multiple types of database connections.  Best practice is to have a class for each type of database connection all of which having the same functions to more ligically handle and manage the different code needed for the different connections.  I did not have time to do best practice.  Instead I put in a switch statement to manage the different database types.

First off I created class level variables to manage connection variables.

Private _oracleConnString As String

Private _sqlConnString As String
Private _odbcConnString As String

Private _dataType As String
Private _dataSource As String
Private _userId As String
Private _password As String
Private cnOracle As OracleClient.OracleConnection
Private cnSql As SqlClient.SqlConnection
Private cnOdbc As Odbc.OdbcConnection

Private writer As StreamWriter
Private logFile As String

After the variables I added a "New" sub procedure which receives values that are assigned to the class level variables.

Public Sub New(ByVal DataType As String, ByVal DataSource As String, ByVal UserID As String, ByVal Password As String)

_dataType = DataType
_dataSource = DataSource
_userId = UserID
_password = Password

_oracleConnString = "Data Source=[DATASOURCE];Persist Security Info=True;User ID=[USERID];Password=[PASSWORD];Unicode=True"
_sqlConnString = "Data Source=[DATASOURCE];Persist Security Info=True;User ID=[USERID];Password=[PASSWORD]"
_odbcConnString = "Dsn=[DATASOURCE];uid=[USERID];pwd=[PASSWORD]"

logFile = "dbcheck.log"
writer = New StreamWriter(logFile, True)
End Sub

The last part of this class is a function for the module code to call to open the database connection and return a result.  The function starts off with a variable to store a boolean value on whether the connection was successful or not.  The value of this variable is the return result for the function.  After that I created a switch statement based upon the database connection type (MS SQL, Oracle or ODBC).  Each connection attempt is surrounded by a try/catch statement.  If there are any errors the process writes the error to an error log.
 

Dim success As Boolean = False


Select Case _dataType

Case "Oracle"
_oracleConnString = _oracleConnString.Replace("[DATASOURCE]", _dataSource)
_oracleConnString = _oracleConnString.Replace("[USERID]", _userId)
_oracleConnString = _oracleConnString.Replace("[PASSWORD]", _password)
cnOracle = New OracleClient.OracleConnection(_oracleConnString)

Try
cnOracle.Open()
cnOracle.Close()
Return 1
Catch ex As Exception
Console.Write(ex.Message)
writer.WriteLine(Now.ToString())
writer.WriteLine(ex.Message)
Return 0
End Try

Case "SQL"
_sqlConnString = _sqlConnString.Replace("[DATASOURCE]", _dataSource)
_sqlConnString = _sqlConnString.Replace("[USERID]", _userId)
_sqlConnString = _sqlConnString.Replace("[PASSWORD]", _password)
cnSql = New SqlClient.SqlConnection(_sqlConnString)

Try
cnSql.Open()
cnSql.Close()
Return 1
Catch ex As Exception
Console.Write(ex.Message)
writer.WriteLine(Now.ToString())
writer.WriteLine(ex.Message)
Return 0
End Try

Case Else
_odbcConnString = _odbcConnString.Replace("[DATASOURCE]", _dataSource)
_odbcConnString = _odbcConnString.Replace("[USERID]", _userId)
_odbcConnString = _odbcConnString.Replace("[PASSWORD]", _password)
cnOdbc = New Odbc.OdbcConnection(_odbcConnString)

Try
cnOdbc.Open()
cnOdbc.Close()
Return 1
Catch ex As Exception
Console.Write(ex.Message)
writer.WriteLine(Now.ToString())
writer.WriteLine(ex.Message)
Return 0
End Try
End Select

Now that the connection class is complete it was time to put together the code for the main sub procedure in the main module that will capture the command line arguments and perform the connection test using the provided arguments.
 
I started off with the declaration of variables

Dim _dataType As String

Dim _dataSource As String
Dim _userId As String
Dim _password As String
Dim I As Integer = 0

Now that the variables are declared I added a process to cycle through the provided command line arguments and assign the values to the proper variables.

For Each arg As String In args

If arg.ToUpper = "/DATATYPE" Then
_dataType = args(I + 1)
End If

If arg.ToUpper = "/DATASOURCE" Then
_dataSource = args(I + 1)
End If

If arg.ToUpper = "/USERID" Then
_userId = args(I + 1)
End If

If arg.ToUpper = "/PASSWORD" Then
_password = args(I + 1)
End If

i = i + 1
Next

After the arguments are processed it is time to add the code that calls the connection class and tests to see if the connection is successful.

Dim success As Integer = 0


Try
Dim cn As Connection = New Connection(_dataType, _dataSource, _userId, _password)
success = cn.Open()
Catch ex As Exception
Console.Write(ex.Message)
End Try
If success = 1 Then
Console.Write("SUCCESS")
Else
Console.Write("FAILURE")
End If

A sample command line to execute this utility is:

dbcheck.exe /DATATYPE SQL /DATASOURCE SERVERNAME /USERID USERNAME /PASSWORD PASSWORD

To download a copy of the code click this link.

How to create a utility to send an email via the command line

A while ago I came across a need to create a utility that can send email from the command line.  The reason for this is that the client I was working with had an application that did not have email support but it did have the ability to make a call from the command line.  The client needed to have a process that could pick up a file that is generated from the application and send it to an email address.

There are quite a few ways to accomplish this and there are already some utilities that I have seen that can do this though my need was to have a solution in a very short period in time.  There was no need for any bells and whistles.  They only needed the base functionality to being able to send the file via email and they needed the solution as soon as possible.

I decided to build the utility using Visual Studio 2005 with Visual Basic (though C# is currently my preferred language).

I began by creating a console application called "sendmail".  I deleted the default module that was created when the project was created.  I added a new module named "SMTPMail".  In that module I have two classes, SMTPMail and SendNewMail.

The SMTPMail class is where I will setup the properties and functions needed to send the email.  The SendNewMail class is used as a wrapper to collect the informaion from the command line and pass it to the SMTPMail class.

In the SMTPMail class I first added variables needed to store the infomation to send the email

Private _from As String
Private _to As String
Private _toName As String
Private _cc As String
Private _bcc As String
Private _attachment As Attachment
Private _subject As String
Private _body As String
Private _smtpPort As Integer = 25
Private _smtpServer As String

Next I defined the properties needed to populate the variables

Public Property SendFrom() As String

Get
Return _from
End Get
Set(ByVal value As String)
_from = value
End Set
End Property

Public Property SendTo() As String
Get
Return _to
End Get
Set(ByVal value As String)
_to = value
End Set
End Property

Public Property SendToName() As String
Get
Return _toName
End Get
Set(ByVal value As String)
_toName = value
End Set
End Property

Public Property SendCC() As String
Get
Return _cc
End Get
Set(ByVal value As String)
_cc = value
End Set
End Property

Public Property SendBCC() As String
Get
Return _bcc
End Get
Set(ByVal value As String)
_bcc = value
End Set
End Property

Public Property SendSubject() As String
Get
Return _subject
End Get
Set(ByVal value As String)
_subject = value
End Set
End Property

Public Property SendBody() As String
Get
Return _body
End Get
Set(ByVal value As String)
_body = value
End Set
End Property

Public Property SMTPPort() As Integer
Get
Return _smtpPort
End Get
Set(ByVal value As Integer)
_smtpPort = value
End Set
End Property

Public Property SMTPServer() As String
Get
Return _smtpServer
End Get
Set(ByVal value As String)
_smtpServer = value
End Set
End Property

The next step was to add a function to prepare the email message

Public Function PrepareMessage() As MailMessage

Dim toAddr As New MailAddress(_to, _toName)
Dim msg As New MailMessage(_from, _to, _subject, _body)

If Not _toName = String.Empty Then
msg.To.Clear()
msg.To.Add(toAddr)
End If

If Not _attachment Is Nothing Then
msg.Attachments.Add(_attachment)
End If

If _cc <> "" Then
msg.CC.Add(_cc)
End If

If _bcc <> "" Then
msg.Bcc.Add(_bcc)
End If

Return msg
End Function

The last elements that would need to be added to this class are the sub procedures needed to add the attachment to the message and the procedure to send the message.
 

Public Sub AddAttachment(ByVal FilePath As String)

_attachment = New Attachment(FilePath)
End Sub

Public Sub Send()
Dim mailClient As New SmtpClient(_smtpServer, _smtpPort)
mailClient.Send(PrepareMessage)
End Sub

I did not add any comments (which is bad form) and there is not much explanation on each of the lines of code though it is very self explanitory if you are familiar with VB. 
 
Next I needed to add the code to the SendNewMail class to collect the information from the command line and send it to the SendMail class.
 
Within this class there is only one sub procedure, "Main".  This is the procudure that will be called by the console when the executable is run.  I began editing this procedure similar to the other class by creating the variables I needed to store the information passed from the command line.
 

Dim _sendMail As New SMTPMail
Dim _trace As Boolean = False
Dim trace As String
Dim logFile As String = "sendmail.log"
Dim writer As StreamWriter = New StreamWriter(logFile, True)
Dim i As Integer = 0

NOTE:  The variable "_trace" is a boolean variable used to see if the user wants to create a log file to log all the transactions that pass through the executable.
 
Next I setup a for/next loop to cycle through each of the parameters that are passed from the command line and sets the variables with the passed values where appropriate.  A sample of the command line that I wanted to use would look like this:

c:\projects\sendmail\sendmail.exe /SMTPServer "servername" /To "bob@test.com" /From "sam@test.com" /Subject "test email" /Attachment "c:\projects\sendmail\test.pdf"

The for/next loop needed to be able to handle a command line in this format.  If any of the desired parameters are found in the command line arguments then the process will take the argument directly after it and assign it to the appropriate variable

For Each str As String In args

If str = "/SMTPServer" Then
_sendMail.SMTPServer = args(i + 1).Replace("", """")
End If

If str = "/SMTPport" Then
_sendMail.SMTPPort = args(i + 1).Replace("", """")
End If

If str = "/To" Then
_sendMail.SendTo = args(i + 1).Replace("", """")
End If

If str = "/ToName" Then
_sendMail.SendToName = args(i + 1).Replace("", """")
End If

If str = "/From" Then
_sendMail.SendFrom = args(i + 1).Replace("", """")
End If

If str = "/Subject" Then
_sendMail.SendSubject = args(i + 1).Replace("", """")
End If

If str = "/Body" Then
_sendMail.SendBody = args(i + 1).Replace("", """")
End If

If str = "/CC" Then
_sendMail.SendCC = args(i + 1).Replace("", """")
End If

If str = "/BCC" Then
_sendMail.SendBCC = args(i + 1).Replace("", """")
End If

If str = "/Attachment" Then
_sendMail.AddAttachment(args(i + 1))
End If

If str = "/Trace" Then
_trace = True
End If

i = i + 1
Next

Lastly I created a "Try" statement to handle the sending of the email as well as some loging processes.  It is very important in development to add the ability to "trace" an application to help troubleshoot errors.  Within the "Try statment I check the _trace variable to see if the commandline had an argument to turn on tracing.  If it does then it will output all the values being send to the email process to the log file.  Next the process will call the function to send the email with the provided parameters.  If there is an issue with sending the email the the "Catch" portion of the statment will output the error to the log file and lastly the "Finaly" portion of the statement will clear and destroy any open references in the code.

Try

If _trace Then
trace = "SMTPServer=" & _sendMail.SMTPServer & ":"
trace &= "SMTPPort=" & _sendMail.SMTPPort & ":"
trace &= "SentTo=" & _sendMail.SendTo & ":"
trace &= "SentToName=" & _sendMail.SendToName & ":"
trace &= "SendFrom=" & _sendMail.SendFrom & ":"
trace &= "SendSubject=" & _sendMail.SendSubject & ":"
trace &= "SendBody=" & _sendMail.SendBody & ":"
trace &= "SendCC=" & _sendMail.SendCC & ":"
trace &= "SendBCC=" & _sendMail.SendBCC & ":"
trace &= Now.Date.ToString("yyyyMMdd") & " " & Now.TimeOfDay.ToString() & vbCrLf
writer.WriteLine(trace)
End If

_sendMail.Send()

Catch ex As Exception
trace = ex.Message & " - " & ex.StackTrace.ToString & vbCrLf
trace &= Now.Date.ToString("yyyyMMdd") & " " & Now.TimeOfDay.ToString() & vbCrLf
writer.WriteLine(trace)

Finally
If Not writer Is Nothing Then
writer.Flush()
writer.Close()
writer = Nothing
End If
End Try
End Sub

Click this link to download the vb file that I used in the project