PLSQL - is there a generic data type?-CodePudding

I would like to archive some situation-based type conversions in PLSQL. I guess, it is just not possible but, let's see: Can I do something like the below:

function my_type( p_type in variant) return varchar2
as
begin
    
    if is_date(p_type) and p_type = trunc(p_type ,'dd') 
    then 
        return to_string(p_type,'some_setting'); 
    end if;
    -- and so on for numbers and other date-type cases
    
end;

I am also happy about other ideas. Oracle would convert the data into varchar2 anyways. I am just not happy about how it does it. Are there maybe some flexible data settings that you can recommend?

Best, Peter

CodePudding user response：

ANYDATA

Example:

create table t_anydata (
  nsq number(19,0) primary key,
  anyd anydata
);

insert into t_anydata(nsq, anyd) values (1, sys.anyData.convertNumber(5) ) ;
insert into t_anydata(nsq, anyd) values (2, sys.anyData.convertDate(to_date('01-10-2019', 'dd-mm-yyyy')) ) ;
insert into t_anydata(nsq, anyd) values (3, sys.anyData.convertVarchar2('test varchar') ) ;
insert into t_anydata(nsq, anyd) values (4, sys.anyData.convertChar('c') ) ;
insert into t_anydata(nsq, anyd) values (5, sys.anyData.convertBDouble(3.14159) ) ;
insert into t_anydata(nsq, anyd) values (6, sys.anyData.ConvertTimestamp(TIMESTAMP '1997-01-31 09:26:50.12') ) ;
insert into t_anydata(nsq, anyd) values (7, sys.anyData.ConvertTimestampTZ(TIMESTAMP '1997-01-31 09:26:50.12') ) ;
insert into t_anydata(nsq, anyd) values (8, sys.anyData.ConvertTimestampLTZ(TIMESTAMP '1997-01-31 09:26:50.12') ) ;
insert into t_anydata(nsq, anyd) values (9, sys.anyData.ConvertCollection( sys.odcivarchar2list( 'abcd', 'efgh' ) ) ) ;
insert into t_anydata(nsq, anyd) values (10, sys.anyData.ConvertCollection( sys.odcinumberlist( 1.1, 2.2, 3.3 ) ) ) ;

commit ;

SELECT nsq, 
    CASE sys.anyData.gettypename(anyd) 
        WHEN 'SYS.NUMBER' THEN
            TO_CHAR(SYS.ANYDATA.accessNumber(anyd))
        WHEN 'SYS.VARCHAR2' THEN
            SYS.ANYDATA.accessVarchar2(anyd)
        WHEN 'SYS.CHAR' THEN
            SYS.ANYDATA.accessChar(anyd)
        WHEN 'SYS.DATE' THEN
            TO_CHAR(SYS.ANYDATA.accessDate(anyd), 'DD-MON-YYYY HH24:MI:SS')
        WHEN 'SYS.TIMESTAMP' THEN
            TO_CHAR(SYS.ANYDATA.accessTimestamp(anyd), 'DD-MON-YYYY HH24:MI:SS')
        WHEN 'SYS.TIMESTAMP_WITH_TIMEZONE' THEN
            TO_CHAR(SYS.ANYDATA.accessTimestampTZ(anyd), 'DD-MON-YYYY HH24:MI:SS')
        WHEN 'SYS.TIMESTAMP_WITH_LTZ' THEN
            TO_CHAR(SYS.ANYDATA.accessTimestampLTZ(anyd), 'DD-MON-YYYY HH24:MI:SS')
        WHEN 'SYS.ODCIVARCHAR2LIST' THEN
            'COLLECTION'
    END as value, 
    sys.anyData.gettypename(anyd), vsize(anyd) 
FROM t_anydata 
;

CodePudding user response：

There are two approaches:

Use if ... then ... else ... statements and perform processing in the branches. It makes the code less readable and maintainable, but it doesn't require extra objects. Then you may use a unified container of the data (for example, anydata).
Use overloading and divide responsibility: perform datatype-dependent processing and serialization in each individual instance of the datatype and then bring the results together. It's possible in Oracle as long as it allows function overloading in packages.

Note that it may cause unexpected results for varchar2 parameter, because it may cause implicit conversion for supported datatypes for which there's no overloaded function exist.

create package pkg_my_to_varchar
as
  function f_prepare(p_number in number) return varchar2;
  function f_prepare(p_date in date) return varchar2;
  function f_prepare(p_timestamp_tz in timestamp with time zone) return varchar2;
  function f_prepare(p_varchar in varchar2) return varchar2;

  function f_all_together(
    p1 varchar2,
    p2 varchar2 default null,
    p3 varchar2 default null,
    p4 varchar2 default null,
    p5 varchar2 default null,
    p6 varchar2 default null,
    p7 varchar2 default null,
    p8 varchar2 default null,
    p9 varchar2 default null
  ) return varchar2;

end pkg_my_to_varchar;/

create package body pkg_my_to_varchar
as
  function f_prepare(
    p_number in number
  ) return varchar2
  as
  begin
    return 'I''ve processed the number: ' || to_char(p_number, 'RN');
  end;

  function f_prepare(
    p_date in date
  ) return varchar2
  as
  begin
    return 'I''ve processed the date: ' || to_char(p_date, 'YYYY-MM-DD HH24:MI:SS "and some stuff"');
  end;

  function f_prepare(
    p_timestamp_tz in timestamp with time zone
  ) return varchar2
  as
  begin
    return 'I''ve processed the timestamp with TZ: ' || to_char(p_timestamp_tz, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM');
  end;

  function f_prepare(
    p_varchar in varchar2
  ) return varchar2
  as
  begin
    return 'I''ve processed the string: ' || p_varchar;
  end;


  function f_all_together(
    p1 varchar2,
    p2 varchar2 default null,
    p3 varchar2 default null,
    p4 varchar2 default null,
    p5 varchar2 default null,
    p6 varchar2 default null,
    p7 varchar2 default null,
    p8 varchar2 default null,
    p9 varchar2 default null
  ) return varchar2
  as
  begin
    return p1 || chr(10) || p2 || chr(10) || p3 || chr(10) || p4 || chr(10) || p5 || chr(10) || p6 || chr(10) || p7 || chr(10) || p8 || chr(10) || p9;
  end;
end pkg_my_to_varchar;/

with a(n, d, ts, vc) as (
  select
    123.456,
    sysdate,
    systimestamp at time zone ' 07:30',
    'qwerty'
  from dual
)
select
  a.*
  , pkg_my_to_varchar.f_all_together(
      pkg_my_to_varchar.f_prepare(n),
      pkg_my_to_varchar.f_prepare(d),
      pkg_my_to_varchar.f_prepare(ts),
      pkg_my_to_varchar.f_prepare(vc)
  ) as concat_all
from a

N	D	TS	VC	CONCAT_ALL
123.456	16-JAN-23	16-JAN-23 23.13.00.685956 07:30	qwerty	I've processed the number: CXXIII I've processed the date: 2023-01-16 15:43:00 and some stuff I've processed the timestamp with TZ: 2023-01-16 23:13:00.685956000 07:30 I've processed the string: qwerty

fiddle

CodePudding user response：

If you are trying to pass different data types to a function then it is not possible to have many different versions of a function with different data types; however, you can pass the ANYDATA data type:

CREATE FUNCTION ANYDATA_TO_STRING(
  p_anydata IN ANYDATA
) RETURN VARCHAR2
IS
  v_typeid      PLS_INTEGER;
  v_anytype     ANYTYPE;
  v_result_code PLS_INTEGER;
BEGIN
  v_typeid := p_anydata.GetType( typ => v_anytype );
  CASE v_typeid
  WHEN DBMS_TYPES.TYPECODE_NUMBER THEN
    DECLARE
      v_value NUMBER;
    BEGIN
      v_result_code := p_anydata.GetNumber( v_value );
      RETURN TO_CHAR( v_value );
    END;
  WHEN DBMS_TYPES.TYPECODE_VARCHAR2 THEN
    DECLARE
      v_value VARCHAR2(4000);
    BEGIN
      v_result_code := p_anydata.GetVarchar2( v_value );
      RETURN v_value;
    END;
  WHEN DBMS_TYPES.TYPECODE_DATE THEN
    DECLARE
      v_value DATE;
    BEGIN
      v_result_code := p_anydata.GetDate( v_value );
      RETURN TO_CHAR( v_value, 'YYYY-MM-DD HH24:MI:SS' );
    END;
  END CASE;
  RETURN NULL;
END;
/

Then:

WITH table_name (value) AS (
  SELECT ANYDATA.ConvertDate(SYSDATE)     FROM DUAL UNION ALL
  SELECT ANYDATA.ConvertNumber(42)        FROM DUAL UNION ALL
  SELECT ANYDATA.ConvertVarchar2('Hello') FROM DUAL
)
SELECT ANYDATA_TO_STRING(value)
FROM   table_name;

Outputs:

ANYDATA_TO_STRING(VALUE)
2023-01-16 14:48:37
42
Hello

fiddle