PLS-00306: wrong number or types of arguments in call to a Oracle SP

Question

I'm trying to execute a below SP and it throws be the below error:

CREATE OR REPLACE PROCEDURE denodo.CLEAR_INDEX
   (   INDEX_NAME1 IN VARCHAR2,
       INDEX_NAME2 IN VARCHAR2,
       IT_WORKED   OUT BOOLEAN ) as
BEGIN
    IT_WORKED := FALSE;
    EXECUTE IMMEDIATE 'drop index ' || INDEX_NAME1;
    EXECUTE IMMEDIATE 'drop index ' || INDEX_NAME2;
    IT_WORKED := TRUE;
EXCEPTION
    WHEN OTHERS THEN
       IT_WORKED := FALSE;
END CLEAR_INDEX;

CLEAR_INDEX#0 [JDBC ROUTE] [ERROR] Received exception with message 'ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'CLEAR_INDEX' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

What is going on here? appreciate your help.

Solution

This error you generally face when you deal with BOOLEAN datatype as OUT parameter and you try to print it or do any operation with any other inbuilt Oracle packages. You cannot directly use BOOLEAN datatype in dbms_output.put_line or any other DBMS package. For instance,if you want to print the OUT parameter you need to use sys.diutil.bool_to_int. See below example which demonstrate the error you faced when you try to execute as below:

DECLARE
  inx VARCHAR2(100):='ABC';
  var BOOLEAN;  
BEGIN
  CLEAR_INDEX(INDEX_NAME1=>inx ,IT_WORKED =>var);    
  dbms_output.put_line(var);
END;

You face the issue:

ORA-06550: line 6, column 3:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action

To overcome such issue you must execute as below:

DECLARE
  inx VARCHAR2(100):='ABC';
  var BOOLEAN;
  var1 varchar2(10);
BEGIN
  CLEAR_INDEX(INDEX_NAME1=>inx ,IT_WORKED =>var);  

  var1:=CASE WHEN (sys.diutil.bool_to_int(var)) = 1 THEN 'TRUE'
             WHEN (sys.diutil.bool_to_int(var)) = 0 THEN  'FALSE'
             END;  
    dbms_output.put_line(var1);
END;

Output:

anonymous block completed

Comments

[Vasan]

How are you invoking it?

[sunny babau]

Through a client application

[Gary Myers]

I'll hazard a guess that you are either calling the procedure with the wrong number of arguments (ie not 3) or at least one of the arguments is the wrong type (not varchar2 for the first two or not boolean for the third). It might be both though.

[Littlefoot]

Without actually seeing how you called that procedure, it is difficult to guess. One of parameters is OUT - it means that you have to provide it while calling the procedure, so you'd have to create a variable which accepts procedure's OUT parameter value and do something with it. If you missed to do that, it might be a reason for wrong number of arguments.

[William Robertson]

The error message refers to an incorrect call to 'CLEAR_INDEX', so we need to see that call, not just the procedure signature.