动态插入 过程中失败，但用作静态 SQL

Question

我正在研究一个将数据从大矩阵转置到由三列组成的表中的过程。我在将行动态插入表中时遇到一些困难。当我尝试执行下面的过程块时，我收到一条错误消息：

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

该过程生成一个有效的插入语句，我可以将其复制并作为静态 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
)

环境： SQL Developer on Ubuntu 16.04 甲骨文 12c 社区版。

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;