动态插入在过程失败，但作为静态SQL

Question

我正在处理一个过程，将数据从一个大矩阵转置到一个由三列组成的表中。我在动态插入表格时遇到了一些困难。当我尝试执行以下程序块，我得到一个错误mesage：

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

程序产生一个有效的INSERT语句，我可以复制并执行静态SQL。一切达到立即执行stmnt工作正常。此外，我有一个几乎完全相同的过程。两者之间只有一个区别。在工作版本中，所有插入的值都是“VARCHAR2”类型。我对如何继续排除故障感到不知所措。

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; 
/

通过上述方法产生的插入语句的一个例子：

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

环境： 在Ubuntu 16.04 甲骨文12C社区版SQL开发人员。

Answer 1

你应该使用绑定变量，即

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;