Wrong number or types of arguments in call to Stored Procedure

Wrong number or types of arguments in call to Stored Procedure error occurs when the identified subprogram call cannot be matched to any declaration for that subprogram name. The name of the subprogram may be misspelt, a parameter may be of the incorrect datatype, the declaration may be wrongly positioned in the block structure, or the declaration may be incorrect. Make sure the subprogram name is spelled correctly and declared. Also, double-check that its call is valid, that its parameters are of the correct datatype, and that its declaration is appropriately positioned in the block structure if it isn’t a built-in function.



The Problem

The error will be thrown if the wrong number of arguments are passed to a stored procedure call, or if the parameters are passed with the invalid data type. As stated in the stored procedure, the stored procedure must contain valid arguments. It’s possible that the name of the stored process is misspelt. Make sure the stored procedure is declared and spelt appropriately.

Error

Error starting at line : 46 in command -
begin
    myprocedure('test');
end;
Error report -
ORA-06550: line 2, column 5:
PLS-00306: wrong number or types of arguments in call to 'MYPROCEDURE'
ORA-06550: line 2, column 5:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:


Solution 1

An IN parameter and an OUT parameter are used to build a user specified stored procedure. To execute the stored procedure, the stored procedure call needs give two parameters. The whole stored procedure PL/SQL code block is shown in the following example. The stored procedure will be defined and created in the Oracle database using the following example.

Stored Procedure

CREATE OR REPLACE Procedure myprocedure
   ( nameIn IN varchar2, msgOut OUT varchar2 )

IS
    name varchar2(100);
    msg varchar2(100);
begin
    dbms_output.put_line('nameIn = ' || nameIn);
    dbms_output.put_line('name = ' || name);
    dbms_output.put_line('msg = ' || msg);
end;

Output

Procedure MYPROCEDURE compiled


Solution 2

A parameter may be of the incorrect datatype, the declaration may be incorrectly positioned in the block structure, or the name of the stored procedure may be misspelt. Check that the name of the stored procedure is spelled correctly and that it is declared. Double-check that its call is valid, that its parameters are of the proper datatype, and that its declaration is in the correct block structure.

Error

begin
    myprocedure('test');
end;

Solution

declare 
    msgvar varchar2(100);
begin
    myprocedure('test', msgvar);
end;


Solution 3

The error will be thrown if the datatype of the argument does not match the stated data type of the stored procedure parameters. According to the stored procedure declaration, the data type must be valid and correct. In the example below, boolean datatype value is passed to the string data type. The boolean value can not be converted to the string value. The error will be thrown

Error

declare 
    msgvar varchar2(100);
begin
    myprocedure(true, msgvar);
end;

Solution

declare 
    msgvar varchar2(100);
begin
    myprocedure('test', msgvar);
end;


Solution 4

The error will be thrown if no argument is passed to the stored procedure that expects one or more arguments. Check the stored procedure declaration and find the number of arguments expected in the stored procedure. Add the arguments to the stored procedure call to resolve the error

Error

declare 
    msgvar varchar2(100);
begin
    myprocedure();
end;

Solution

declare 
    msgvar varchar2(100);
begin
    myprocedure('test', msgvar);
end;


Leave a Reply