1

This is the function which I designed. It got complied successfully

CREATE OR REPLACE FUNCTION "F_CHECK"
(
    p_id IN VARCHAR2
    p_name IN VARCHAR2)RETURN VARCHAR2
    is 
    v_id VARCHAR2;
    v_name VARCHAR2;
    cnt pls_integer;
    BEGIN
        IF id IS NULL THEN
            RETURN NULL;
        END IF;
        SELECT COUNT(*) INTO cnt from emp_new where id = p_id;
        IF (cnt > 0) THEN
            SELECT id, name INTO v_id, v_name from emp_new where id=p_id;
        IF (v_id is null and p_id is null and v_name is null and p_name is null) THEN
            return NULL;    
        ELSE
            IF (v_name =trunc(p_name)) then
                return NULL;
            else
                insert into employees values(p_id,p_name,sysdate);
            end if;
        end if;
     end if;

    exception
    when DUP_VAL_ON_INDEX THEN
    raise application error (-20001, 'NAME EXISTS UNDER DIFFERENT ID');
    END F_CHECK;

But I'm not getting the expected result, when I execute the function

select F_CHECK(1,1) from dual;

ERROR I'M getting is:

SQL EEROR: ORA-06503: PL/SQL : FUNCTION RETURNED WITHOUT VALUE
Improve this question
9
  • This shouldn't compile successfully, there is an extra parenthesis Commented Sep 3, 2014 at 7:35
  • Try to add when no_data_found then and check if the function reaches this exception. Commented Sep 3, 2014 at 7:43
  • It's also missing an end if, and where that fits is important; if it is after the final return null then it could cause this error, though it looks like it should be after the select. Please show us your actual, successfully-compiled function. Commented Sep 3, 2014 at 8:17
  • 2
    Also, if you're only ever returning null, why is this a function at all - why not make it a procedure? Commented Sep 3, 2014 at 9:12
  • @AlexPoole In the final if loop I have mentioned the insert statement Commented Sep 3, 2014 at 9:31

3 Answers 3

Reset to default
2

You must return a value when the execution flow reaches (around line 22)

...
else
  insert into employees values(p_id,p_name,sysdate);
end if;
...

In this case, the function does not return a value which is expected by the caller, hence the error.

You can instruct the PL/SQL compiler to warn you about such code (and other problems) with ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL'; prior to compilation.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

@Nyffenegger: Yeah I got you..But what is the solution, so that I get the desired result set
@ABDUL What do you want to be returned after that insert?
@yammy that insert statement will do my job and I don't know what I should be returning after that
@ABDUL since you're returning null in all other cases and are not interested in a returned value for the insert case, you probably don't need a function at all and a procedure (without return value) would do.
1

One of the possible causes of exceptions that you will get when you run this code is: If your select into didn't return a value, you will get an exception, an nu-handled exception

Even though you have a return NULL in the end of the function, but still you need to catch all the exceptions that might occur

The area where you need to take care of is:

SELECT id, name INTO v_id, v_name from emp_new where id=p_id;

Surround it by Begin ... EXCEPTION WHEN NO_DATA_FOUND THEN ... END; block

Also, your insert statement might cause an exception if you violated some constraints on your table, so you might need to handle that also

Have a look at Error Handling in Oracle

Below is your updated code, also fixed code regarding the extra parenthesis

Edited

CREATE OR REPLACE FUNCTION F_CHECK
(
    P_ID    EMP_NEW.ID%TYPE,
    P_NAME  EMP_NEW.NAME%TYPE
)
RETURN VARCHAR2 IS 
    V_ID      EMP_NEW.ID%TYPE;
    V_NAME    EMP_NEW.NAME%TYPE;
    CNT       NUMBER;
BEGIN

    --IF ID IS NULL THEN
    -- What is ID ?? is it P_ID
    --Changed below
    IF P_ID IS NULL THEN
        RETURN 'Error: Add Value For Id';
    END IF;

    IF P_NAME IS NULL THEN
        RETURN 'Error: Add Value For Name';
    END IF;

    SELECT 
        COUNT(*) INTO CNT FROM EMPLOYEES 
    WHERE ID = P_ID;

    IF (CNT > 0) THEN

        SELECT 
            ID, NAME INTO V_ID, V_NAME 
        FROM 
            EMP_NEW 
        WHERE 
           ID=P_ID;

        ----------------------------------------------------------------------------------------
        --IF V_ID IS NULL AND P_ID IS NULL AND V_NAME IS NULL AND P_NAME IS NULL THEN
        --The code above will always evaluate to False because P_ID at this stage is not null!
        --Also, if P_Name must have a value, check it at the begining along with the ID, not here
        ----------------------------------------------------------------------------------------

        IF V_ID IS NULL AND V_NAME IS NULL THEN
            RETURN 'Error: Not found details';      
        ELSE
            --Details are found
            IF (V_NAME = TRUNC(P_NAME)) THEN
                RETURN 'Name already assigned to this id';
            ELSE --Its a new name, add it
                INSERT INTO EMPLOYEES VALUES(P_ID,P_NAME,SYSDATE);
                --Make sure your columns are only three and in the correct order as the Values specified
            END IF;

        END IF;

    END IF;

    RETURN 'Ok, added';

    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
            raise application error (-20001, 'NAME EXISTS UNDER DIFFERENT ID');
END F_CHECK;
Improve this answer

9 Comments

An unhandled exception will propagate back to the caller; it won't cause a 'returned without value' exception. And the select isn't going to get no_data_found because of the cnt check, unless you're really unlucky with the timing and someone deletes your record between the two select statements executing.
You are correct, basically, the code in the question shouldn't throw any exceptions regarding the select into codes, but if it wasn't compiled successfully, it will go crazy, I'm saying that because there is an extra right parenthesis in the function body, if the function was correctly compiled, and changed later to a version that contains syntax errors, Oracle will still use the latest version of it, which will throw an exception upon use of the function. the extra begin exception end code block won't hurt also, I've added it for educational purposes :)
And also, I've just seen you commit about the missing end if. good catch
But if it isn't compiled successfully then it won't execute at all, it won't fall back to the last good version? The object will be invalid and calling it will get ORA-06575, package or function is in an invalid state... Anyway, the function in the question isn't valid or compilable so anything we say is speculation really *8-)
@simsim I tried with your codings its throwing an complication error
|
1

A function MUST ALWAYS RETURN a VALUE of proper data type. Else, it would throw the following error:

ORA-06503: PL/SQL: Function returned without value
Cause: A call to PL/SQL function completed, but no RETURN statement was executed.
Action: Rewrite PL/SQL function, making sure that it always returns a value of a proper type.

Read ORA-06503: PL/SQL: Function returned without value

DB version : 11.2.0.2.0

Let’s see the various scenarios of this error :

Without a RETURN statement in the function body and without exception handler(most stupid way):

SQL> set serveroutput on    
SQL> CREATE OR REPLACE FUNCTION f_test(i_val NUMBER)
2 RETURN NUMBER AS
3 o_val NUMBER;
4 BEGIN
5 SELECT 100 / i_val
6 INTO o_val
7 FROM DUAL;
8 END;
9 /
Function created

SQL> select f_test(100) from dual;
select f_test(100) from dual
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "F_TEST", line 8

Now, in the above code, the mathematical logic was correct, hence there was no SQL error to override the PL/SQL error. Let’s see how ORA-01476 will override the ORA-06503 error.

SQL> CREATE OR REPLACE FUNCTION f_test(i_val NUMBER)
2 RETURN NUMBER AS
3 o_val NUMBER;
4 BEGIN
5 SELECT 100 / i_val
6 INTO o_val
7 FROM DUAL;
8 END;
9 /
Function created

SQL> select f_test(0) from dual;
select f_test(0) from dual
ORA-01476: divisor is equal to zero
ORA-06512: at "F_TEST", line 5

Well, that’s quite obvious, isn’t it?

  1. Without a RETURN statement in the exception handler(most common mistake):
    
    SQL> CREATE OR REPLACE FUNCTION f_test(i_val NUMBER)
    2 RETURN NUMBER AS
    3 o_val NUMBER;
    4 BEGIN
    5 SELECT 100 / i_val
    6 INTO o_val
    7 FROM DUAL;
    8
    9 RETURN o_val;
    10
    11 EXCEPTION
    12 WHEN OTHERS THEN
    13 NULL;
    14 END;
    15 /
    Function created

    SQL> select f_test(0) from dual;
    select f_test(0) from dual
    ORA-06503: PL/SQL: Function returned without value
    ORA-06512: at "F_TEST", line 14

Now let’s put a RETURN statement at required places and the code should work fine without any error:

SQL> CREATE OR REPLACE FUNCTION f_test(i_val NUMBER)
2 RETURN NUMBER AS
3 o_val NUMBER;
4 BEGIN
5 SELECT 100 / i_val
6 INTO o_val
7 FROM DUAL;
8
9 RETURN o_val;
10
11 EXCEPTION
12 WHEN OTHERS THEN
13 DBMS_OUTPUT.PUT_LINE('Came inside Exception handler');
14 RETURN 0;
15 END;
16 /
Function created

SQL> select f_test(0) from dual;

F_TEST(0)
0
Came inside Exception handler

Bottom line is that :

  • A function MUST ALWAYS RETURN a value of proper datatype, no matter from the body or exception.
  • We must do something with the error not just return junk. We must RAISE/log error and handle it, do something about the error so that underlying process has no impact.
  • Lastly, not to forget, EXCEPTION WHEN OTHERS THEN NULL; –> is itself a bug in the code waiting for its chance to break the code.
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.