ORA-00917: Missing Comma in PLSQL

Question

I have written a PL SQL script to add values stored in an array into a temp table. I have used Execute immediate to execute the insert query inside a loop.

I have checked the number of single quotes and the number of commas and they are closed. Still, I'm getting the error code

ORA-00917: missing comma.

declare
   type post_CODS IS VARRAY(34) OF VARCHAR2(20); 
   type locality_ID IS VARRAY(34) OF NUMBER(9); 
   pc post_CODS; 
   Y varchar2(1);
   lid locality_ID; 
   total integer;
begin
   Y := 'Y'; 
   lid := locality_ID(2380,3785,8710,17895,20345,24630,26814,28525,29130,31025,31265,32445,36940,40590,54290,83775,83780,83785,83790,83795,88483,93480,94560,96670,1000524,1000628,1000738,1000857,1000988,1001103,1001466,1001575,1001707,1001744);
   pc := post_CODS(2822,2739,2822,2372,2817,4314,4300,4726,4300,4884,4314,4314,4300,5719,3026,4314,4314,4314,4314,4314,873,2582,2817,4314,5723,5440,2083,2575,873,2372,5440,5440,5715,5440);
   total := lid.count;
   FOR i in 1 .. total LOOP 
      execute immediate 'insert into tmp_ref_lc_pc_cods values('||lid(i)||','||pc(i)||','||Y||',89987,'||sysdate||')';  
   END LOOP; 
END;

This is the error I am getting

Error report:

ORA-00917: missing comma ORA-06512: at line 14 00917. 00000 - "missing comma"

I'm working on an oracle machine.

Answer 1

A good trick when trying to execute immediate is making sure that the statements created are formatted correctly, printing the code you've written reveals that the Y is not inside of apostrophes

insert into tmp_ref_lc_pc_cods values(2380,2822,Y,89987,23-APR-19)

So when dynamically creating code make sure that all strings created have the right amount of apostrophes.

dbms_output.put_line('insert into tmp_ref_lc_pc_cods values('||lid(i)||','||pc(i)||','''||Y||''',89987,'||sysdate||')');

Answer 2

Wrap your insert statement in q'[]' and try

execute immediate q'[insert into tmp_ref_lc_pc_cods values('||lid(i)||','||pc(i)||','||Y||',89987,'||sysdate||')]';  

Update

It is recommended to use bind variable. You can try the below. It is working for me and inserting the data.

declare
   type post_CODS IS VARRAY(34) OF NUMBER(20); 
   type locality_ID IS VARRAY(34) OF NUMBER(9); 
   pc post_CODS; 
   Y varchar2(1);
   lid locality_ID; 
   total integer;
begin
   Y := 'Y'; 
   lid := locality_ID(2380,3785,8710,17895,20345,24630,26814,28525,29130,31025,31265,32445,36940,40590,54290,83775,83780,83785,83790,83795,88483,93480,94560,96670,1000524,1000628,1000738,1000857,1000988,1001103,1001466,1001575,1001707,1001744);
   pc := post_CODS(2822,2739,2822,2372,2817,4314,4300,4726,4300,4884,4314,4314,4300,5719,3026,4314,4314,4314,4314,4314,873,2582,2817,4314,5723,5440,2083,2575,873,2372,5440,5440,5715,5440);
   total := lid.count;
   FOR i in 1 .. total LOOP 
      execute immediate q'[insert into tmp_ref_lc_pc_cods values(:var1,:var2,'Y',89987,sysdate)]' using lid(i),pc(i);  
   END LOOP; 
END;