Execute Immediate in Oracle 11g

Question

I am trying to create a script in oracle to search through a number of tables and insert into a temp table i am creating but i am getting a few errors when trying to do anything other than a number.

Declare Variables

    m_polCount NUMBER:= 0;
    m_product NUMBER:= 0;
    m_version NUMBER:= 0;
    m_plan NUMBER:= 0;
    m_policy NUMBER:= 0;
    m_pol_comm_date DATE:= TO_DATE('01-01-3000','DD-MM-YYYY');
    m_pol_end_date DATE:= TO_DATE('01-01-3000','DD-MM-YYYY');
    m_pol_status NUMBER:= 0;
    m_next_pre_renew_date DATE:= TO_DATE('01-01-3000','DD-MM-YYYY');
    m_next_renew_date DATE:= TO_DATE('01-01-3000','DD-MM-YYYY');
    m_last_renew_date DATE:= TO_DATE('01-01-3000','DD-MM-YYYY');
    m_cover_no NUMBER:= 0;
    m_cover_name VARCHAR(240):= 'Test';
    m_cover_start_date DATE:= TO_DATE('01-01-3000','DD-MM-YYYY');
    m_cover_end_date DATE:= TO_DATE('01-01-3000','DD-MM-YYYY');
    m_sum_assured NUMBER:= 0;
    m_cover_layer NUMBER:= 0;
    m_cover_prem_layer NUMBER:= 0;
    m_premium NUMBER:= 0;
    m_loading_type NUMBER:= 0;
    m_loading_name VARCHAR(200):= 'Test';
    m_basic_prem_loading SMALLINT:= 0;
    m_loading_start_date DATE:= TO_DATE('01-01-3000','DD-MM-YYYY');
    m_loading_end_date DATE:= TO_DATE('01-01-3000','DD-MM-YYYY');
    m_loading_perc NUMBER:= 0;
    m_loading_rate NUMBER:= 0;
    m_loading_prem NUMBER:= 0;
    m_calc_desc VARCHAR(240):= 'Test';
    m_inflation_rate NUMBER:= 0;
    m_agent_no NUMBER:= 0;

Table Created

EXECUTE IMMEDIATE '
        CREATE TABLE I233_ACTIVE_P4L_POLICY_DATA
        (
        PRODUCT NUMBER,
        VERSION NUMBER,
        PLAN_NUMBER NUMBER,
        POLICY_NUMBER NUMBER,
        POLICY_COMM_DATE DATE,
        POLICY_END_DATE DATE,
        POLICY_STATUS NUMBER,
        NEXT_PRE_RENEWAL_DATE DATE,
        NEXT_RENEWAL_DATE DATE,
        LAST_RENEWAL_DATE DATE, 
        COVER_NUMBER NUMBER,
        COVER_NAME VARCHAR(240),
        COVER_START_DATE DATE,
        COVER_END_DATE DATE,
        SUM_ASSURED NUMBER,
        COVER_LAYER NUMBER,
        COVER_PREMIUM_LAYER NUMBER,
        ANNUAL_PREMIUM NUMBER,
        LOADING_TYPE NUMBER,
        LOADING_NAME VARCHAR(200),
        BASIC_PREM_LOADING SMALLINT,
        LOADING_START_DATE DATE,
        LOADING_END_DATE DATE,
        LOADING_PERC NUMBER,
        LOADING_RATE NUMBER,
        LOADING_PREM NUMBER,
        PREMIUM_CALC_DESC VARCHAR(240),
        INFLATION_RATE NUMBER,
        AGENT_NUMBER NUMBER
        )';

Insert Statement

EXECUTE IMMEDIATE '
        INSERT INTO I233_ACTIVE_P4L_POLICY_DATA
        VALUES(
        '||m_product||',
        '||m_version||',
        '||m_plan||',
        '||policy_rec.policy_no||',
        '||m_pol_comm_date||', <----This field
        TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
        '||m_pol_status||',
        TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
        TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
        TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
        '||m_cover_no||',
        '''',
        TO_DATE(''01-01-3000'',''DD-MM-YYYY''), 
        TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
        '||m_sum_assured||',
        '||m_cover_layer||',
        '||m_cover_prem_layer||',
        '||m_premium||',
        '||m_loading_type||',
        ''TEST3'',
        '||m_basic_prem_loading||',
        TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
        TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
        '||m_loading_perc||',
        '||m_loading_rate||',
        '||m_loading_prem||',
        ''TEST3'',
        '||m_inflation_rate||',
        '||m_agent_no||')';

Error

ORA-00917: missing comma

If i try to do the date in the same way i get a difference error

Insert Statement

EXECUTE IMMEDIATE '
        INSERT INTO I233_ACTIVE_P4L_POLICY_DATA
        VALUES(
        '||m_product||',
        '||m_version||',
        '||m_plan||',
        '||policy_rec.policy_no||',
        m_pol_comm_date, <----This field
        TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
        '||m_pol_status||',
        TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
        TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
        TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
        '||m_cover_no||',
        '''',
        TO_DATE(''01-01-3000'',''DD-MM-YYYY''), 
        TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
        '||m_sum_assured||',
        '||m_cover_layer||',
        '||m_cover_prem_layer||',
        '||m_premium||',
        '||m_loading_type||',
        ''TEST3'',
        '||m_basic_prem_loading||',
        TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
        TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
        '||m_loading_perc||',
        '||m_loading_rate||',
        '||m_loading_prem||',
        ''TEST3'',
        '||m_inflation_rate||',
        '||m_agent_no||')';

Gives me the following error

ORA-00984: column not allowed here

I also come into the same problems when trying to insert any of the VARCHAR fields using the same methods.

Is there any way i can insert date / varchar fields using an execute immediate into a temp table created above ?

All the above statements are contained within a BEGIN , END chunk

Thanks

Why are you using dynamic sql and not a static sql statement? Why are you creating the table dynamically, rather than having a permanent table (maybe a global temporary table (GTT) if you only need to keep the results for your session and you'll throw them away afterwards)? — Boneist
To diagnose your issue, you should use dbms_output.put_line (or whatever debug method you prefer) to find out what the string being executed dynamically is. That will go a loooong way to diagnosing the issues. But if I were you and I had to use dynamic sql, I'd use bind variables. (The reason why you're getting the errors is likely because you aren't explicitly converting the dates to strings before concatenating them to the executable statement, and then reconverting them back to dates in the statement.) — Boneist

Boneist Boneist · Accepted Answer · 2017-02-06T16:30:22

Assuming you can't avoid the dynamic sql, use bind variables. That way, you don't have to worry about datatype conversions, etc.

Something like:

execute immediate '
    INSERT INTO I233_ACTIVE_P4L_POLICY_DATA
    VALUES(:m_product,
           :m_version,
           :m_plan,
           :policy_rec.policy_no,
           :m_pol_comm_date, <----This field
           TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
           :m_pol_status,
           TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
           TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
           TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
           :m_cover_no,
           '''',
           TO_DATE(''01-01-3000'',''DD-MM-YYYY''), 
           TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
           :m_sum_assured,
           :m_cover_layer,
           :m_cover_prem_layer,
           :m_premium,
           :m_loading_type,
           ''TEST3'',
           :m_basic_prem_loading,
           TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
           TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
           :m_loading_perc,
           :m_loading_rate,
           :m_loading_prem,
           ''TEST3'',
           :m_inflation_rate,
           :m_agent_no)'
  USING :m_product,
        :m_version,
        :m_plan,
        :policy_rec.policy_no,
        :m_pol_comm_date,
        :m_pol_status,
        :m_cover_no,
        :m_sum_assured,
        :m_cover_layer,
        :m_cover_prem_layer,
        :m_premium,
        :m_loading_type,
        :m_basic_prem_loading,
        :m_loading_perc,
        :m_loading_rate,
        :m_loading_prem,
        :m_inflation_rate,
        :m_agent_no;

Execute Immediate in Oracle 11g

1 Answers