Oracle insert loop - PLS-00103 error

Question

I am trying to create an insert statement based off of a loop but I am getting PLS-00103 Encountered the symbol "end of file" when expecting one of the following: ;

The point of the script is to select all IDs from another table based on the value of another field in a joined table and then insert those IDs into a second table, one row at a time of course.

Here is what I have so far and I'm not sure why I get the error. The line number points to the last CURRENT_DATE entry.

BEGIN
 For v_apv_cmp_id IN (
                            select distinct
                              paa.APV_CMP_ID
                            from APV_APVR paa
                            join APV_CMP pac on pac.apv_cmp_id = paa.apv_cmp_id
                            join SEC_DISPLAY ssd on ssd.app_level_id = pac.app_level_id
                            where ssd.field_table_name = 'application_name'
                        )
 LOOP
 INSERT INTO APV_APVR
        (APV_APVR_ID, APV_CMP_ID, APVR_USR_ID, ORG_USR_ACV_TS, ORG_USR_NU, LTS_UPD_USR_NU, LTS_UPD_USR_TS)
 values (
        (
          select MAX(APV_APVR_ID)+1 from APV_APVR
        )
          , v_apv_cmp_id
          , 'tssao18'
          , 'tssetac'
          , CURRENT_DATE
          , 'tssetac'
          , CURRENT_DATE
        );
 END LOOP;
END;

Can you just remove the value ( select MAX(PRE_APV_APVR_ID)+1 from PRE_APV_APVR ) and replace it with some dummy constant and see if it works — Lokesh
Well, I need to increment the PK for that table. It works when inserting just one row. I was hoping to be able to use it for multimple rows. — N1tr0
Convert it into a single SQL statement and generate the id using rownum+coalesce((select max(APV_APVR_ID) from APV_APVR),0). It'll work as long as this is the only session inserting into the table at any one time — David Aldridge
The usage of v_apv_cmp_id as ID is a little bit suspicious. Try to replace it with v_apv_cmp_id.apv_cmp_id. — Toru

Mohsen Heydari Mohsen Heydari · Accepted Answer · 2013-05-20T20:43:50

DECLARE
    new_id number;
BEGIN
    SELECT NVL(MAX(APV_APVR_ID),0) + 1 into new_id FROM APV_APVR;
    FOR v_apv_cmp_id IN (
                                SELECT DISTINCT
                                  paa.APV_CMP_ID
                                FROM APV_APVR paa
                                JOIN APV_CMP pac ON pac.apv_cmp_id = paa.apv_cmp_id
                                JOIN SEC_DISPLAY ssd ON ssd.app_level_id = pac.app_level_id
                                WHERE ssd.field_table_name = 'application_name'
                            )
     LOOP
         INSERT INTO APV_APVR
            (APV_APVR_ID, APV_CMP_ID, APVR_USR_ID, ORG_USR_ACV_TS, ORG_USR_NU, LTS_UPD_USR_NU, LTS_UPD_USR_TS)
     VALUES (  new_id
              , v_apv_cmp_id
              , 'tssao18'
              , 'tssetac'
              , CURRENT_DATE
              , 'tssetac'
              , CURRENT_DATE
            );
         new_id := new_id + 1;
     END LOOP;
END;

Oracle insert loop - PLS-00103 error

2 Answers