Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE clm_header_h (pay_order_date) AS
SELECT ADD_MONTHS(DATE '2020-01-01', LEVEL - 1) FROM DUAL CONNECT BY LEVEL <= 50;
50 rows affected

CREATE OR REPLACE PROCEDURE ddl_sp (begin_date nvarchar2, end_date nvarchar2) AS
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE sao_eligible_members_tbl (pay_order_date DATE)';

  EXECUTE IMMEDIATE q'[INSERT INTO sao_eligible_members_tbl
      SELECT pay_order_date
      FROM   clm_header_h
      WHERE  pay_order_date >= TO_DATE(:1, 'YYYYMM')
      AND    pay_order_date <  ADD_MONTHS(TO_DATE(:2, 'YYYYMM'), 1)
    ]' USING begin_date, end_date;
END ddl_sp;
/
BEGIN
    ddl_sp('202301', '202401');
END;
/
1 rows affected

SELECT * FROM sao_eligible_members_tbl;


PAY_ORDER_DATE


2023-01-01 00:00:00

2023-02-01 00:00:00

2023-03-01 00:00:00

2023-04-01 00:00:00

2023-05-01 00:00:00

2023-06-01 00:00:00

2023-07-01 00:00:00

2023-08-01 00:00:00

2023-09-01 00:00:00

2023-10-01 00:00:00

2023-11-01 00:00:00

2023-12-01 00:00:00

2024-01-01 00:00:00


DROP TABLE sao_eligible_members_tbl;
CREATE OR REPLACE PROCEDURE ddl_sp (begin_date nvarchar2, end_date nvarchar2) AS
BEGIN
    EXECUTE IMMEDIATE q'[
    CREATE TABLE sao_eligible_members_tbl AS
      SELECT pay_order_date
      FROM   clm_header_h
      WHERE  pay_order_date >= TO_DATE(']' || begin_date || q'[', 'YYYYMM')
      AND    pay_order_date <  ADD_MONTHS(TO_DATE(']' || end_date || q'[', 'YYYYMM'), 1)
    ]';
END ddl_sp;
/
SELECT * FROM USER_ERRORS;
BEGIN
    ddl_sp('202301', '202401');
END;
/
1 rows affected

SELECT * FROM sao_eligible_members_tbl;


PAY_ORDER_DATE


2023-01-01 00:00:00

2023-02-01 00:00:00

2023-03-01 00:00:00

2023-04-01 00:00:00

2023-05-01 00:00:00

2023-06-01 00:00:00

2023-07-01 00:00:00

2023-08-01 00:00:00

2023-09-01 00:00:00

2023-10-01 00:00:00

2023-11-01 00:00:00

2023-12-01 00:00:00

2024-01-01 00:00:00