Using Stored Procedures via the OracleCommand Class

In This Topic

This topic describes how to create and use Oracle stored procedures and functions with dotConnect for Oracle by the help of OracleCommand class.

There are two general ways of executing a stored procedure via OracleCommand.

The first way is to include a procedure invocation into a PL/SQL block and execute this block by putting it into the OracleCommand.CommandText property. In this case, the data returned by the procedure may be at once processed in the same block. If the procedure needs some parameters, they should be added to the OracleCommand.Parameters collection. This method does not differ from the usual command execution, please see Using Parameters article and OracleCommand Class for details.

The second way is to set the OracleCommand.CommandType to System.Data.CommandType.StoredProcedure. In this case, CommandText should be set to the procedure's name. The following sample shows how a DataTable can be filled using the get_all_depts_proc procedure from the previous section:

// Open the connection
OracleConnection connection 
    = new OracleConnection("Server=Ora; User Id=Scott; Password = tiger;");
connection.Open();

// Create a command
OracleCommand command = new OracleCommand();
command.Connection = connection;

// Set the CommandType property to execute 
// stored procedures or functions by this command
command.CommandType = System.Data.CommandType.StoredProcedure;

// Set the name of procedure or function to be executed
command.CommandText = "get_all_depts_proc";

// The ParameterCheck property should be true to automatically 
// check the parameters needed for the procedure execution.
command.ParameterCheck = true;

// At this moment, the command is ready for execution. 
// As we have an output cursor parameter, we may use the command to fill a data table.
OracleDataTable dt = new OracleDataTable(command, connection);
dt.Fill();

Visual Basic


Dim connection _
    As New OracleConnection("Server=Ora; User Id=Scott; Password = tiger;")
connection.Open()

' Create a command.
Dim command = New OracleCommand()
command.Connection = connection

' Set the CommandType property to execute stored procedures or functions by this command.
command.CommandType = System.Data.CommandType.StoredProcedure

' Set the name of procedure or function to be executed.
command.CommandText = "get_all_depts_proc"

' The ParameterCheck property should be true to automatically 
' check the parameters needed for the procedure execution.
command.ParameterCheck = True

' At this moment, the command is ready for execution. 
' As we have an output cursor parameter, we may use the command to fill a data table.
Dim dt = New OracleDataTable(command, connection)
dt.Fill()

Setting CommandText to "get_all_depts_func", the very same code fills the data table using the stored function instead of the procedure.

Optimization of Stored Procedure Execution

When executing ExecuteReader or ExecuteScalar, and OracleCommand.CommandType is set to System.Data.CommandType.StoredProcedure, by default an additional queries are performed that check whether the procedure is pipelined and, if not, describing the parameters (checking for OUT cursor parameters). This allows you to execute stored procedures after setting just the necessary procedure parameters without bothering to fill the parameters collection completely and correctly, because after getting metadata it will be filled automatically.

However, performing an additional query may be unsuitable, and in some cases may lead to a performance loss. dotConnect for Oracle allows disabling this check with the DescribeStoredProcedure connection string parameter.

If you simply set this connection string parameter to false, OracleCommand executes stored routines without any additional checks. In such case the routine must not be a table-valued function, and all its parameters must be set manually.

If you want to execute a table-valued function without additional checks, you need to set the IsTableValuedFunction property of OracleCommand to true. This allows you to execute table-valued functions without additional checks. Setting this property to true is also the only way to execute non-pipelined table-valued functions. Even if DescribeStoredProcedure is set to true, you must also set IsTableValuedFunction to true to execute a non-pipelined table-valued function.

When you want to disable additional checks just for a single instance of OracleCommand without disabling them for a connection, set both IsTableValuedFunction property (to true or false depending on whether the executed function is table-valued) and ImplicitRefCursors property to false. Setting the IsTableValuedFunction property disables the check whether the the executed function is table-valued, and setting the ImplicitRefCursors property to false disables the check for additional cursor parameters.

Optimization of Stored Procedure Execution

See Also