How to do "Insert into... Select... in a PL/SQL block (IF/THEN)

Question

I'm trying to run the script below in PL/SQL Developer, and I got an error says 'V_INSERT' is not a procedure or is undefined, and that statement is ignored.

Anyone can help? Thanks!

DECLARE chktime date; v_trunc varchar2(200); v_insert varchar2(200);
BEGIN
Select trunc(max(a.action_timestamp)) into chktime from hcr_dm.hcr_dm_fact a;

If chktime <> trunc(sysdate) then 
  v_trunc:='truncate table yxg3509.account_crosswalk_hcrdm';
  execute immediate v_trunc;
Else
  v_trunc:='truncate table yxg3509.product_to_ah_092514'; 
  v_insert:='insert into yxg3509.product_to_ah_092514 
             select prod.oracle_prod_code,
                    prod.oracle_prod_description,
                    prod.ah_code,
                    prod.effective_date
             from hcr_sandbox.product_to_ah prod';
  execute immediate v_trunc; v_insert;
END IF;
END;

execute immediate v_trunc; v_insert; is this correct with one exec statement? — SMA, Oct 23 '14 at 15:39
ps: u dont need to do the insert in dynamic in this situation — Thomas, Oct 23 '14 at 16:42

score 1 · Accepted Answer · answered Oct 23 '14 at 16:44

You'll need to use two EXECUTE IMMEDIATE statements to do this:

DECLARE
  chktime  date;
  v_trunc  varchar2(2000);
  v_insert varchar2(2000);
BEGIN
  Select trunc(max(a.action_timestamp)) into chktime from hcr_dm.hcr_dm_fact a;

  If chktime <> trunc(sysdate) then 
    v_trunc:='truncate table yxg3509.account_crosswalk_hcrdm';
    execute immediate v_trunc;
  Else
    v_trunc:='truncate table yxg3509.product_to_ah_092514'; 
    v_insert:='insert into yxg3509.product_to_ah_092514 
                 select prod.oracle_prod_code,
                        prod.oracle_prod_description,
                      prod.ah_code,
                      prod.effective_date
               from hcr_sandbox.product_to_ah prod';
    execute immediate v_trunc;
    execute immediate v_insert;
  END IF;
END;

although IMO there's no reason to use an EXECUTE IMMEDIATE for the INSERT statement, nor can I see a good reason to put the TRUNCATE TABLE statements into a variable, and thus you might be better off with:

DECLARE
  chktime  date;
BEGIN
  Select trunc(max(a.action_timestamp))
    into chktime
    from hcr_dm.hcr_dm_fact a;

  If chktime <> trunc(sysdate) then 
    execute immediate 'truncate table yxg3509.account_crosswalk_hcrdm';
  Else
    execute immediate 'truncate table yxg3509.product_to_ah_092514';

    insert into yxg3509.product_to_ah_092514 
      select prod.oracle_prod_code,
             prod.oracle_prod_description,
             prod.ah_code,
             prod.effective_date
        from hcr_sandbox.product_to_ah prod;
  END IF;
END;

Share and enjoy.

I changed my script based on yours. Although there was no error message, it did not insert the records into the table. Only the truncate statement has been executed... I tried both examples and the results were the same. I also validated that the insert statement is correct, if executed as a stand-alone script. Any idea why this's happening? thanks! — gcbm1984, Oct 23 '14 at 17:20
try to debug, create a temp proc from that block, and see step by step what happening. — Thomas, Oct 23 '14 at 18:13

How to do "Insert into... Select... in a PL/SQL block (IF/THEN)

1 Answers1