PLS-00382: expression is of wrong type in Oracle cursor

cursor, dynamicquery, oracle, plsql, sql

Solution

Change the datatype of sqlCommand from NVARCHAR2 to varchar2. Execute immediate expects varchar, varchar2 or char. Code given below should work

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

Problem

I have written a cursor in oracle 11g as follows: ``` 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: ``` 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': ``` 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?

Original source