Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table acct_hrs (wid, hours_type_cd, wrk_date, tech_id, tech_type, some_ts, "Some_TsTz", some_intervalym, some_intervalds) as
select 1, 2, sysdate, 42, 'ABC', sys_extract_utc(systimestamp), systimestamp, interval '5' year, interval '1 2:3' day to minute from dual
union all
select 1.5, -2.75, sysdate, 42, 'ABC', sys_extract_utc(systimestamp), systimestamp, interval '2' month, interval '0 0:0:2' day to second from dual;
2 rows affected

declare
  -- your target table name and query, could be a procedure arguments later
  l_table_name all_tables.table_name%type := dbms_assert.simple_sql_name('ACCT_HRS');
  l_statement varchar2(4000) := 'select * from acct_hrs';
  -- for initial parse and execute
  l_refcursor sys_refcursor;
  -- variables for dbms_sql use
  l_c pls_integer;
  l_col_cnt pls_integer;
  l_desc_t dbms_sql.desc_tab4;
  -- local variables to hold column info as required
  l_column_name all_tab_cols.column_name%type;
  l_data_type varchar2(20);
  l_varchar varchar2(4000);
begin
  -- For now set NLS to something useful so we can default all the results to strings
  execute immediate q'^alter session set nls_numeric_characters='.,'^';
  execute immediate q'^alter session set nls_date_format='SYYYY-MM-DD HH24:MI:SS'^';
  execute immediate q'^alter session set nls_timestamp_format='SYYYY-MM-DD HH24:MI:SS.FF9'^';
  execute immediate q'^alter session set nls_timestamp_tz_format='SYYYY-MM-DD HH24:MI:SS.FF9 TZR'^';

  -- open ref cursor for the statement
  open l_refcursor for l_statement;

  -- convert ref cursor to dbms_sql cursor
  l_c := dbms_sql.to_cursor_number(l_refcursor);

  dbms_sql.describe_columns3(c=>l_c, col_cnt=>l_col_cnt, desc_t=>l_desc_t);

  -- define all columns as strings; this will end up with implicit conversion
  -- of dates etc. using NLS settings (hence settng above), and won't handle LOBs;
  -- so should be finessed based on actual data types really...
  for i in 1..l_col_cnt loop
    dbms_sql.define_column(c=>l_c, position=>i, column=>l_varchar, column_size=>4000);
  end loop;

  -- fetch each row in turn
  while dbms_sql.fetch_rows(c=>l_c) > 0 loop
    -- generate the insert statement and column list
    dbms_output.put('insert into ' || l_table_name || '(');
    for i in 1..l_col_cnt loop
      if i > 1 then
        dbms_output.put(',');
      end if;
      dbms_output.put('"' || l_desc_t(i).col_name || '"');
    end loop;
    -- and start the values clause
    dbms_output.put(') values (');
  
    for i in 1..l_col_cnt loop
      -- get the column value for this row (again, as string...)
      dbms_sql.column_value(l_c, i, l_varchar);
      -- write out with explicit conversion, with delimiter after first column
      if i > 1 then
        dbms_output.put(',');
      end if;
      case
        when l_varchar is null then
          dbms_output.put('null');
        when l_desc_t(i).col_type = 2 then
          dbms_output.put(l_varchar);
        when l_desc_t(i).col_type in (12, 180, 181, 231) then
          -- date type 12 will be implicitly cast; can do that explicitly
          dbms_output.put(q'^timestamp '^' || l_varchar || q'^'^');
        when l_desc_t(i).col_type = 182 then
          dbms_output.put(q'^interval '^' || l_varchar || q'^' year to month^');
        when l_desc_t(i).col_type = 183 then
          dbms_output.put(q'^interval '^' || l_varchar || q'^' day to second^');
        -- any other explcit handling if you have other data types
        -- ...
        else
          dbms_output.put(q'^'^' || replace(l_varchar, q'^'^', q'^''^') || q'^'^');
      end case;
    end loop;
    dbms_output.put_line(');');
  end loop;

  dbms_sql.close_cursor(l_c);
end;
/
1 rows affected

dbms_output:
insert into ACCT_HRS("WID","HOURS_TYPE_CD","WRK_DATE","TECH_ID","TECH_TYPE","SOME_TS","Some_TsTz","SOME_INTERVALYM","SOME_INTERVALDS") values (1,2,timestamp ' 2024-03-26 19:43:49',42,'ABC',timestamp ' 2024-03-26 19:43:49.276568000',timestamp ' 2024-03-26 19:43:49.276568000 +00:00',interval '+05-00' year to month,interval '+01 02:03:00.000000' day to second);
insert into ACCT_HRS("WID","HOURS_TYPE_CD","WRK_DATE","TECH_ID","TECH_TYPE","SOME_TS","Some_TsTz","SOME_INTERVALYM","SOME_INTERVALDS") values (1.5,-2.75,timestamp ' 2024-03-26 19:43:49',42,'ABC',timestamp ' 2024-03-26 19:43:49.276568000',timestamp ' 2024-03-26 19:43:49.276568000 +00:00',interval '+00-02' year to month,interval '+00 00:00:02.000000' day to second);

insert into ACCT_HRS("WID","HOURS_TYPE_CD","WRK_DATE","TECH_ID","TECH_TYPE","SOME_TS","Some_TsTz","SOME_INTERVALYM","SOME_INTERVALDS") values (1,2,timestamp ' 2024-03-26 19:41:57',42,'ABC',timestamp ' 2024-03-26 19:41:57.874409000',timestamp ' 2024-03-26 19:41:57.874409000 +00:00',interval '+05-00' year to month,interval '+01 02:03:00.000000' day to second);
1 rows affected

insert into ACCT_HRS("WID","HOURS_TYPE_CD","WRK_DATE","TECH_ID","TECH_TYPE","SOME_TS","Some_TsTz","SOME_INTERVALYM","SOME_INTERVALDS") values (1.5,-2.75,timestamp ' 2024-03-26 19:41:57',42,'ABC',timestamp ' 2024-03-26 19:41:57.874409000',timestamp ' 2024-03-26 19:41:57.874409000 +00:00',interval '+00-02' year to month,interval '+00 00:00:02.000000' day to second);
1 rows affected