We have a table with a column type varchar2 and my customer is storing a DATE into that column. The table is based on a Oracle Form and there is a calendar attached to the front-end. The Form Users select/enter and save date without any issues. The date store into the table in a YYYY/MM/DD 00:00:00 format and display from the front-end as DD-MON-YYYY. (so far so good).

Recently, I designed a PL SQL package interface and have a DBlink with another oracle based system. The DBlink table has a column with type varchar2 and store the date into the following format: YYYYMMDD. I need to transform the source column date and populate into the target tables. I have tried and used different date formats but no luck. how can i achieve my goal. Please help to resolve the issue. Thanks in advance!.

Source (DBLink external table)
Column type varchar2 and date format is YYYYMMDD

Target table
Column type varchar2 and date format YYYY/MM/DD 00:00:00 and display as DD-MON-YYYY.