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

2
votes

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.

2
oraclestored-procedures
How are you invoking it? – Vasan
Through a client application – sunny babau
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. – Gary Myers
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. – Littlefoot
The error message refers to an incorrect call to 'CLEAR_INDEX', so we need to see that call, not just the procedure signature. – William Robertson

2 Answers

1
votes

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
0
votes

Mine is a similar case, however, a different call to a function, which has return type as Boolean and the input to the function is varchar2. So, this is what I am doing:

Declare
v_ret1 varchar2(1000);
v_ret BOOLEAN;
Begin
v_ret := CASE WHEN SEI_PROCESS_MF_MNL_INVC_PKG.f_process_mf_mnl_invc(v_ret1) = 1 THEN 'TRUE' 
              WHEN SEI_PROCESS_MF_MNL_INVC_PKG.f_process_mf_mnl_invc(v_ret1) = 0 THEN 'FALSE' 
              END;
dbms_output.put_line(v_ret); 
end;

I just wanted to see output of v_ret so that I can use that for further decision making. If v_ret is of type Boolean, the output should be either "TRUE" or "FALSE".