I'm running below code snippet, where dbms_ouput is not printing anything and just showing function compiled successfully in SQL Developer.
set serveroutput on size 30000;
create or replace function check_status (p_user_name in varchar2) return number is
begin
DBMS_OUTPUT.PUT_LINE('Yoo');
end;
/
where below code snippet is printing. I wounder why its not printing inside function.
set serveroutput on size 30000;
begin
DBMS_OUTPUT.PUT_LINE('Yoo');
end;
/
You have compiled the function, but you haven't called it. The code in the function will only be run when the function is called, so it is correct for it to not show any output at compile time.
Your function also needs to return a value; at the moment calling it would get:
select check_status('test') from dual;
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "MYSCHEMA.CHECK_STATUS", line 4
Yoo
which does show the output, but also errors.
So your function needs to return something:
create or replace function check_status (p_user_name in varchar2) return number is
begin
DBMS_OUTPUT.PUT_LINE('Yoo');
return 42;
end;
/
Function CHECK_STATUS compiled
select check_status('test') from dual;
CHECK_STATUS('TEST')
--------------------
42
Yoo
Also note that anyone calling the function will only see that Yoo if they have enabled output in their session or client. As you can't control that you shouldn't rely on it. It's mostly useful for debugging.
set serveroutput onbefore calling the function too. But as there is noreturnin the function, it will error at runtime anyway...