Oracle convert table to interval day(3) to second

Question

I have the following table definition, with data that creates fine but I like to convert it to a more generic format but I'm having issues. Can someone point me in the right direction

CREATE  TABLE partition_retention
(
  seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
   TABLE_NAME VARCHAR2(30),
DAYS NUMBER(6),
CONSTRAINT
partition_retention_pk primary key (table_name)); 
/

INSERT into partition_retention(TABLE_NAME, DAYS) 
        WITH data as (
          select 'T1', 0
from dual union all
         select 'T3', 15
from dual union all
        select 'T4', 10
from dual union all
       select 'T5', 5
from dual)
        SELECT * from data;
/

-- having problem creating 

CREATE TABLE PARTITION_RETENTION AS (
    TABLE_NAME AS VARCHAR2(30)
    RETENTION DAY AS INTERVAL DAY(3) TO SECOND(0) 
);

Answer 1

The three AS keywords are invalid (create table .. as select .. is valid, but you aren't doing that); you are missing a comma; and you have an unquoted column name with a space.

Correcting those things, this works:

CREATE TABLE PARTITION_RETENTION (
    TABLE_NAME VARCHAR2(30),
    RETENTION_DAY INTERVAL DAY(3) TO SECOND(0) 
);

Your inserts will then have to insert interval values, not simple numbers, obviously.

db<>fiddle

how can I add a CONSTRAINT on the table to ensure the day>0 and the time is always 0

You can add separate constraints to check both things:

CREATE TABLE PARTITION_RETENTION (
    TABLE_NAME VARCHAR2(30),
    RETENTION_DAY INTERVAL DAY(3) TO SECOND(0),
    CONSTRAINT CHK_NON_ZERO_DAYS CHECK (
        RETENTION_DAY > INTERVAL '0' DAY
    ),
    CONSTRAINT CHK_WHOLE_DAYS CHECK (
        EXTRACT(HOUR FROM RETENTION_DAY) = 0
        AND EXTRACT(MINUTE FROM RETENTION_DAY) = 0
        AND EXTRACT(SECOND FROM RETENTION_DAY) = 0
    )
);

to give slightly different errors (via the constraint names) - db<>fiddle - or combine them into one.

I'm not sure this is really any clearer or easier than having a number column constrained to integers between 1 and 999.