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
– Lokesh, Commented May 20, 2013 at 17:06
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
– N1tr0, Commented May 20, 2013 at 17:08
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
– David Aldridge, Commented May 20, 2013 at 18:25
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
– Toru, Commented May 20, 2013 at 21:03

Mohsen Heydari · Accepted Answer · 2013-05-21 05:23:09Z

1

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;

edited May 21, 2013 at 5:23

answered May 20, 2013 at 20:43

Mohsen Heydari

7,2924 gold badges33 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

N1tr0 Over a year ago

I like this approach of taking the select out of the values section but I actually get two 'end of file' errors with this one. Maybe I can flesh them out. The new error is on line 2: Encountered the symbol "end-of-file" when expecting one of the following: := . ( @ % ; not null range default character

Mohsen Heydari Over a year ago

If you add the tables DDL that are involved in the process, that would be helpfull

Shailesh · Accepted Answer · 2013-05-21 07:37:20Z

0

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;

answered May 21, 2013 at 7:37

Shailesh

9988 silver badges16 bronze badges

1 Comment

N1tr0 Over a year ago

I tried adding the semi colon to the end of the select in the values section but no luck. :-(

Collectives™ on Stack Overflow

Oracle insert loop - PLS-00103 error

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related