Why won't my dynamic DDL procedure in Oracle 19c run?

Question

I'm trying to create a procedure that will create a table based on the dates provided as arguments. The procedure seems good to go, but when I attempt to run it, I get "ORA-00904: "END_DATE": invalid identifier".

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
        h.pay_order_date
        FROM
            clm_header_h h
        WHERE
            TO_CHAR( h.pay_order_date, 'YYYYMM' ) BETWEEN begin_date AND end_date
    ]';
END ddl_sp;
/


BEGIN
    ddl_sp('202301', '202401');
END;

Please don't ask about why the dates are the way they are; it wasn't my choice. They work.

Answer 1

Why won't my dynamic DDL procedure in Oracle 19c run?

Because the statement ran via EXECUTE IMMEDIATE runs in a different scope to the PL/SQL procedure and does not have access to PL/SQL scope so begin_date and end_date are not defined.

---

You would normally use a USING clause of the EXECUTE IMMEDIATE statement to pass the arguments into bind variables in the query; however, you cannot use bind variables in DDL statements so you either need to:

1. Create the table first and then insert the data

   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;
   /
   

   or

2. Concatenate the date strings into the query:

   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;
   /
   

   If you are going to build the query using string concatenation then ensure you sanitise your inputs so you do not suffer from SQL injection attacks.

fiddle