oracleclient exception...

[Dan =o\)]

<as posted in the microsoft.public.dotnet.languages.adonet newsgroup>

Hey guys,

I'm stumped on this... Today I was asked to look into coverting our ODBC
access to a Oracle database to the client that microsoft wrote and have come
up against the lovely

"ORA-06550: line 1, column 7: PLS-00306: wrong number or types of
arguments in call to 'MSG_DATAREAD' "

message... All I'm doing is converting the code from the stuff that's using
OdbcConnection/Command etc to OracleConnection/Command etc so I know the
stored procedure's all work fine.



Here's the code that's failing:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' get a reader for the record set with this msg number
Dim dbcommand As OracleCommand = New OracleCommand
dbcommand.Connection = dbData
dbcommand.CommandType = CommandType.StoredProcedure
dbcommand.CommandText = "MSG_DATAREAD"

' get a cursor out
Dim param As OracleParameter = New OracleParameter
param = dbCommand.Parameters.Add("RC1", OracleClient.OracleType.Cursor)
param.Direction = ParameterDirection.Output

' pass in the msg number
Dim msgnumParam As OracleParameter = New OracleParameter
msgnumParam = dbcommand.Parameters.Add("MsgNumber", OracleType.VarChar,
50)
msgnumParam.Direction = ParameterDirection.Input
msgnumParam.Value = NullToBlank(m_szCurrentMsgNumber)

Dim dbReader As OracleDataReader = dbcommand.ExecuteReader

While dbreader.Read

' do some stuff with the data I get back

End While

dbreader.Close()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


Here's the Stored Procedure it's calling:

/*
Stored procedure MSG_DATAREAD
-------------------------------------------
*/
(
MsgNumber IN VARCHAR2 DEFAULT NULL,
RC1 IN OUT Omwb_emulation.globalPkg.RCT1
)
AS
MsgNumber_ VARCHAR2(50) := MsgNumber;
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
NULL;
/*[SPCONV-ERR(6)]set DATEFORMAT) Manual conversion required*/

OPEN RC1 FOR

SELECT MsgNumber
, MsgItem
, Section
, Field
, Data
FROM MSG_Data
WHERE MsgNumber = MSG_DATAREAD.MsgNumber_
ORDER BY MsgItem ;
END MSG_DATAREAD;

 

[Guest]

try the change below ...
in the SP make the ref cursor an OUT parameter not an IN OUT parameter

hth

guy

<as posted in the microsoft.public.dotnet.languages.adonet newsgroup>

Hey guys,

I'm stumped on this... Today I was asked to look into coverting our ODBC
access to a Oracle database to the client that microsoft wrote and have come
up against the lovely

"ORA-06550: line 1, column 7: PLS-00306: wrong number or types of
arguments in call to 'MSG_DATAREAD' "

message... All I'm doing is converting the code from the stuff that's using
OdbcConnection/Command etc to OracleConnection/Command etc so I know the
stored procedure's all work fine.



Here's the code that's failing:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' get a reader for the record set with this msg number
Dim dbcommand As OracleCommand = New OracleCommand
dbcommand.Connection = dbData
dbcommand.CommandType = CommandType.StoredProcedure
dbcommand.CommandText = "MSG_DATAREAD"

' get a cursor out
Dim param As OracleParameter = New OracleParameter
param = dbCommand.Parameters.Add("RC1", OracleClient.OracleType.Cursor)
param.Direction = ParameterDirection.Output

' pass in the msg number
Dim msgnumParam As OracleParameter = New OracleParameter
msgnumParam = dbcommand.Parameters.Add("MsgNumber", OracleType.VarChar,
50)
msgnumParam.Direction = ParameterDirection.Input
msgnumParam.Value = NullToBlank(m_szCurrentMsgNumber)

Dim dbReader As OracleDataReader = dbcommand.ExecuteReader

While dbreader.Read

' do some stuff with the data I get back

End While

dbreader.Close()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


Here's the Stored Procedure it's calling:

/*
Stored procedure MSG_DATAREAD

\\\
---> RC1 IN OUT Omwb_emulation.globalPkg.RCT1
RC1 OUT Omwb_emulation.globalPkg.RCT1

)
AS
MsgNumber_ VARCHAR2(50) := MsgNumber;
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
NULL;
/*[SPCONV-ERR(6)]set DATEFORMAT) Manual conversion required*/

OPEN RC1 FOR

SELECT MsgNumber
, MsgItem
, Section
, Field
, Data
FROM MSG_Data
WHERE MsgNumber = MSG_DATAREAD.MsgNumber_
ORDER BY MsgItem ;
END MSG_DATAREAD;