DBA Sensation

July 27, 2009

two ways to call Oracle Stored procedure from VB.net

Filed under: [PL/SQL dev&tuning] — Tags: , , , — zhefeng @ 10:05 pm

PROCEDURE TEST_C(temp out varchar2,a IN varchar2, b in varchar2)
temp:=a || b;

Solution 1:
add “Imports System.Data.OleDb” at the code beginning

Dim dbConn As New OleDbConnection
Dim dbComm As OleDbCommand

dbConn.ConnectionString = “Provider=MSDAORA;User ID=xxx;Password=xxx;Data Source=xxx;”
dbComm = dbConn.CreateCommand

dbComm.Parameters.Add(“temp”, OleDbType.VarChar, 30).Direction = ParameterDirection.Output
dbComm.Parameters.Add(“a”, OleDbType.VarChar, 30).Direction = ParameterDirection.Input
dbComm.Parameters(“a”).Value = “test ”
dbComm.Parameters.Add(“b”, OleDbType.VarChar, 30).Direction = ParameterDirection.Input
dbComm.Parameters(“b”).Value = “OK”

dbComm.CommandText = “TEST_C”
dbComm.CommandType = CommandType.StoredProcedure


Solution 2:
add “Imports System.Data.OracleClient” at the code beginning

Dim oraConn As New OracleConnection
Dim oraComm As New OracleCommand

oraConn.ConnectionString = “Data Source=xxx;User Id=xxx;Password=xxx”
oraComm.Connection = oraConn

oraComm.Parameters.Add(“temp”, OracleType.VarChar, 10).Direction = ParameterDirection.Output
oraComm.Parameters.Add(“a”, OracleType.VarChar, 10).Direction = ParameterDirection.Input
oraComm.Parameters(“a”).Value = “test ”
oraComm.Parameters.Add(“b”, OracleType.VarChar, 10).Direction = ParameterDirection.Input
oraComm.Parameters(“b”).Value = “OK”

oraComm.CommandText = “TEST_C”
oraComm.CommandType = CommandType.StoredProcedure


Note: the first parameter name has to be the same as the oracle stored procedure parameter name;
if there is dblink in the stored procedure, then the solution 1 is the only choice.

if you are trying to call oracle stored procedure by passing clob/lob as parameters, then don’t use odbc solution because it has 32k limitation. Oracle have this metalink note talk about this:
From metalink: Subject-32k Limitation When Passing LOB Parameter Through Stored Procedure Doc ID: 252102.1


Workaround 1:

Use the Oracle Provider for OLEDB instead, making sure to set the command
property SPPrmsLOB to TRUE:

objCmd.Properties(“SPPrmsLOB”) = TRUE

Workaround 2:

Instead of passing a CLOB as a parameter to a stored procedure, use a method
that directly interfaces with the database and does not require the use of
a stored procedure to update the CLOBs as in the following example:

Note 126125.1 – ADO Streaming BLOB & CLOB Example Using ODBC and OLEDB in VB (SCR 1388).

Create a free website or blog at WordPress.com.