CODEWITHSUNDEEP
c#.netoracle-databasestored-proceduresoracle11g
iblis
iblis

Reputation: 18

Oracle sp call error with .NET (PLS-00306)

I have a very simple stored procedure as below:

create or replace procedure WF_ALI is
ali number;
begin
ali :=2;
end;

and a .NET code as below:

GetDatabaseConnection();
sqlString = "WF_ALI";
command = new OracleCommand(sqlString, connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add("ali", OracleType.Number);
command.Parameters["ali"].Value = 4;
int32 rowsAffected = command.ExecuteNonQuery;

However, it returns the error

PLS-00306: wrong number or types of arguments in call to WF_ALI.

I also tried it with Toad for Oracle. When I execute that sp, it returns the same error.

I can connect to the database. There is no problem with that. I can even obtain a result of SELECT statement. Please help.

Upvotes: 0

Views: 680

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

Your procedure is not declared to take any parameters so you can't pass a number in. If you want to pass in a parameter ali, your procedure declaration would need to be something like

CREATE OR REPLACE PROCEDURE wf_ali( ali IN NUMBER )
IS
BEGIN
  <<do something>>
END;

If you want to pass in and change the parameter, you'd have to declare it as IN OUT rather than IN

CREATE OR REPLACE PROCEDURE wf_ali( ali IN OUT NUMBER )
IS
BEGIN
  ali := 2;
END;

Generally, though, it's a good idea to prefix parameter names so that there is no risk that a parameter name would conflict with a column in a table, i.e.

CREATE OR REPLACE PROCEDURE wf_ali( p_ali IN OUT NUMBER )
IS
BEGIN
  p_ali := 2;
END;

That will, of course, require that you change the parameter name in your C# code.

Upvotes: 2

Related Questions