CODEWITHSUNDEEP
sqloracle-databaseplsql
Danon
Danon

Reputation: 37

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

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 ?)

Upvotes: 0

Views: 377

Answers (1)

astentx
astentx

Reputation: 6751

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

Upvotes: 1

Related Questions