oracle ORA-00984: 使用 sql 过程插入时此处不允许列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27223493/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-00984: column not allowed here while inserting using sql procedure
提问by Ryan
I have a table(my_routine) with the below structure
我有一个具有以下结构的表（my_routine）
Name Null Type
MY_ID NOT NULL NUMBER(10)
TEMPLATE_NAME NOT NULL VARCHAR2(255)
TEMPLATE_ID NOT NULL NUMBER(10)
ASSIGN_FL NOT NULL CHAR(1)
SYSTEM_FL CHAR(1)
DELETE_FL NOT NULL CHAR(1)
VERSION_ID NOT NULL NUMBER(10)
P_ID NOT NULL NUMBER(10)
I tried to insert data into this table based on the output from another table. So I wrote a procedure to perform the task, However I have problem while inserting the values into the table.
我试图根据另一个表的输出将数据插入到这个表中。所以我写了一个程序来执行任务，但是我在将值插入表中时遇到了问题。
I am getting the below error
我收到以下错误
ORA-00984: column not allowed here
ORA-06512: at line 21
00984. 00000 - "column not allowed here"
Below is the procedure I have written to perform the insertion task
下面是我编写的执行插入任务的过程
DECLARE RCOUNT NUMBER(10);
I NUMBER(10);
BEGIN
SELECT COUNT(TEMPLATE_ID) INTO RCOUNT FROM MY_TEMPLATE WHERE %SOME_CONDITION%;
I := 1;
WHILE I <= RCOUNT LOOP
DECLARE TEMPLATEID NUMBER(10);
MYNAME VARCHAR(100);
BEGIN
--CTEID := 10;
--CTENAME := 'QWER';
SELECT TEMPLATE_ID INTO TEMPLATEID FROM MY_TEMPLATE WHERE ID = I;
SELECT MY_NAME INTO MYNAME FROM MY_TEMPLATE WHERE ID = I;
DBMS_OUTPUT.PUT_LINE(I || TEMPLATEID || MYNAME);
EXECUTE IMMEDIATE 'INSERT INTO MY_ROUTINE VALUES(I,MYNAME,TEMPLATEID,N,N,N,1,1)';
I := I + 1;
END;
END LOOP;
END;
Let me know if there is anything wrong in the procedure Any help is appreciated!!!
如果程序中有任何问题，请告诉我任何帮助表示赞赏！！！
回答by a_horse_with_no_name
There is no need to use execute immediate here, use a regular statement and use the variables directly:
这里不需要使用立即执行，使用正则语句，直接使用变量：
insert into my_routing
values (I,MYNAME,TEMPLATEID, 'N','N','N',1,1);
Btw: it's good coding style to always state the columns in the insert statement:
顺便说一句：总是在插入语句中说明列是一种很好的编码风格：
insert into my_routine
(my_id, template_id, name, assign_fl, system_fl, delete_fl, version-id, p_id)
values
(I,MYNAME,TEMPLATEID, 'N','N','N',1,1);
But you don't need the slow and inefficient PL/SQL loop at all. What you want to do, can be done without PL/SQL in a single statement:
但是您根本不需要缓慢且低效的 PL/SQL 循环。你想做的事，可以不用PL/SQL在一条语句中完成：
insert into my_routine (my_id, template_id, name, assign_fl, system_fl, delete_fl, version-id, p_id)
select id, template_id, my_name, 'N','N','N',1,1
from my_template
where %some_condition%
回答by Exhausted
You can also use the same by using bind variables(If execute immediate needed) like below
您也可以通过使用绑定变量（如果需要立即执行）来使用相同的，如下所示
EXECUTE IMMEDIATE 'INSERT INTO MY_ROUTINE VALUES(I,:MYNAME,:TEMPLATEID,N,N,N,1,1)' using MYNAME,TEMPLATEID
Please refer binding at runtime http://www.dba-oracle.com/t_oracle_execute_immediate.htm
请参考运行时绑定http://www.dba-oracle.com/t_oracle_execute_immediate.htm
