Can't understand warnings in this simple PL/SQL function

Question:
I am using Oracle 10g with SqlDeveloper. When I execute the following code, it says

"FUNCTION wafadar compiled Warning: execution completed with warning"

create or replace function wafadar
return varchar2(10)
is

cursor c1 is
SELECT employee_id,first_name FROM employees WHERE department_id=50 ;

begin
for i in c1
loop
dbms_output.put_line(i.first_name);
end loop;
return 'hello';
end;

SHOW ERRORS at the end is also not showing the warnings. Why are the warnings there?


Answer:
The answer to this is actually quite simple. When you declare a function you only specify the type of the data returned by the function. Not the length. Therefore the function header should be:-

create or replace function wafadar return varchar2.

This is quite a common mistake that is not restricted to beginners and is not always easy to spot - because the function header looks right! This rule also applies to parameters - this is invalid:

create or replace function wafadar (x varchar2(10)) return varchar2

The parameter should be declared to be of type varchar2 without specifying the length, like so:-

create or replace function wafadar (x varchar2) return varchar2

Although the preceding function header is valid it is good practice to anchor parameter type declarations to that of a column as in the following example:-

create or replace function wafadar (dept_in departments.department_name%type) return varchar2 is.

In this case the parameter dept_in is declarfed to be of the same type as the department_name column in the departments table.

As for the reason why SHOW ERRORS didn't display anything that is because SHOW ERRORS only works in SQL*Plus. In Oracle SQL Developer, compilation errors are shown in a separate tab entitled "Compiler - Log" which might not be visible, however you can use the LOG option on the VIEW menu to display it.

For more help with PL/SQL see our series of tutorials starting at http://www.asktheoracle.net/plsql-tutorial.html.

Also refer to the Oracle Pl/SQL Language Reference manual on Oracle's web site.