Convert the string date type into date type while creating a table

I am trying to convert the string date type that retrieved from a column. Below is the scenario

#input

date_str

02/08/24
21/09/2023
25.02.16
null
null

I need to convert above column into a new column with below format

formated_col
02-aug-24
21-sep-23
25-feb-16
null
null

'date_str' column is the part of cte table when I used below query, just to test the conversion, it works for eg:

with test_table as (select * from basetable)
select t.*, 
to_char(to_date(t.date_str, 'DD/MM/YYYY'), 'DD-MON-YY' ) as formatted_date
from test_table t

this create the expected output like this

formatted_date

02-aug-24
21-sep-23
25-feb-16
null
null

However when I am trying to create a table out of this, it

like

create table basetable as
with test_table as (select * from basetable)
select t.*, 
to_char(to_date(t.date_str, 'DD/MM/YYYY'), 'DD-MON-YY' ) as formatted_date
from test_table t

It throws below error

day of month must be between 1

i am not able to understand where exactly is the issue?

Don't store dates as strings - store dates as DATEs. If you want a formatted string the you can either do that when you query the data or you can add a virtual column to the table.

When you parse your strings, if you have two-digit years then don't use YYYY as the format mask as 16 will get converted to the year 0016 and not 2016. You want to use RR or RRRR.

CREATE TABLE basetable (
  date_str VARCHAR2(10),
  dt       DATE,
  formatted_date VARCHAR2(9) GENERATED ALWAYS AS (
     CAST(TO_CHAR(dt, 'DD-MON-YY', 'NLS_DATE_LANGUAGE=English') AS VARCHAR2(9))
  )
);

Then you can insert the data using:

INSERT INTO basetable (date_str, dt)
SELECT date_str,
       TO_DATE(date_str DEFAULT NULL ON CONVERSION ERROR, 'DD/MM/RR')
FROM   input_table;

Then the table will contain:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT * FROM basetable

fiddle