Wrong quantity or types of arguments error while using dbms_output.put_line in Oracle PL/SQL

Question

I have a problem with the command dbms_output.put_line().

I have a variable pres_name and I would like to print the value on the screen. Therefore I use dbms_output.put_line() but the command doesn't accept this type of Variable but all sites that I found they use it like dbms_output.put_line(varchar variable). But I get this warning:

Fehler(15,5): please-00306: Falsche Anzahl oder Typen von Argumenten in Aufruf von 'PUT'

In English:

wrong quantity or types of arguments on the invocation of put

I use Oracle sql developer.

The code:

create or replace
procedure ex10_1
(
 pr_name varchar
)
as
cursor pres is select vice_pres_name from admin_pr_vp;
begin
  open pres;
  for pr_name in pres loop
    dbms_output.put_line(pr_name);
  end loop;
end;

ps: the code is not perfect yet.

Are you typing varchar, too? You need just dbms_output.put_line(your_variable). Try a simpler thing first: dbms_output.put_line('hello, world'). — 9000
– 9000, Commented Dec 16, 2011 at 17:19
no write it right and the comand works if i write a text as argument — Dennis
– Dennis, Commented Dec 16, 2011 at 17:23

Shannon Severance · Accepted Answer · 2011-12-16 18:54:15Z

<snip>
for pr_name in pres loop
    dbms_output.put_line(pr_name);
end loop;
<snip>

In PL/SQL a cursor for loop implicitly declares the loop variable to be a record type matching a row of the cursor. So, within the loop pr_name is a record with a single field, vice_pres_name. That field has type vice_pres_name%TYPE. This inner pr_name shadows the outer pr_name argument. DBMS_OUTPUT.PUT_LINE takes a varchar2 and PL/SQL is not able to implicitly convert the pr_name record.

Here is an example anonymous that uses an cursor for loop and the record syntax to print the values returned by a cursor:

SQL>     declare
  2          cursor pres is select 'A' as vice_pres_name
  3              from dual union all select 'B' from dual;
  4      begin <<ex10_1>>
  5          for pr_name in pres loop
  6              dbms_output.put_line(pr_name.vice_pres_name);
  7          end loop;
  8      end ex10_1;
  9  /
A
B

PL/SQL procedure successfully completed.

Also note, that a cursor for loop implicitly opens and closes the cursor for you.

Collectives™ on Stack Overflow

Wrong quantity or types of arguments error while using dbms_output.put_line in Oracle PL/SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related