Using the OracleCommand Object - Executing Stored Procedures with Output Parameters

(Page 3 of 4 )

Stored procedures are the core of most database applications. Stored procedures have significant performance benefits because they are compiled before they are used. This allows the database to forego the typical parsing steps that are required to process dynamic SQL, as well as skip the creation of an access plan. The OracleCommand object is the primary ADO.NET mechanism for executing stored procedures against an Oracle database. To use the OracleCommand class in your project, you first need to add an import directive for the System.Data.OracleClient Namespace in your project. You can find an example showing how to create an import directive for the System.Data.OracleClient in Chapter 5.

The following listing presents the code required to use the GETEMPDETAILS stored procedure that was created in the preceding example in this chapter. If you look back at the preceding code listing, you can see that the GETEMPDETAILS stored procedure accepts a single numeric input parameter. That’s used to identify the employee whose information will be retrieved. The GETEMPDETAILS stored procedure returns the employee name, job description, and salary from the EMP table in the Oracle sample SCOTT database. After the GETEMPDETAILS stored procedure has been created in the SCOTT database, it can then be called from your ADO.NET applications using the OracleCommand object. The following shows how to use the OracleCommand object to execute the GETEMPDETAILS stored procedure and retrieve the value that it returns:

Private Sub OracleCommandSPParms(ByVal sServer As String, _
ByVal sUser As String, ByVal sPassword As String)
Cursor.Current = Cursors.WaitCursor
Dim cn As New OracleConnection("DATA SOURCE=" & sServer _
& ";UID=" & sUser & ";PWD=" & sPassword)
'Setup the Command object's Connection, SQL and parameter types
Dim cmd As New OracleCommand("GETEMPDETAILS", cn)
cmd.CommandType = CommandType.StoredProcedure
Dim parmEMPNO = New OracleParameter("I_EMPNO", _
OracleType.Number)
parmEMPNO.Direction = ParameterDirection.Input
Dim parmENAME = New OracleParameter("O_ENAME", _
OracleType.VarChar, 10)
parmENAME.Direction = ParameterDirection.Output
Dim parmJOB = New OracleParameter("O_JOB", _
OracleType.VarChar, 9)
parmJOB.Direction = ParameterDirection.Output
Dim parmSAL = New OracleParameter("O_SAL", OracleType.Number)
parmSAL.Direction = ParameterDirection.Output
' Add the parameter objects to the Command Parameters collection
cmd.Parameters.Add(parmEMPNO)
cmd.Parameters.Add(parmENAME)
cmd.Parameters.Add(parmJOB)
cmd.Parameters.Add(parmSAL)
Try
' Open the connection & prepare the command
cn.Open()
' Execute the prepared SQL statement to retrieve EMPNO 7788
parmEMPNO.Value = 7788
cmd.ExecuteNonQuery()
Debug.WriteLine(parmENAME.Value)
Debug.WriteLine(parmJOB.Value)
Debug.WriteLine(parmSAL.Value)
Catch e As Exception
Cursor.Current = Cursors.Default
MsgBox(e.Message)
End Try
cn.Close()
Cursor.Current = Cursors.Default
End Sub

Following is the C# version of the OracleCommandSPParms subroutine:

private void OracleCommandSPParms(string sServer, string sUser,
string sPassword)
{
Cursor.Current = Cursors.WaitCursor;
OracleConnection cn = new OracleConnection("DATA SOURCE=" +
sServer + ";UID=" + sUser + ";PWD=" + sPassword);
//Setup the Command object's Connection, SQL and parameter types
OracleCommand cmd = new OracleCommand("GETEMPDETAILS", cn);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter parmEMPNO = new OracleParameter("I_EMPNO",
OracleType.Number);
parmEMPNO.Direction = ParameterDirection.Input;
OracleParameter parmENAME = new OracleParameter("O_ENAME",
OracleType.VarChar, 10);
parmENAME.Direction = ParameterDirection.Output;
OracleParameter parmJOB = new OracleParameter("O_JOB",
OracleType.VarChar, 9);
parmJOB.Direction = ParameterDirection.Output;
OracleParameter parmSAL = new OracleParameter("O_SAL",
OracleType.Number);
parmSAL.Direction = ParameterDirection.Output;
// Add the parameter objs to the Command Parameters collection
cmd.Parameters.Add(parmEMPNO);
cmd.Parameters.Add(parmENAME);
cmd.Parameters.Add(parmJOB);
cmd.Parameters.Add(parmSAL);
try
{
// Open the connection & prepare the command
cn.Open();
// Execute the prepared SQL statement to retrieve EMPNO 7788
parmEMPNO.Value = 7788;
cmd.ExecuteNonQuery();
Debug.WriteLine(parmENAME.Value);
Debug.WriteLine(parmJOB.Value);
Debug.WriteLine(parmSAL.Value);
}
catch (Exception ex)
{
Cursor.Current = Cursors.Default;
MessageBox.Show("Connection error: :" + ex.ToString());
}
cn.Close();
Cursor.Current = Cursors.Default;
}

Three string variables are passed as parameters into the beginning of this subroutine. The sServer variable contains the .NET Service name of the Oracle database. The sUser variable contains the Oracle user’s login, and the sPassword variable contains the user’s password. Next, the cn OracleConnection object is created, followed by an OracleCommand object named cmd. The OracleCommand object’s constructor uses two parameters. The first parameter of the OracleCommand object’s constructor is a string that contains the name of the stored procedure that will be executed: GETEMPDETAILS. For stored procedures, this parameter contains the name of the stored procedure but it can also contain SQL statements that will be executed. The second parameter contains the name of the active OracleConnection object. After the OracleCommand object has been created, its CommandType property is set to CommandType.StoredProcedure, which indicates that a stored procedure will be executed. The OracleCommand object’s CommandType property can accept any of the values shown in the table that follows.

CommandType Values	Description
CommandType.StoredProcedure	The command is a stored procedure.
CommandType.TableDirect	The command is the name of a database table.
CommandType.Text	The command is a SQL statement.

After the OracleCommand object has been created and its CommandType property is set to CommandType.StoredProcedure, four OracleParameter objects are created. These OracleParameter objects are used to supply the input value to the GETEMPDETAILS stored procedure, as well as return the values in the output parameters to the ADO.NET application. In this example, the OracleParameter objects are instantiated using the OracleParameter object’s constructor. Two parameters are passed to the OracleParameter class constructor. The first parameter is a string containing the name of the parameter. The second parameter specifies the data type of the parameter. Because the OracleCommand object uses named parameters, this string must exactly match the name of the parameter as it is defined in the Oracle stored procedure. However, because named parameters are used, the order that the OracleParameters objects are created in or added to the OracleCommand object doesn’t matter. The names are used to identify each parameter, not their position. Here the first OracleParameter object uses the parameter name of the "I_EMPNO"— matching the name of the first parameter in the GETEPDETAILS stored procedure.

Note Oracle named parameters use the colon character to designate a named parameter. You can optionally use or omit the colon in your OracleParameter objects. In this case, the first parameter could have been named either I_EMPNO or :I_EMPNO.

The second parameter used in the OracleParameter object’s constructor specifies the data type that is used in the parameter. This is stored in the OracleType property. Table 9-2 shows the valid values for the OracleType property.

Unfortunately ADO.NET doesn’t provide a convenient way to set up the Direction property along with the other parameter attributes. The next line following the parameter object’s constructor sets the Direction property of the OracleParameter object. Table 9-3 lists the valid enumerations for the OracleParameter Direction property.

Once the OracleParameter objects have been created, they are added to the cmd OracleCommand object’s Parameters collection.

Next, a Try-Catch block is used to open the connection to the Oracle server and then set the value of the parmEMPNO inout parameter to 7788. Then the OracleCommand object’s ExecuteNonQuery method is used to execute the GETEMPDETAILS stored procedure. The ExecuteNonQuery method doesn’t return a resultset, but after the stored procedure has executed, the returned values will be available in the output parameter objects. Here, the contents of the parmENAME, parmJob, and parmSAL Value properties are written to the Debug console. If the connection or the stored procedure fails, a message box showing the error text appears. Closing the connection is the last action in the subroutine.

Table 9-2. OracleType Enumerations

Oracle Type	Description
BFile	Contains a reference to binary data with a maximum size of 4GB that is stored in an external file. Passed as an OracleBFileType data type.
Blob	Contains binary data with a maximum size of 4GB. Passed as an OracleLOB data type.
Byte	Not a native Oracle data type. The Byte type contains an integral type representing unsigned 8-bit integers with values between 0 and 255. Passed as a .NET Byte data type.
Char	Contains a string of up to 2000 bytes. Passed as either a .NET String or an OracleString data type.
Cob	Contains character data with a maximum size of 4GB. Passed as an OracleLob data type.
Cursor	Contains an Oracle REF Cursor, which can be read using the OracleDataReader.
DateTime	Represents dates ranging from January 1, 4712 B.C., to A.D. December 31, 4712. The default format is dd-mmm-yy. Passed as a .NET DataTime or an OracleDataTime data type.
Double	Not a native Oracle data type, the Double contains a double precision floating point value. Passed as a .NET Double or an OracleNumber data type.
Float	Not a native Oracle data type, the Float contains a single precision floating point value. Passed as a .NET Single or an OracleNumber data type.
Int16	Not a native Oracle data type, the Int16 integral type represents signed 16-bit integers with values between -32768 and 32767. Passed as a .NET Int16 or an OracleNumber data type.
Int32	Not a native Oracle data type, the Int32 integral type represents signed 32 bit integers with values between -2147483648 and 2147483647. Passed as a .NET Int32 or an OracleNumber data type.
IntervalDayToSecond	Contains an interval of time in days, hours, minutes, and seconds, and has a fixed size of 11 bytes. Passed as a .NET TimeSpan or OracleTimeSpan data type. (Only on Oracle 9i and later.)
IntervalYearToMonth	Contains an interval of time in years and months, and has a fixed size of 5 bytes. Passed as a .NET Int32 or an OracleMonthSpan data type. (Only on Oracle 9i and later.)
LongRaw	Contains variable-length character string with a maximum size of 2 gigabytes. Passed as a .NET String or an OracleString data type.
LongVarChar	Contains a variable-length character string with a maximum size of 2 gigabytes. Passed as a .NET String or an OracleString data type.
NChar	Contains a fixed-length Unicode character string with a maximum size of 2000 bytes. Passed as a .NET string or an OracleString data type.
NClob	Contains Unicode character data with a maximum size of 4GB. Passed as an OracleLob data type.
Number	Contains variable-length numeric data with a maximum and scale of 38. Passed as a .NET Decimal or OracleNumber data type.
NVarChar(nVarChar2)	Contains a variable-length Unicode string with a maximum size of 4000 bytes. Passed as a .NET String or an OracleString data type.
Raw	Contains variable-length binary data with a maximum size of 2000 bytes. Passed as a .NET Byte array or an OracleBinary data type.
RowID	Contains a base64 string representing an Oracle RowID data type. Passed as a .NET String or an OracleString data type.
SByte	Not a native Oracle data type, the sByte data type is an integral type representing signed 8-bit integers with values between -128 and 127. Passed as a .NET SByte data type.
Timestamp	Contains the date and time and ranges in size from 7 to 11 bytes. Passed as a .NET DateTime or an OracleDateTime data type. (Only on Oracle 9i and later.)
TimestampLocal	Contains the date, time, and a reference to the original time zone, and ranges in size from 7 to 11 bytes. Passed as a .NET DateTime or an OracleDateTime data type. (Only on Oracle 9i and later.)
TimestampwithTZ	Contains the date, time, and a specified time zone, and has a fixed size of 13 bytes. Passed as a .NET DateTime or an OracleDateTime data type. (Only on Oracle 9i and later.)
UInt16	Not a native Oracle data type, the UInt16 contains an integral type representing unsigned 16-bit integers with values between 0 and 65535. Passed as a .NET UInt16 or an OracleNumber data type.
UInt32	Not a native Oracle data type, the UInt16 contains an integral type representing unsigned 32-bit integers with values between 0 and 4294967295. Passed as a .NET UInt32 or an OracleNumber data type.
VarChar (VarChar2)	Contains a variable-length character string with a maximum size of 4000 bytes. Passed as a .NET String or an OracleString data type.

Table 9-3. OracleParameterDirection Enumeration.

Enumeration	Description
ParameterDirection.Input	The parameter is an input parameter.
ParameterDirection.InputOutput	The parameter is capable of both inputand output.
ParameterDirection.Output	The parameter is an output parameter.
ParameterDirection.ReturnValue	The parameter represents a return value.

Next: Executing Transactions with Parameterized SQL Statements >>
 

More Database Articles
More By McGraw-Hill/Osborne

Comments On: Using the OracleCommand Object

· ' Create a suitable connection object for a given connection string. Function...   >>> Post your comment now!

Buy this book...

This article is taken from chapter 9 of ADO.NET: The Complete Reference, written by Michael Otey and Denielle Otey (McGraw-Hill/Osborne, 2004; ISBN: 0072228989). Check it out at your favorite bookstore. Buy this book now.