CODEWITHSUNDEEP
oracle-databaseplsqlinsertdynamic-sqlexecute-immediate
user3672527
user3672527

Reputation: 55

Dynamic INSERT Fails in procedure, but works as static SQL

I am working on a procedure to transpose data from a large matrix into a table consisting of three columns. I'm having some difficulty dynamically inserting rows into the table. When I try to execute the procedure block below, I get an error mesage:

ORA-00936: missing expression
ORA-06512: at line 24
00936. 00000 -  "missing expression"

The procedure generates a valid insert statement, that I can copy and execute as static SQL. Everything up to execute immediate stmnt is working properly. Moreover, I have a nearly identical procedure that functions perfectly. There is only one difference between the two. In the working version, all of the values inserted are of type "VARCHAR2". I'm at a loss as to how to continue troubleshooting. 

declare
  type rec_type is record(
    row_name varchar2(250),
    measurement number(30,27)
    );
    my_rec rec_type;

    type cols_type is table of varchar2(10);
    cols cols_type;

    stmnt varchar2(2000);
    cur sys_refcursor;
begin
  select colnames bulk collect into cols from p100_stg1_tmnt_meta;
  for i in cols.first..cols.last loop
    stmnt := 'select site_id, '|| cols(i) ||' from p100_stg1_site_matrix';
    open cur for stmnt;
    loop
      fetch cur into my_rec;
      exit when cur%notfound;
      stmnt := 'insert into p100_stg1_site_measurement (site_id, col_name, measurement) values '||
      '('''||my_rec.row_name ||''', '''||cols(i)||''', '||my_rec.measurement||')';

      --dbms_output.put_line(stmnt);
    execute immediate stmnt;
    end loop;
  end loop;
end;
/

An example of an insert statement generated by the above procedure:

insert into p100_stg1_site_measurement (
     site_id, 
     col_name, 
     measurement
) 
values (
    '5715_C17orf85_S500_RPHS[+80]PEKAFSSNPVVR', 
    'tmnt_2', 
    .0288709682691077
)

Environment: SQL Developer on Ubuntu 16.04 Oracle 12c Community Edition.

Upvotes: 0

Views: 455

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59652

You should use bind variables, i.e. 

stmnt := 'insert into p100_stg1_site_measurement (site_id, col_name, measurement) 
    values (:site_id, :col, :measurement)';

execute immediate stmnt using my_rec.row_name, cols(i), my_rec.measurement;

Upvotes: 1

Related Questions