oracle function input with more than one possible type

I am building a function to compare 2 input variables:

CREATE FUNCTION compare_num (first IN NUMBER, second in NUMBER) 
    RETURN VARCHAR2 AS 
BEGIN
    -- write compare request into audit log database  table
    if first = second then return 0; else return 1; end if; 
END TESTFUNCTION;

then I will need to build another for date:

CREATE FUNCTION compare_date (first IN date, second in date) 
    RETURN VARCHAR2 AS 
BEGIN
    -- write compare request into audit log database  table
    if first = second then return 0; else return 1; end if; 
END TESTFUNCTION;

as you can see the 2 functions are identical except for the input type difference.

What I would like to do is build one function so I only need to write the function body once, something like:

CREATE FUNCTION compare (first IN <date OR number OR varchar2>, second in <date OR number OR varchar2>) 
    RETURN VARCHAR2 AS 
BEGIN
    -- write compare request into audit log database table
    if first = second then return 0; else return 1; end if; 
END TESTFUNCTION;

so that if I need to update “write to log” logic I don’t have to update in two places.

Is this possible?

I tried use ‘anytype’ as input types but compilation failed at “first = second” check.

New contributor

Overloading

The proper way to handle this is with “overloading”. But Oracle supports overloaded procs/functions only in packages, so you need to wrap it in a package:

CREATE OR REPLACE PACKAGE pkg_test
AS
  function compare_data (first in varchar2, second in number) return varchar2;
  function compare_data (first in varchar2, second in date) return varchar2;
END;

CREATE OR REPLACE PACKAGE BODY pkg_test
AS
  function compare_data (first in varchar2, second in number) 
    return varchar2
  as
    var_second_date date;
  begin
    -- convert second to a date then call the other overload.
    var_second_date := second +...... [whatever math you need];

    RETURN compare_data(first,var_second_date);
  end;

  function compare_data (first in varchar2, second in date) 
    return varchar2
  as
  begin
    -- now do stuff and return something 
  end;
END pkg_test;

When you call pkg_test.compare_date(...,...) Oracle will detect the datatype of the literal (or bind) you are passing in for both parameters and will find the function definition in the package header that matches those datatypes… so if your second parameter is a number, it will call the first specification. If your second parameter is a date, it automatically knows to call the second one instead.

Then all you need to do to consolidate code is do whatever is number-specific and then convert it to the primary datatype (date) and call the other overload to do the main work.. again, Oracle automatically knows to call the second function rather than recursively call itself because of the datatypes being supplied in the parameters.

Generic varchar2 parameter (risky)

You could, of course, have only one function and not need packages at all if you simply use generic varchar2 parameters, but that’s generally considered bad programming because it does not enforce the real datatype validity of the parameter values. A user could pass in total junk and you’re going to end up with datatype conversion errors being thrown. You should not rely on implicit datatype conversions, and you certainly don’t want to be in the business of trying to do your own datatype inspection on a string and do conditional conversions based on what you “think” it is.

Multiple (optional) strongly-typed parameters

A third and final solution that is better than the generic varchar2 method and not as elegant as the overload is to have additional optional parameters and programming test for non-nullity and use whichever one has a non-null value. Then at least you can stay strongly-typed:

CREATE FUNCTION compare_num (first IN NUMBER, 
                             second_number in NUMBER := NULL, 
                             second_date IN date := NULL) 
    RETURN VARCHAR2 
AS 
  var_second_date date;
BEGIN
  IF second_number IS NOT NULL
  THEN
    var_second_date := second_number+..... [conversion here];
  ELSE
    var_second_date := second_date;
  END IF;

  
    -- do stuff using var_second_date
END compare_num;

The downside is that there is no enforcement that only one of the types have a value (they might both, then which do you use?), and since they now must be optional, the function could be called without any of them. So more checks and error conditions have to be built in when you do this.

So, various options, but the cleanest and most programmatically secure method is the overload approach.

Well, what you could do is to rely on Oracle’s capabilities of implicit datatype conversion and create a function which accepts parameters of a varchar2 datatype.

SQL> create or replace function compare_data
  2    (first in varchar2, second in varchar2)
  3  return number
  4  as
  5  begin
  6      if first = second then return 0; else return 1; end if;
  7  end compare_data;
  8  /

Function created.

Let’s try it:

SQL> select compare_data(1, 2) result from dual;

    RESULT
----------
         1

SQL> select compare_data('A', 'A') result from dual;

    RESULT
----------
         0

SQL> select compare_data(date '2024-10-28', date '2021-02-13') result from dual;

    RESULT
----------
         1

SQL>

Seems to be working; though, I prefer not to rely on implicit datatype conversion. If you want to take control over it, I guess you’d have to create separate functions. They could even be overloaded, i.e. look and have the same name – the only difference would be parameters’ datatype.

Filed under: Kiến thức lập trình - @ 12:46

Thẻ: oracle-databasefunctionsqldatatypes

oracle function input with more than one possible type

I am building a function to compare 2 input variables:

CREATE FUNCTION compare_num (first IN NUMBER, second in NUMBER) 
    RETURN VARCHAR2 AS 
BEGIN
    -- write compare request into audit log database  table
    if first = second then return 0; else return 1; end if; 
END TESTFUNCTION;

then I will need to build another for date:

CREATE FUNCTION compare_date (first IN date, second in date) 
    RETURN VARCHAR2 AS 
BEGIN
    -- write compare request into audit log database  table
    if first = second then return 0; else return 1; end if; 
END TESTFUNCTION;

as you can see the 2 functions are identical except for the input type difference.

What I would like to do is build one function so I only need to write the function body once, something like:

CREATE FUNCTION compare (first IN <date OR number OR varchar2>, second in <date OR number OR varchar2>) 
    RETURN VARCHAR2 AS 
BEGIN
    -- write compare request into audit log database table
    if first = second then return 0; else return 1; end if; 
END TESTFUNCTION;

so that if I need to update “write to log” logic I don’t have to update in two places.

Is this possible?

I tried use ‘anytype’ as input types but compilation failed at “first = second” check.

New contributor

Overloading

The proper way to handle this is with “overloading”. But Oracle supports overloaded procs/functions only in packages, so you need to wrap it in a package:

CREATE OR REPLACE PACKAGE pkg_test
AS
  function compare_data (first in varchar2, second in number) return varchar2;
  function compare_data (first in varchar2, second in date) return varchar2;
END;

CREATE OR REPLACE PACKAGE BODY pkg_test
AS
  function compare_data (first in varchar2, second in number) 
    return varchar2
  as
    var_second_date date;
  begin
    -- convert second to a date then call the other overload.
    var_second_date := second +...... [whatever math you need];

    RETURN compare_data(first,var_second_date);
  end;

  function compare_data (first in varchar2, second in date) 
    return varchar2
  as
  begin
    -- now do stuff and return something 
  end;
END pkg_test;

Generic varchar2 parameter (risky)

Multiple (optional) strongly-typed parameters

CREATE FUNCTION compare_num (first IN NUMBER, 
                             second_number in NUMBER := NULL, 
                             second_date IN date := NULL) 
    RETURN VARCHAR2 
AS 
  var_second_date date;
BEGIN
  IF second_number IS NOT NULL
  THEN
    var_second_date := second_number+..... [conversion here];
  ELSE
    var_second_date := second_date;
  END IF;

  
    -- do stuff using var_second_date
END compare_num;

So, various options, but the cleanest and most programmatically secure method is the overload approach.

Well, what you could do is to rely on Oracle’s capabilities of implicit datatype conversion and create a function which accepts parameters of a varchar2 datatype.

SQL> create or replace function compare_data
  2    (first in varchar2, second in varchar2)
  3  return number
  4  as
  5  begin
  6      if first = second then return 0; else return 1; end if;
  7  end compare_data;
  8  /

Function created.

Let’s try it:

SQL> select compare_data(1, 2) result from dual;

    RESULT
----------
         1

SQL> select compare_data('A', 'A') result from dual;

    RESULT
----------
         0

SQL> select compare_data(date '2024-10-28', date '2021-02-13') result from dual;

    RESULT
----------
         1

SQL>

Filed under: Kiến thức lập trình - @ 12:46

Thẻ: oracle-databasefunctionsqldatatypes

Thiết kế website giá rẻ

Danh mục

oracle function input with more than one possible type

Overloading

Generic varchar2 parameter (risky)

Multiple (optional) strongly-typed parameters

oracle function input with more than one possible type

Overloading

Generic varchar2 parameter (risky)

Multiple (optional) strongly-typed parameters