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, Feb 06 '17 at 16:12
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, Feb 06 '17 at 16:20

score 1 · Accepted Answer · answered Feb 06 '17 at 16:30

1

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;

answered Feb 06 '17 at 16:30

Boneist

22,910
1
25
40

This does the job great, Thanks very much was pulling my hair out over this – PowPowPowell Feb 06 '17 at 17:02
This is the third post in a row that I have suggested using the q-quote syntax, as it simplifies the need to escape quotes etc. Basically you do something like q'[ Your string ]' – BobC Feb 06 '17 at 17:09
@BobC bind variables are even better than the quote syntax in this case. No danger of SQL injection, automatic handling of different datatypes etc – Boneist Feb 06 '17 at 17:13
@Boneist Indeed, assuming it's s customer facing SQL. My comment was more an FYI, and you can still use it in the first part of your solution, so you would avoid having to escape the quotes. – BobC Feb 06 '17 at 17:19
this will indeed be customer facing and dynamic which is why i have gone down this approach this is just the bare bones of it and will be a lot of logic and processing between the population of the variables and inserting into the temp table – PowPowPowell Feb 07 '17 at 08:48
If this is to become part of production code that will be run frequently, why create the temp table on the fly? Instead, if you only require the data in the temp table for the duration of the processing, I highly recommend you create a permanent Global Temporary Table. That way, you don't have to bother creating and dropping the table, plus it means you don't need a dynamic insert statement - which would make it much simpler. – Boneist Feb 07 '17 at 08:51

Execute Immediate in Oracle 11g

1 Answers1