Oracle Client Parameters

  • Thread starter Thread starter Bob Clegg
  • Start date Start date
B

Bob Clegg

Hi,
I am trying my first attempt at calling an Oracle Procedure using the
Micosoft Oracle data client.
Can any one spot what I am doing wrong?
I get Oracle Error 06550 'Wrong number or types of arguments in call'
The table contains 14 columns number(10)
The proc was generated by Oracle Migration Workbench from an SQL server
proc. I have altered the RC1 Parameter type from 'IN OUT' to 'OUT' to match
the code which was drawn from one of the help examples.
thanks
bob

CALLING CODE:
*********************
Dim cmdConn As New OracleClient.OracleCommand

Dim cmdSource As OracleClient.OracleDataReader

Dim myDBNUll as object

Try

myDBNull = Convert.DBNull

strSQL = "proc_GetColWidths"

cmdConn = New OracleClient.OracleCommand(strSQL, New
OracleClient.OracleConnection(mstrConn))

cmdConn.Parameters.Add(New OracleClient.OracleParameter("xx",
OracleClient.OracleType.Cursor, 2000, ParameterDirection.Output, True, 0, 0,
"", DataRowVersion.Default, myDBNUll))

cmdConn.CommandType = CommandType.StoredProcedure

cmdConn.CommandText = strSQL

cmdConn.Connection.Open()

cmdSource = cmdConn.ExecuteReader(CommandBehavior.CloseConnection)'FAILS
HERE!!!!!

************************************************

ORACLE Procedure :
########################################
PROCEDURE PROC_GETCOLWIDTHS(

RC1 OUT Omwb_emulation.globalPkg.RCT1)

AS

StoO_selcnt INTEGER;

StoO_error INTEGER;

StoO_rowcnt INTEGER;

StoO_crowcnt INTEGER := 0;

StoO_fetchstatus INTEGER := 0;

StoO_errmsg VARCHAR2(255);

StoO_sqlstatus INTEGER;

BEGIN

OPEN RC1 FOR

SELECT *

FROM GridColWidths;

END PROC_GETCOLWIDTHS;

#######################################################
 
¤ Hi,
¤ I am trying my first attempt at calling an Oracle Procedure using the
¤ Micosoft Oracle data client.
¤ Can any one spot what I am doing wrong?
¤ I get Oracle Error 06550 'Wrong number or types of arguments in call'
¤ The table contains 14 columns number(10)
¤ The proc was generated by Oracle Migration Workbench from an SQL server
¤ proc. I have altered the RC1 Parameter type from 'IN OUT' to 'OUT' to match
¤ the code which was drawn from one of the help examples.
¤ thanks
¤ bob
¤
¤ CALLING CODE:
¤ *********************
¤ Dim cmdConn As New OracleClient.OracleCommand
¤
¤ Dim cmdSource As OracleClient.OracleDataReader
¤
¤ Dim myDBNUll as object
¤
¤ Try
¤
¤ myDBNull = Convert.DBNull
¤
¤ strSQL = "proc_GetColWidths"
¤
¤ cmdConn = New OracleClient.OracleCommand(strSQL, New
¤ OracleClient.OracleConnection(mstrConn))
¤
¤ cmdConn.Parameters.Add(New OracleClient.OracleParameter("xx",
¤ OracleClient.OracleType.Cursor, 2000, ParameterDirection.Output, True, 0, 0,
¤ "", DataRowVersion.Default, myDBNUll))
¤
¤ cmdConn.CommandType = CommandType.StoredProcedure
¤
¤ cmdConn.CommandText = strSQL
¤
¤ cmdConn.Connection.Open()
¤
¤ cmdSource = cmdConn.ExecuteReader(CommandBehavior.CloseConnection)'FAILS
¤ HERE!!!!!
¤
¤ ************************************************
¤
¤ ORACLE Procedure :
¤ ########################################
¤ PROCEDURE PROC_GETCOLWIDTHS(
¤
¤ RC1 OUT Omwb_emulation.globalPkg.RCT1)
¤
¤ AS
¤
¤ StoO_selcnt INTEGER;
¤
¤ StoO_error INTEGER;
¤
¤ StoO_rowcnt INTEGER;
¤
¤ StoO_crowcnt INTEGER := 0;
¤
¤ StoO_fetchstatus INTEGER := 0;
¤
¤ StoO_errmsg VARCHAR2(255);
¤
¤ StoO_sqlstatus INTEGER;
¤
¤ BEGIN
¤
¤ OPEN RC1 FOR
¤
¤ SELECT *
¤
¤ FROM GridColWidths;
¤
¤ END PROC_GETCOLWIDTHS;
¤
¤ #######################################################


I thing you need to define your output parameter as a REF_CURSOR in your package or stored
procedure. The following article uses a package:

HOW TO: Return an Oracle Ref Cursor to a .NET DataReader Object by Using the .NET Managed Provider
for Oracle
http://support.microsoft.com/default.aspx?scid=kb;en-us;322160&Product=ado


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Paul said:
¤ Hi,
¤ I am trying my first attempt at calling an Oracle Procedure using the
¤ Micosoft Oracle data client.
¤ Can any one spot what I am doing wrong?
¤ I get Oracle Error 06550 'Wrong number or types of arguments in call'
¤ The table contains 14 columns number(10)
¤ The proc was generated by Oracle Migration Workbench from an SQL server
¤ proc. I have altered the RC1 Parameter type from 'IN OUT' to 'OUT' to match
¤ the code which was drawn from one of the help examples.
¤ thanks
¤ bob
¤
¤ CALLING CODE:
¤ *********************
¤ Dim cmdConn As New OracleClient.OracleCommand
¤
¤ Dim cmdSource As OracleClient.OracleDataReader
¤
¤ Dim myDBNUll as object
¤
¤ Try
¤
¤ myDBNull = Convert.DBNull
¤
¤ strSQL = "proc_GetColWidths"
¤
¤ cmdConn = New OracleClient.OracleCommand(strSQL, New
¤ OracleClient.OracleConnection(mstrConn))
¤
¤ cmdConn.Parameters.Add(New OracleClient.OracleParameter("xx",
¤ OracleClient.OracleType.Cursor, 2000, ParameterDirection.Output, True, 0, 0,
¤ "", DataRowVersion.Default, myDBNUll))
¤
¤ cmdConn.CommandType = CommandType.StoredProcedure
¤
¤ cmdConn.CommandText = strSQL
¤
¤ cmdConn.Connection.Open()
¤
¤ cmdSource = cmdConn.ExecuteReader(CommandBehavior.CloseConnection)'FAILS
¤ HERE!!!!!
¤
¤ ************************************************
¤
¤ ORACLE Procedure :
¤ ########################################
¤ PROCEDURE PROC_GETCOLWIDTHS(
¤
¤ RC1 OUT Omwb_emulation.globalPkg.RCT1)
¤
¤ AS
¤
¤ StoO_selcnt INTEGER;
¤
¤ StoO_error INTEGER;
¤
¤ StoO_rowcnt INTEGER;
¤
¤ StoO_crowcnt INTEGER := 0;
¤
¤ StoO_fetchstatus INTEGER := 0;
¤
¤ StoO_errmsg VARCHAR2(255);
¤
¤ StoO_sqlstatus INTEGER;
¤
¤ BEGIN
¤
¤ OPEN RC1 FOR
¤
¤ SELECT *
¤
¤ FROM GridColWidths;
¤
¤ END PROC_GETCOLWIDTHS;
¤
¤ #######################################################


I thing you need to define your output parameter as a REF_CURSOR in your package or stored
procedure. The following article uses a package:

HOW TO: Return an Oracle Ref Cursor to a .NET DataReader Object by Using the .NET Managed Provider
for Oracle
http://support.microsoft.com/default.aspx?scid=kb;en-us;322160&Product=ado


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
Click to expand...
Hi Paul,
I am using Oracle 8.1.7.
Using toad I tried to alter parameter to REF_CURSOR and also REF CURSOR.
Both won't compile. Former says 'identifier REF_CURSOR must be declared'
Latter says identifier CURSOR must be identified even though it colours
REF and CURSOR blue indicating two key words.

The examples that I have seen seem to use predefined types.
eg 'Using .NET FrameWork Data Provider for Oracle to Improve .NET
Application Performance ' by Gregory Leake July 2002.
He is using 'P_Cursor OUT NILES_TYPES.NILE_CURSOR'

What I did was convert my SQL SERVER database to Oracle using the Oracle
Migration Workbench.

I have just chased through the Oracle model generated and Type RCT1 is a
global predefined type of REF CURSOR.
"TYPE RCT1 IS REF CURSOR;/*new weak cursor definition*/"
I then created a new package in the current instance with a type of RCT2
because I thought maybe that global type wasn't in scope somehow for the
dot net call.
But it has made no difference.

I am truly stuffed here. I can't believe it is this difficult to use a
..NET Client to simply retrieve a Datareader.
I have 73 stored procs to get working.
thanks anyway
regards
bob
 
¤ Paul Clement wrote:
¤ >
¤ > ¤ Hi,
¤ > ¤ I am trying my first attempt at calling an Oracle Procedure using the
¤ > ¤ Micosoft Oracle data client.
¤ > ¤ Can any one spot what I am doing wrong?
¤ > ¤ I get Oracle Error 06550 'Wrong number or types of arguments in call'
¤ > ¤ The table contains 14 columns number(10)
¤ > ¤ The proc was generated by Oracle Migration Workbench from an SQL server
¤ > ¤ proc. I have altered the RC1 Parameter type from 'IN OUT' to 'OUT' to match
¤ > ¤ the code which was drawn from one of the help examples.
¤ > ¤ thanks
¤ > ¤ bob
¤ > ¤
¤ > ¤ CALLING CODE:
¤ > ¤ *********************
¤ > ¤ Dim cmdConn As New OracleClient.OracleCommand
¤ > ¤
¤ > ¤ Dim cmdSource As OracleClient.OracleDataReader
¤ > ¤
¤ > ¤ Dim myDBNUll as object
¤ > ¤
¤ > ¤ Try
¤ > ¤
¤ > ¤ myDBNull = Convert.DBNull
¤ > ¤
¤ > ¤ strSQL = "proc_GetColWidths"
¤ > ¤
¤ > ¤ cmdConn = New OracleClient.OracleCommand(strSQL, New
¤ > ¤ OracleClient.OracleConnection(mstrConn))
¤ > ¤
¤ > ¤ cmdConn.Parameters.Add(New OracleClient.OracleParameter("xx",
¤ > ¤ OracleClient.OracleType.Cursor, 2000, ParameterDirection.Output, True, 0, 0,
¤ > ¤ "", DataRowVersion.Default, myDBNUll))
¤ > ¤
¤ > ¤ cmdConn.CommandType = CommandType.StoredProcedure
¤ > ¤
¤ > ¤ cmdConn.CommandText = strSQL
¤ > ¤
¤ > ¤ cmdConn.Connection.Open()
¤ > ¤
¤ > ¤ cmdSource = cmdConn.ExecuteReader(CommandBehavior.CloseConnection)'FAILS
¤ > ¤ HERE!!!!!
¤ > ¤
¤ > ¤ ************************************************
¤ > ¤
¤ > ¤ ORACLE Procedure :
¤ > ¤ ########################################
¤ > ¤ PROCEDURE PROC_GETCOLWIDTHS(
¤ > ¤
¤ > ¤ RC1 OUT Omwb_emulation.globalPkg.RCT1)
¤ > ¤
¤ > ¤ AS
¤ > ¤
¤ > ¤ StoO_selcnt INTEGER;
¤ > ¤
¤ > ¤ StoO_error INTEGER;
¤ > ¤
¤ > ¤ StoO_rowcnt INTEGER;
¤ > ¤
¤ > ¤ StoO_crowcnt INTEGER := 0;
¤ > ¤
¤ > ¤ StoO_fetchstatus INTEGER := 0;
¤ > ¤
¤ > ¤ StoO_errmsg VARCHAR2(255);
¤ > ¤
¤ > ¤ StoO_sqlstatus INTEGER;
¤ > ¤
¤ > ¤ BEGIN
¤ > ¤
¤ > ¤ OPEN RC1 FOR
¤ > ¤
¤ > ¤ SELECT *
¤ > ¤
¤ > ¤ FROM GridColWidths;
¤ > ¤
¤ > ¤ END PROC_GETCOLWIDTHS;
¤ > ¤
¤ > ¤ #######################################################
¤ >
¤ >
¤ > I thing you need to define your output parameter as a REF_CURSOR in your package or stored
¤ > procedure. The following article uses a package:
¤ >
¤ > HOW TO: Return an Oracle Ref Cursor to a .NET DataReader Object by Using the .NET Managed Provider
¤ > for Oracle
¤ > http://support.microsoft.com/default.aspx?scid=kb;en-us;322160&Product=ado
¤ >
¤ >
¤ > Paul ~~~ (e-mail address removed)
¤ > Microsoft MVP (Visual Basic)
¤ Hi Paul,
¤ I am using Oracle 8.1.7.
¤ Using toad I tried to alter parameter to REF_CURSOR and also REF CURSOR.
¤ Both won't compile. Former says 'identifier REF_CURSOR must be declared'
¤ Latter says identifier CURSOR must be identified even though it colours
¤ REF and CURSOR blue indicating two key words.
¤
¤ The examples that I have seen seem to use predefined types.
¤ eg 'Using .NET FrameWork Data Provider for Oracle to Improve .NET
¤ Application Performance ' by Gregory Leake July 2002.
¤ He is using 'P_Cursor OUT NILES_TYPES.NILE_CURSOR'
¤
¤ What I did was convert my SQL SERVER database to Oracle using the Oracle
¤ Migration Workbench.
¤
¤ I have just chased through the Oracle model generated and Type RCT1 is a
¤ global predefined type of REF CURSOR.
¤ "TYPE RCT1 IS REF CURSOR;/*new weak cursor definition*/"
¤ I then created a new package in the current instance with a type of RCT2
¤ because I thought maybe that global type wasn't in scope somehow for the
¤ dot net call.
¤ But it has made no difference.
¤
¤ I am truly stuffed here. I can't believe it is this difficult to use a
¤ .NET Client to simply retrieve a Datareader.
¤ I have 73 stored procs to get working.
¤ thanks anyway
¤ regards
¤ bob


I would highly recommend using a package and the example in the MS KB article. I'm using the same
version of Oracle as you and it works just fine for me.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Paul said:
¤ Paul Clement wrote:
Click to expand...
I would highly recommend using a package and the example in the MS KB article. I'm using the same
version of Oracle as you and it works just fine for me.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
Click to expand...
Hi Paul,
Thanks for your reply.
Problem solved.
Had to name Oracle.Command parameter the same as the parameter in the
procedure.
I am used to SQL server where as long as the type is right you can have
whatever name you like.
regards
Bob
 
¤ Paul Clement wrote:
¤ >
¤ > ¤ Paul Clement wrote:
¤
¤ >
¤ >
¤ > I would highly recommend using a package and the example in the MS KB article. I'm using the same
¤ > version of Oracle as you and it works just fine for me.
¤ >
¤ >
¤ > Paul ~~~ (e-mail address removed)
¤ > Microsoft MVP (Visual Basic)
¤ Hi Paul,
¤ Thanks for your reply.
¤ Problem solved.
¤ Had to name Oracle.Command parameter the same as the parameter in the
¤ procedure.
¤ I am used to SQL server where as long as the type is right you can have
¤ whatever name you like.
¤ regards
¤ Bob

I saw your other post. I had completely forgotten about that little gotcha.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top