Dynamic SQL within cursor

开发者_JAVA百科

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

---

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;

---

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.