Hello,
I have several tables with fields name starting with a numeric (like "0BANKCTRY"). I have to do a global change to modify the field length (change from BYTE to CHAR)
If I do the following instruction, it work perfectly:
ALTER TABLE TST_TABLE MODIFY "0BANKCTRY" VARCHAR2(3 CHAR);

So instead of having a script to make the changes I wanted to write a little procedure that do an EXECUTE IMMEDIATE with my sql statement like this:
DECLARE
sql_stmt VARCHAR2(200);
varcol VARCHAR2(200);
BEGIN
FOR x in (SELECT * FROM USER_TAB_COLUMNS WHERE data_type LIKE 'VARCHAR%' and CHAR_USED = 'B' and table_name = 'PMO_TST_CA_H602_2')
LOOP
sql_stmt := 'ALTER TABLE '||x.table_name||' MODIFY "'||x.column_name||'" '||x.data_type||'('||x.data_length||' CHAR);';
DBMS_OUTPUT.PUT_LINE ( sql_stmt) ;
EXECUTE IMMEDIATE 'ALTER TABLE '||x.table_name||' MODIFY "'||x.column_name||'" '||x.data_type||'('||x.data_length||' CHAR);';
END LOOP;
END;
/

Unfortunately I made many tentative with and without double quote or single quote and until now I couldn't succeed.

Do you have some suggestion to make this code working ?

Thanks in advance for your help,
Kind regards,
Philippe

SQL> create table PMO_TST_CA_H602_2 ( x int, y varchar2(10), "0BANKCTRY" varchar2(20));

Table created.

SQL> insert into PMO_TST_CA_H602_2 values ( 1, 'Hello', 'There');

1 row created.

SQL>
SQL> set serverout on
SQL> DECLARE
  2    sql_stmt VARCHAR2(200);
  3    varcol VARCHAR2(200);
  4  BEGIN
  5  FOR x in (SELECT * FROM USER_TAB_COLUMNS WHERE data_type LIKE 'VARCHAR%' and CHAR_USED = 'B' and table_name = 'PMO_TST_CA_H602_2')
  6  LOOP
  7    sql_stmt := 'ALTER TABLE '||x.table_name||' MODIFY "'||x.column_name||'" '||x.data_type||'('||x.data_length||' CHAR)';
  8    DBMS_OUTPUT.PUT_LINE ( sql_stmt ||';') ;
  9    EXECUTE IMMEDIATE sql_stmt;
 10  END LOOP;
 11  END;
 12  /
ALTER TABLE PMO_TST_CA_H602_2 MODIFY "Y" VARCHAR2(10 CHAR);
ALTER TABLE PMO_TST_CA_H602_2 MODIFY "0BANKCTRY" VARCHAR2(20 CHAR);

PL/SQL procedure successfully completed.