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

uestion:

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?

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

Average Rating starstarstarstarstar

Click here to add your own comments

Nov 10, 2016
Rating
starstarstarstarstar
PL/SQL function return type should not be constrained
by: John

The answer to this is actually quite simple. When you declare a PL/SQL 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 PL/SQL 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 in the Oracle database.

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 the series of tutorials starting at http://www.asktheoracle.net/plsql-tutorial.html.

Also refer to the Oracle Pl/SQL Language Reference manual on OTN.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.