Dynamic SQL inside the cursor

Question

My dynamic sql below to modify the table and create columns based on the output of the query gives an error.

Inquiry:

DECLARE CURSOR c1 is select distinct WP_NO from temp; cnum VARCHAR2(255); BEGIN FOR cnum in c1 LOOP EXECUTE IMMEDIATE 'Alter table temp_col add (:1 varchar2(255))' using cnum; END LOOP; COMMIT; END; 

Error:

PLS-00457: expressions must have SQL types

Answer 1

This is because variable bindings are not allowed in DDL reports .

Think about this without using the bind variable:

 DECLARE CURSOR c1 is select distinct WP_NO from temp; cnum VARCHAR2(255); BEGIN FOR cnum in c1 LOOP EXECUTE IMMEDIATE 'Alter table temp_col add ('|| cnum ||' varchar2(255))'; END LOOP; COMMIT; END; 

Answer 2

You have a conflict with the cnum character, which you use both a local variable and the current cursor line.

You probably want:

 DECLARE CURSOR c1 is select distinct WP_NO from temp; BEGIN FOR current_row in c1 LOOP EXECUTE IMMEDIATE 'Alter table temp_col add (:1 varchar2(255))' using current_row.WP_NO; END LOOP; COMMIT; END; 

As you can see, you do not need to declare the current_row variable that you use in the for loop.