ORA-06550 error (me too) when I call a SP...

[Jéjé]

Hi,

I received the ORA-06550 error when I try to execute a stored procedure in
Oracle.
( ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments
in call to .....)

My stored procedure use 10 Varchar2 IN parameters.
And I'm using 10 oracleparameters in my VB.NET application.

OracleCommand1.Connection = OracleConnection1

OracleCommand1.CommandText = "MyPck.updateData"

OracleCommand1.CommandType = System.Data.CommandType.StoredProcedure

OracleCommand1.Parameters.Add(New
System.Data.OracleClient.OracleParameter("V_STUDENT_NO",
System.Data.OracleClient.OracleType.VarChar, 4000))

OracleCommand1.Parameters.Add(New
System.Data.OracleClient.OracleParameter("V_SCHOOL_CODE",
System.Data.OracleClient.OracleType.VarChar, 4000))

OracleCommand1.Parameters.Add(New
System.Data.OracleClient.OracleParameter("V_STOPCODEIN",
System.Data.OracleClient.OracleType.VarChar, 4000))

OracleCommand1.Parameters.Add(New
System.Data.OracleClient.OracleParameter("V_STOPROUTEIN",
System.Data.OracleClient.OracleType.VarChar, 4000))

OracleCommand1.Parameters.Add(New
System.Data.OracleClient.OracleParameter("V_STOPTIMEIN",
System.Data.OracleClient.OracleType.VarChar, 4000))

OracleCommand1.Parameters.Add(New
System.Data.OracleClient.OracleParameter("V_STOPCODEOUT",
System.Data.OracleClient.OracleType.VarChar, 4000))

OracleCommand1.Parameters.Add(New
System.Data.OracleClient.OracleParameter("V_STOPROUTEOUT",
System.Data.OracleClient.OracleType.VarChar, 4000))

OracleCommand1.Parameters.Add(New
System.Data.OracleClient.OracleParameter("V_STOPTIMEOUT",
System.Data.OracleClient.OracleType.VarChar, 4000))

OracleCommand1.Parameters.Add(New
System.Data.OracleClient.OracleParameter("V_STOPTYPE",
System.Data.OracleClient.OracleType.VarChar, 4000))

OracleCommand1.Parameters.Add(New
System.Data.OracleClient.OracleParameter("V_AIDCODE",
System.Data.OracleClient.OracleType.VarChar, 4000))



my Oracle SP:

CREATE OR REPLACE PACKAGE MyPck AS
procedure updateData
(
V_STUDENT_NO IN varchar2,
V_SCHOOL_CODE IN varchar2,
V_STOPCODEIN IN varchar2,
V_STOPROUTEIN IN varchar2,
V_STOPTIMEIN IN varchar2,
V_STOPCODEOUT IN varchar2,
V_STOPROUTEOUT IN varchar2,
V_STOPTIMEOUT IN varchar2,
V_STOPTYPE IN varchar2,
V_AIDCODE IN varchar2
);
end;


I've tried to put this SP in and out of a package in Oracle without any
changes.

I've another SP with an IN Number argument and this procedure works fine.

The Oracle login is correct.



I don't understand what is the problem.

I've found many other users with the same problem, but I've not found a
solution!

Thanks for your help !!!



Jerome.

 

[Carlos J. Quintero]

Not sure about your error but you may want to take a look at this simple
code that works:

SQL*Plus Script:

CREATE OR REPLACE PACKAGE MYPACKAGE IS
PROCEDURE MYPROC(P1 IN VARCHAR2,P2 OUT VARCHAR2);
END;
CREATE OR REPLACE PACKAGE BODY MYPACKAGE IS
PROCEDURE MYPROC(P1 IN VARCHAR2,P2 OUT VARCHAR2) IS
BEGIN
P2:=P1;
END;
END;

VB.NET code:

Dim objOracleConnection As OracleConnection
Dim objOracleCommand As OracleCommand
Dim objOracleParameterP1 As OracleParameter
Dim objOracleParameterP2 As OracleParameter

Try

objOracleConnection = New
System.Data.OracleClient.OracleConnection()
objOracleConnection.ConnectionString = "Data Source=XXX;User
ID=YYY;Password="ZZZ"
objOracleConnection.Open()

objOracleCommand = objOracleConnection.CreateCommand()
objOracleCommand.CommandText = "MYPACKAGE.MYPROC"
objOracleCommand.CommandType = Data.CommandType.StoredProcedure

objOracleParameterP1 = objOracleCommand.CreateParameter()
objOracleParameterP1.ParameterName = "P1"
objOracleParameterP1.DbType = System.Data.DbType.AnsiString
objOracleParameterP1.Direction = Data.ParameterDirection.Input
objOracleParameterP1.Size = 4000
objOracleParameterP1.Precision = 0
objOracleParameterP1.Value = "TEST"
objOracleCommand.Parameters.Add(objOracleParameterP1)

objOracleParameterP2 = objOracleCommand.CreateParameter()
objOracleParameterP2.ParameterName = "P2"
objOracleParameterP2.DbType = System.Data.DbType.AnsiString
objOracleParameterP2.Direction = Data.ParameterDirection.Output
objOracleParameterP2.Size = 4000
objOracleParameterP2.Precision = 0
objOracleCommand.Parameters.Add(objOracleParameterP2)

objOracleCommand.ExecuteNonQuery()

MessageBox.Show(objOracleParameterP2.Value.ToString)

Catch objException As Exception
MessageBox.Show(objException.ToString)
Finally
If Not (objOracleCommand Is Nothing) Then
objOracleCommand.Dispose()
End If
objOracleConnection.Close()
End Try

HTH,

Carlos Quintero