How to define a DEFAULT value for a CUSTOM datatype in Oracle SQL?

Question

For example inside a package there are two custom data types:

-- A single time period with start date and end date.
TYPE time_period IS OBJECT(
  start_period Datetime,
  end_period Datetime
);
-- A collection of defined time periods.
TYPE time_periods IS TABLE OF time_period;

How to define a default value for the second custom datatype inside the function parameter?

-- The main function.
FUNCTION Foo (bar_periods IN time_periods DEFAULT ?)

astentx · Accepted Answer · 2021-07-15 11:23:48Z

1

You need to use type constructor, passing arguments of initial properties to it:

create or replace package p_test as
  TYPE time_period is record (
    start_period Date,
    end_period Date
  );

  TYPE time_periods IS TABLE OF time_period;

  function f (
    p in time_periods
      default time_periods(
        time_period(date '2021-01-01', date '2021-12-31'),
        time_period(date '2020-01-01', date '2020-12-31')
      )
  ) return number;
end;
/

db<>fiddle here

edited Jul 15, 2021 at 11:23

answered Jul 15, 2021 at 11:18

astentx

6,7812 gold badges19 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

How to define a DEFAULT value for a CUSTOM datatype in Oracle SQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related