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

August 23, 2023

The SQL below results in the Oracle 11g error: PLS-00306: wrong number or types of arguments in call to 'PRINT'. But it looks to me like my print procedure expects 1 varchar2 argument and that it’s exactly what I am passing to it as an argument.

CREATE TABLE employees (full_name VARCHAR2(64))
//

INSERT INTO employees (full_name) VALUES ('John')
//

INSERT INTO employees (full_name) VALUES ('Paul')
//

INSERT INTO employees (full_name) VALUES ('George')
//

INSERT INTO employees (full_name) VALUES ('Ringo')
//

CREATE OR REPLACE PROCEDURE print (
  v_string IN VARCHAR2
) IS
BEGIN
  dbms_output.put_line(v_string);
END print;
//

DECLARE
  v_string VARCHAR2(64);
BEGIN
    FOR v_string IN (SELECT DISTINCT full_name
                            FROM  employees) LOOP
        print (v_string);
    END LOOP;
END;
//

I can reproduce the issue in SQLFiddle: http://sqlfiddle.com/#!4/c0e80e/6

What am I missing?

Thanks

>Solution :

Exactly, you’ve done it wrong. Should’ve been

SQL> BEGIN
  2      FOR v_string IN (SELECT DISTINCT full_name
  3                              FROM  employees) LOOP
  4          p_print (v_string.full_name);
  5      END LOOP;
  6  END;
  7  /
John
Paul
George
Ringo

PL/SQL procedure successfully completed.

SQL>

Why? Because you declared local variable and used cursor FOR loop (whose record shares the same name with the local variable). What you passed to print procedure was a cursor variable, not a local variable.

As documentation says:

The cursor FOR LOOP statement implicitly declares its loop index as a record variable of the row type that a specified cursor returns, and then opens a cursor.

Syntax is: for record in ..., where record represents

name for the loop index that the cursor FOR LOOP statement implicitly declares as a %ROWTYPE record variable of the type that cursor or select_statement returns.