Error when calling PL/SQL function

Question

I have created a PL/SQL function that is supposed to be adding a given number of records to a table with the help of new_dept_new sequence that I've created earlier. The function compiled successfully. But I'm getting an error when calling the function:

BEGIN
dbms_output.put_line(myfunction3(3, 'OPERATIONS', 'NEW YORK'));
END;

Error report -
ORA-06503: PL/SQL: Function returned without value

The function:

CREATE OR REPLACE FUNCTION myfunction3(
records_count_in IN NUMBER, dept_name_in IN dept.dname%TYPE, loc_name_in IN dept.loc%TYPE)

RETURN INTERVAL DAY TO SECOND
IS 
thestart  TIMESTAMP := CURRENT_TIMESTAMP;
stopwatch INTERVAL DAY TO SECOND;
records_count NUMBER := records_count_in;
dept_name dept.dname%TYPE := dept_name_in;
loc_name dept.loc%TYPE := loc_name_in;

BEGIN
    FOR i IN 1..records_count LOOP
        INSERT INTO dept VALUES(new_dept_new.nextval, dept_name || TO_CHAR(new_dept_new.currval), loc_name || TO_CHAR(new_dept_new.currval));
COMMIT;
END LOOP;
    stopwatch := CURRENT_TIMESTAMP - thestart;
    DBMS_OUTPUT.PUT_LINE('Time for processing function: ');
RETURN stopwatch;
EXCEPTION WHEN OTHERS 
    THEN dbms_output.put_line('ERROR Processing Request for Department: ' || dept_name_in);
END;

Why is the function returned without value?

EDIT: After receiving feedback from the comments I've edited the function and now the values are inserted into the table and the timestamp value is returned. I also want an application error to be raised if 0 rows were inserted.

So I did the following:

CREATE OR REPLACE FUNCTION myfunction3(
records_count_in IN NUMBER, dept_name_in IN dept.dname%TYPE, loc_name_in IN dept.loc%TYPE)

RETURN INTERVAL DAY TO SECOND
IS 
thestart  TIMESTAMP := CURRENT_TIMESTAMP;
stopwatch INTERVAL DAY TO SECOND;
records_count NUMBER := records_count_in;
dept_name dept.dname%TYPE := dept_name_in;
loc_name dept.loc%TYPE := loc_name_in;

BEGIN
    FOR i IN 1..records_count LOOP
        INSERT INTO dept (deptno, dname, loc) 
        VALUES(new_dept_new.nextval, dept_name || TO_CHAR(new_dept_new.currval), loc_name || TO_CHAR(new_dept_new.currval));
    IF SQL%ROWCOUNT = 0 THEN
    raise_application_error (-20001, 'ERROR Processing Request for Department: ' || dept_name_in);
    END IF;
END LOOP;
    stopwatch := CURRENT_TIMESTAMP - thestart;
    DBMS_OUTPUT.PUT_LINE('Time for processing function: ');
RETURN stopwatch;
END;

However if I'm calling the function like this and zero records are inserted, I'm not getting my custom error message.

BEGIN dbms_output.put_line(myfunction3(0, 'DEV_OPS', 'NEW YORK')); END;

How can I fix this?

Answer 1

Nothing seems to be wrong with your functionexcept for the exception block.I was able to simulate the scenario with the table and column names you used and it worked perfectly fine. If there are other issues like missing table,sequence it would not have compiled for you. So, the only reason for it to fail is your insert statement. It could be either constraint violation, length of value exceeds the column length limit etc.

You can make a slight change in your exception block by adding a raise statement.This will help you to identify the actual error in your calling program.

EXCEPTION WHEN OTHERS 
    THEN dbms_output.put_line('ERROR Processing Request for Department: ' || dept_name_in);
RAISE;

Additionally, Never run insert statements like

INSERT INTO dept VALUES (...)

It is not a good coding practice and difficult to know which order it is going to insert especially when there are many columns.

Always mention the columns explicitly

INSERT INTO dept ( dept_id,dname,loc)  VALUES (..,..,..);

Answer 2

There is some exception in your function that causes your function to hit the exception block. There is no return statement in your exception block and hence the error. Add a RAISE statement to see what's causing the exception.