SQL PLS-00382: Oracle 游标中的表达式类型错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23998372/
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
PLS-00382: expression is of wrong type in Oracle cursor
提问by keenUser
I have written a cursor in oracle 11g as follows:
我在oracle 11g中写了一个游标如下：
DECLARE CURSOR QnA_cursor IS
SELECT activity_id, question, answer
FROM TABLE1
WHERE question != 'surveyText'
ORDER BY activity_id, question;
cur_count INT := 1;
que NVARCHAR2(10);
ans NVARCHAR2(10);
sqlCommand NVARCHAR2(500);
RowCountVar INT;
BEGIN
FOR QueAns
IN QnA_cursor
LOOP
IF cur_count = 4 THEN cur_count := 1; END IF; /* We have only 3 questions for each activity_id */
que := 'question' || cur_count; /* question1, question2, question3 */
ans := 'answer' || cur_count; /* answer1, answer2, answer3 */
sqlCommand := 'UPDATE TABLE2 SET '||que||' = :1, '||ans||' = :2 WHERE activity_id = :3';
EXECUTE IMMEDIATE sqlCommand USING QueAns.question, QueAns.answer, QueAns.activity_id;
cur_count := cur_count + 1;
END LOOP;
END;
This is the schema for TABLE1 and TABLE2:
这是 TABLE1 和 TABLE2 的架构：
Create table TABLE2(
ACTIVITY_ID NUMERIC(19,0),
QUESTION1 NVARCHAR2(2000),
ANSWER1 NVARCHAR2(2000),
QUESTION2 NVARCHAR2(2000),
ANSWER2 NVARCHAR2(2000),
QUESTION3 NVARCHAR2(2000),
ANSWER3 NVARCHAR2(2000)
)
Create table TABLE1(
ACTIVITY_ID NUMERIC(19,0),
QUESTION NVARCHAR2(2000),
ANSWER NVARCHAR2(2000)
)
When I try to compile it in sqldeveloper, I am getting following error at the line where I execute the dynamic query 'EXECUTE IMMMEDIATE':
当我尝试在 sqldeveloper 中编译它时，在执行动态查询“EXECUTE IMMMEDIATE”的行中出现以下错误：
PLS-00382: expression is of wrong type
I have played around it a lot, but couldn't figure out the reason. The data types are same for corresponding columns in both the table. Can you please tell me what can be a problem?
我玩了很多，但找不到原因。两个表中对应列的数据类型相同。你能告诉我有什么问题吗？
回答by San
Change the datatype of sqlCommand from NVARCHAR2 to varchar2. Execute immediate expects varchar, varchar2 or char. Code given below should work
将 sqlCommand 的数据类型从 NVARCHAR2 更改为 varchar2。立即执行需要 varchar、varchar2 或 char。下面给出的代码应该可以工作
DECLARE
CURSOR qna_cursor IS
SELECT activity_id,
question,
answer
FROM table1
WHERE question != 'surveyText'
ORDER BY activity_id,
question;
cur_count INT := 1;
que VARCHAR2(10);
ans VARCHAR2(10);
sqlcommand VARCHAR2(500); --> Should be varchar, varchar2 or char
rowcountvar INT;
BEGIN
FOR queans IN qna_cursor LOOP
IF cur_count = 4 THEN
cur_count := 1;
END IF; /* We have only 3 questions for each activity_id */
que := 'question' || cur_count; /* question1, question2, question3 */
ans := 'answer' || cur_count; /* answer1, answer2, answer3 */
sqlcommand := 'UPDATE TABLE2 SET ' || que || ' = :1, ' || ans || ' = :2 WHERE activity_id = :3';
EXECUTE IMMEDIATE sqlcommand USING queans.question, queans.answer, queans.activity_id;
cur_count := cur_count + 1;
END LOOP;
END;
/
