Dynamic SQL within cursor

Question

My dynamic sql below to alter a table & create columns based on the output of a query is giving error.

Query :

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 have to be of SQL types

score 4 · Answer 1 · edited Oct 18 '17 at 09:23

4

This is happening because bind variables are not allowed in DDL statements.

Consider trying it 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;

edited Oct 18 '17 at 09:23

Addison

7,322
2
39
55

answered Aug 21 '10 at 22:56

OMG Ponies

325,700
82
523
502

1

exactly. Bind variables are not allowed everywhere. – Erich Kitzmueller Aug 21 '10 at 22:58
2

Depending on the source of `temp.WP_NO` aka `cnum`, you may want to sanitize it to protect against SQL injection attacks. – Shannon Severance Aug 22 '10 at 06:48

score -1 · Answer 2 · answered Aug 22 '10 at 13:22

-1

You have a conflict with the cnum symbol, which you use both as a local variable and for the current row of the cursor.

You probably want this:

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 don't need to declare the current_row variable that you use in the for loop.

answered Aug 22 '10 at 13:22

Codo

75,595
17
168
206

Bind variables cannot be used here. – Jeffrey Kemp Aug 23 '10 at 04:53
Both the answers did not work (though OMG ponies was right about the bind variables). The correct solution is : begin FOR x in (select distinct WP_NO from temp) loop execute immediate 'alter table temp_col add '|| x.wp_no || ' varchar2(255)'; END LOOP; END; – Sam Sep 01 '10 at 16:27

Dynamic SQL within cursor

2 Answers2