PLSQL missing comma

Question

I try to PARSE this insert statement with DBMS_SQL.PARSE, but I get a "missing comma" error. Please help :(

     'insert into ' || '"' || v_materie || '"' || ' ( 
      nume,prenume,nr_matricol,valoare,data_notare) values(' ||
      v_nume || ','||
      v_prenume || ','||
      v_nr_matricol || ','||
      v_nota || ','||
      v_dataNotare || ')'

Answer 1

As @Giga indicated building an sql statement as string concatenation is a very bad idea. Not only is it dangerous being subject to sql injection, it is ugly, and completely unnecessary. You already using dbms_sql to validate the query - good idea by-the-way - so why not build the query with it in mind. So with your query using dbms_sql all the way through:

-- Setup 
create table "Mat Val" 
     ( nume        integer
     , prenume     integer
     , nr_matricol integer
     , valoare     integer
     , data_notare integer
     );


declare
   k_materie_name varchar2(8) := 'Mat Val';
   k_sql_base varchar2(1000) :=  
     'insert into "v_materie" (nume,prenume,nr_matricol,valoare,data_notare) values(:1,:2,:3,:4,:5)';

   l_sql_stmt varchar2(1000);
   l_dbms_cursor_ref integer; 

   l_rows_processed integer;

   v_nume        integer := 1;
   v_prenume     integer := 2;
   v_nr_matricol integer := 3;
   v_valoare     integer := 4;
   v_data_notare integer := 5;

begin 
  l_sql_stmt := replace(k_sql_base,'v_materie',k_materie_name);
  dbms_output.put_line('Setting up dbms_sql processing for statement:' || chr(10) || l_sql_stmt);  

  l_dbms_cursor_ref:= dbms_sql.open_cursor;
  dbms_sql.parse(l_dbms_cursor_ref,l_sql_stmt,DBMS_SQL.native);

  -- set up bind variables with local variables
  dbms_sql.bind_variable(l_dbms_cursor_ref, ':1', v_nume);
  dbms_sql.bind_variable(l_dbms_cursor_ref, ':2', v_prenume);
  dbms_sql.bind_variable(l_dbms_cursor_ref, ':3', v_nr_matricol);
  dbms_sql.bind_variable(l_dbms_cursor_ref, ':4', v_valoare);
  dbms_sql.bind_variable(l_dbms_cursor_ref, ':5', v_data_notare);

  -- execute dynamic sql
  l_rows_processed := dbms_sql.execute(l_dbms_cursor_ref);

  dbms_output.put_line('Rows inserted  to "' || k_materie_name || '" ==> ' || to_char(l_rows_processed)); 
  dbms_sql.close_cursor(l_dbms_cursor_ref);
end ;

Or just dbms_sql to validate the query then revert to execute immediate:

declare
   k_materie_name varchar2(8) := 'Mat Val';
   k_sql_base varchar2(1000) :=  
     'insert into "v_materie" (nume,prenume,nr_matricol,valoare,data_notare) values(:1,:2,:3,:4,:5)';

   l_sql_stmt varchar2(1000);
   l_dbms_cursor_ref integer; 

   l_rows_processed integer;

   v_nume        integer := 21;
   v_prenume     integer := 32;
   v_nr_matricol integer := 43;
   v_valoare     integer := 54;
   v_data_notare integer := 65;

begin 
  l_sql_stmt := replace(k_sql_base,'v_materie',k_materie_name);
  dbms_output.put_line('Setting up executee immediate for statement:' || chr(10) || l_sql_stmt);  

  l_dbms_cursor_ref:= dbms_sql.open_cursor;
  dbms_sql.parse(l_dbms_cursor_ref,l_sql_stmt,DBMS_SQL.native);
  dbms_sql.close_cursor(l_dbms_cursor_ref);

  execute immediate l_sql_stmt 
              using v_nume      
                  , v_prenume     
                  , v_nr_matricol 
                  , v_valoare     
                  , v_data_notare;

  dbms_output.put_line('Rows inserted  to "' || k_materie_name || '" ==> ' || to_char(sql%rowcount)); 

end ;

Either way Never build SQL with string concatenation. Plan for and use bind variables. It is safer as it eliminates SQL injection, but also your code is cleaner and actually easier to write (I always need to write a standalone statement first for testing it, then converting to bind variables is relatively simple). Finally, if the same statement is run multiple times with just value differences it is faster, as it avoids hard parses.

Answer 2

Actually generating insert string very bad idea. Anyway try this.

'insert into ' || '"' || v_materie || '"' || ' ( 
nume,prenume,nr_matricol,valoare,data_notare) values(''' ||
v_nume || ''','''||
v_prenume || ''','''||
v_nr_matricol || ''','''||
v_nota || ''','''||
v_dataNotare || ''')'

You should put values in ' character, but becouse you generating string you need to escape symbol ', so you will get '''