Is it allowed to write a SQL function in Oracle to return more than data types?

Question

I am trying to understand about SQL functions in Oracle, I was wondering if it is possible to write a SQL function which can return a Number or a Varchar2 based on the conditions in the program.

Answer 1

You can SORT OF do that using a return type of ANYDATA. But the calling query or PL/SQL block would have to use a CASE statement to evaluate the return type in order to do anything with it. Very simple demonstration below.

    CREATE OR REPLACE FUNCTION AnyData_Test
      (
       p_Value_In            IN      VARCHAR2
      )
      RETURN AnyData
AS
      ad_Test_Var            ANYDATA;
BEGIN
      CASE
            WHEN p_Value_In = 'Varchar2' THEN ad_Test_Var := SYS.ANYDATA.ConvertVarchar2('VC2');
            WHEN p_Value_In = 'Number'   THEN ad_Test_Var := SYS.ANYDATA.ConvertNumber(123);
            ELSE                              ad_Test_Var := SYS.ANYDATA.ConvertDate(SYSDATE);
      END CASE;
      RETURN ad_Test_Var;
END AnyData_Test;
/

SELECT
      AnyData.GetTypeName(AnyData_Test('Varchar2'))    AS Return_Type_V
     ,AnyData.AccessVarchar2(AnyData_Test('Varchar2')) AS Return_Type_V_Value
     --
     ,AnyData.GetTypeName(AnyData_Test('Number'))      AS Return_Type_N
     ,AnyData.GetTypeName(AnyData_Test('Nope'))        AS Return_Type_D
FROM dual
;
      
SELECT
      CASE AnyData.GetTypeName(AnyData_Test('Varchar2'))
            WHEN 'SYS.VARCHAR2' THEN AnyData.AccessVarchar2(AnyData_Test('Varchar2'))
            ELSE 'Other'
      END AS Quick_Test
FROM dual;