OleDbCommand and output parameters in Oracle

[Eric]

I'm new in the oracle world and I simply try to call a stored
procedure with output parameter. I have seen that most people are
using stored procedure in package (especially when using cursors). My
first question : is it possible to get query result without using
packages and cursors.For exemple simply returning simple data type.

I have tried the following:

CREATE OR REPLACE PROCEDURE SP_TEST(Code IN
f_captive.cap_code%type,Nom OUT f_captive.cap_nom%type)
IS

BEGIN

SELECT cap_nom
INTO Nom
FROM f_captive
WHERE cap_code like Code;

END;

The C# code calling the stored procedure is the following:

OleDbConnection con=new
OleDbConnection(@"Provider=MSDAORA.1;Password=mypsw;User ID=me;Data
Source=mydsn");
con.Open();

string sp_request= "SP_CAPTIVES";
OleDbCommand cmd=new OleDbCommand(sp_request,con);
cmd.CommandType = CommandType.StoredProcedure;


OleDbParameter Param= cmd.Parameters.Add("Code",OleDbType.VarChar,2);
Param.Direction = ParameterDirection.Input ;
Param.Value = m_sCapCode;

OleDbParameter out_Name=cmd.Parameters.Add("Nom",OleDbType.VarChar,35);
out_Name.Direction = ParameterDirection.Output ;


OleDbDataReader dr=cmd.ExecuteReader();
dr.Read();
string sFieldCount = dr.FieldCount.ToString();

by using this syntax for sp_request string to call the stored
procedure the the resulset is empty and no row is returned but it
should because tested in SQL Worksheet it returns one row as expected.

using the ODBC Escape syntax "{call SP_CAPTIVES(?,?)}" I get a error
telling that the ODBC Escape syntax is wrong.
Then i try with named variables "{call SP_CAPTIVES(Code,Nom)}", i was
the same error message.

Always in the goal to execute the same query I tried to use the
following function:

CREATE OR REPLACE FUNCTION GET_CAPTIVES(Code IN
f_captive.cap_code%type)
RETURN f_captive.cap_nom%type
IS

Nom f_captive.cap_nom%type;

BEGIN

SELECT cap_nom
INTO Nom
FROM f_captive
WHERE cap_code like Code;

RETURN Nom;

END;


And i modified my C# code in consequence:


OleDbConnection con=new
OleDbConnection(@"Provider=MSDAORA.1;Password=mypsw;User ID=me;Data
Source=mydsn");
con.Open();

string sp_request= "GET_CAPTIVES";
OleDbCommand cmd=new OleDbCommand(sp_request,con);
cmd.CommandType = CommandType.StoredProcedure;


OleDbParameter Param= cmd.Parameters.Add("Code",OleDbType.VarChar,2);
Param.Direction = ParameterDirection.Input ;
Param.Value = m_sCapCode;

OleDbParameter out_Name=cmd.Parameters.Add("Nom",OleDbType.VarChar,35);
out_Name.Direction = ParameterDirection.ReturnValue; //CHANGE FOR
FUNCTION


OleDbDataReader dr=cmd.ExecuteReader();
dr.Read();

Then I get the following error:
ORA-06550: line 1 , column 7
PLS-00306: bad number or argument type
PL/SQL : statement ignored -2147217900

the real argument type in the DB is Varchar2(2) for input parameter
Code
and Varchar2(35) for output parameter Nom.


Could someone tell me where I did wrong or give me some help to go on
the right way to call a oracle store procedure
thanks in advance

Eric

 

[Miha Markic]

Hi Eric,

I did play with normal argument types.
Yes, it is possible.
a) you should execute ExecuteNonQuery instead of ExecuteReader (which
returns a cursor).
b) you should use Oracle managed provider (OracleClient namespace)

 

[Eric]

Hi Miha,

Thank you for your advice. I simply used the bad method ExecuteReader
instead of ExecuteNonQuery. Now I can execute functions and procedures
with OleDB , ODBC, using packages (but without cursors) and it works
fine.
But I have a problem with OracleClient connection.

My connectiong string format has been taken from microsoft exemple:
"HOW TO:Call Oracle Stored Procedure in Visual Basic .NET with
Microsoft Oracle Managed Provider"

and my code is the foolowing:

OracleConnection con=new
OracleConnection("Server=oracleclientalias;Uid=myuid;Pwd=mypwd");
con.Open();

The System.Data.OracleClient has been référenced in the project
and the 'using' compilation instruction has also been used .
I get the following error:

Cannot find entry point OCIEnvCreate in DLL oci.dll

this file is present on the PC at client side in "c:\orant\bin" and
the version is 0.0.0.0 , it's the default installation file for Oracle
8.0.5 . Do I have a bad oci.dll file version ?

Do you have already encountered this problem?

thank in advance for your help

Eric

 

[Miha Markic]

Hi Eric,

Is this an asp.net app?

 

[Eric]

Hi Miha,

No it isn't a ASP.NET application but just a ClassLibrary dll used by
a windows Application.

Eric

 

[Miha Markic]

Hi Eric,

Yes, your oracle client is too old.
Here you go:
System Requirements for the .NET Managed Provider for Oracle
Before you run Setup to install the Microsoft .NET Framework Managed
Provider for Oracle, the following items must be installed on the computer
to which you are downloading the provider:
a.. Microsoft .NET Framework
b.. Microsoft Data Access Components (MDAC) 2.6 or later (Microsoft
recommends MDAC 2.7)
c.. Oracle 8i Client Release 3 (8.1.7) or later