OracleClient exception...

  • Thread starter Thread starter Dan =o\)
  • Start date Start date
D

Dan =o\)

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;
 
I know the parameters are the wrong way round too, but I've tried the other
way, and have also altered the stored proc so the cursor is first, then had
them in the same order in the code... same error every time.

What is the correct way for this btw?

Thanks

Dan.
 
What version of Oracle? OracleClient works very well with 8i, okay with 9i
and does not work with 10g. The downloadable ODP.NET (otn.oracle.net) is a
better option for Oracle databases.

In your case, you have an IN OUT in your sproc, but it is only Output in
your code. When you return a cursor (REF_CURSOR is all I have used), it
becomes a DataReader and can fill DataTables. You do have to declare it, but
the norm, for me, has always been:

CursorName OUT Types.Cursor_Type

It worked well in the apps I was writing at the time.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside the box!
*************************************************
Dan =o) said:
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;
Click to expand...
 
Greg,

Thanks for that, I'll have a look at getting that change. Thing is I'm on a
different tier to the Oracle stuff (which is 8i btw =o) ) so I'll see what
we can do to get that changed to check the result.

Thanks.

Daniel.

Cowboy (Gregory A. Beamer) said:
What version of Oracle? OracleClient works very well with 8i, okay with 9i
and does not work with 10g. The downloadable ODP.NET (otn.oracle.net) is a
better option for Oracle databases.

In your case, you have an IN OUT in your sproc, but it is only Output in
your code. When you return a cursor (REF_CURSOR is all I have used), it
becomes a DataReader and can fill DataTables. You do have to declare it,
but the norm, for me, has always been:

CursorName OUT Types.Cursor_Type

It worked well in the apps I was writing at the time.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside the box!
*************************************************
Dan =o) said:
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;
Click to expand...
Click to expand...
 
can u check the parameter name u r using
is its name same as the name in oracle procedure,
i got the same error with 8i but when i changed the name of parameter
it worked, though its strange try it if it works
 
Back
Top