dbms_output in function from package does not showed

Question

I need to know what employee_ids has been updated in a function from a package for the HR Oracle Schema. For this I tried to execute a dbms_output function inside another function in this package but the output only shows the return value fron the function, not the output.

What is the right form to get this?, here is the code from the package and its function:

create or replace package body pac_busqueda_empleados7 as
function f_cambia_datos_empl( p_nom in employees.first_name%TYPE)
return number is 
i NUMBER := 0;
v_val_nom employees.first_name%type;
ids dbms_sql.number_table;
begin
v_val_nom := p_nom;
update employees set first_name = 'NN' where employees.first_name=v_val_nom return employees.employee_id bulk collect into ids;
return 1;
for i in ids.first .. ids.last loop
    dbms_output.put_line('Updated: ' || ids(i));
  end loop;
exception when others then
return 0;
end;
end pac_busqueda_empleados7;

And this is the execution block from this package function.

declare
v_nombre employees.first_name%type;
v_resultado number;
begin
v_nombre := 'Nombre';
v_resultado:=pac_busqueda_empleados7.f_cambia_datos_empl(v_nombre);
dbms_output.put_line(v_resultado);
end;

the output expected should be:

1
Updated: 100
Updated: 101
Updated: 102
Updated: 103
.
.
.
etc.

But for now is only:

1

PL/SQL procedure successfully completed.

Answer 1

Look carefully at this part of your code:

begin
v_val_nom := p_nom;
update employees set first_name = 'NN' where employees.first_name=v_val_nom return employees.employee_id bulk collect into ids;
return 1;
for i in ids.first .. ids.last loop
    dbms_output.put_line('Updated: ' || ids(i));
end loop;

You return from the function before the loop starts.
Place return statement after the loop and you should get the desired output.