Home / Questions / 545915
Accepted
AprendizEntusiasta
Asked: 2022-07-12 13:47:23 +0800 CST 2022-07-12 13:47:23 +0800 CST

How to resolve ORA-06503: PL/SQL: Function returned without value issue?

  • 772

I have created a package with the search_order function that receives F_Order as a parameter

create or replace PACKAGE BUSCAR_ORDER_PKG IS 
FUNCTION buscar_order (F_Order VARCHAR2)
RETURN VARCHAR2;
END BUSCAR_ORDER_PKG;

The function is executed as such but when the parameter is empty it returns the error ORA-06503: PL/SQL: Function returned without value and from there it does not happen even when it has an exception.

create or replace PACKAGE BODY BUSCAR_ORDER_PKG IS
    FUNCTION buscar_order(F_Order VARCHAR2)
    RETURN VARCHAR2 IS    
    
    l_order  varchar2(150);

        Begin
                Select Flex_Value
                    Into l_Order
                From Flex_Values
                Where Flex_Value_Id = (Select Flex_Value_Id 
                                            From Flex_Values
                                            Where Flex_Value_Name = 'PROJECT')
                    And Flex_Value = NVL(F_Order,'X')
                    And Enabled_Flag = 'Y'
                    And Trunc(Nvl(End_Date_Active,Sysdate)) >= Trunc(Sysdate);
        Exception
        WHEN OTHERS THEN   
        l_Order := null;
       
        end buscar_order;          
END  BUSCAR_ORDER_PKG;
plsql

1 Answers

  1. Best Answer

    You need to return either the value, which you get in l_Order, or nullin case of exception.

    For code rigor, and for clarity, I recommend that you always respect the syntax in which you declare your variables.

    In some contexts and programs names are case sensitive, and declaring variables in one way and using them in another (not case sensitive for example) could get you in trouble.

    This is how it should work:

    create or replace PACKAGE BODY BUSCAR_ORDER_PKG IS
    FUNCTION buscar_order(F_Order VARCHAR2)
    RETURN VARCHAR2 IS    
    
    l_Order  varchar2(150);

        Begin
                Select Flex_Value
                    Into l_Order
                From Flex_Values
                Where Flex_Value_Id = (Select Flex_Value_Id 
                                            From Flex_Values
                                            Where Flex_Value_Name = 'PROJECT')
                    And Flex_Value = NVL(F_Order,'X')
                    And Enabled_Flag = 'Y'
                    And Trunc(Nvl(End_Date_Active,Sysdate)) >= Trunc(Sysdate);
                    return l_Order;
        Exception
        WHEN OTHERS THEN   
        return null;
       
        end buscar_order;          
END  BUSCAR_ORDER_PKG;
    • 1