Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
SQL> create or replace procedure alter_table_test
2 AS
3 begin
4 execute immediate 'ALTER TABLE TMP_MAF_2 ADD column col1 varchar(10)';
5 execute immediate 'ALTER TABLE TMP_MAF_2 ADD column col2 varchar(10)';
6 end;
7 /
Procedure created.
SQL> exec alter_table_test
BEGIN alter_table_test; END;
*
ERROR at line 1:
ORA-00904: : invalid identifier
ORA-06512: at "MICHEL.ALTER_TABLE_TEST", line 4
ORA-06512: at line 1
1/ The table does not exist, so you cannot alter it.
SQL> create table TMP_MAF_2 (id integer);
Table created.
SQL> exec alter_table_test
BEGIN alter_table_test; END;
*
ERROR at line 1:
ORA-00904: : invalid identifier
ORA-06512: at "MICHEL.ALTER_TABLE_TEST", line 4
ORA-06512: at line 1
2/ To debug dynamic query you have to execute it directly:
SQL> ALTER TABLE TMP_MAF_2 ADD column col1 varchar(10);
ALTER TABLE TMP_MAF_2 ADD column col1 varchar(10)
*
ERROR at line 1:
ORA-00904: : invalid identifier
3/ The syntax is not correct
SQL> ALTER TABLE TMP_MAF_2 ADD col1 varchar(10);
Table altered.
4/ This is a valid syntax, so fix the procedure code
SQL> create or replace procedure alter_table_test
2 AS
3 begin
4 execute immediate 'ALTER TABLE TMP_MAF_2 ADD col1 varchar(10)';
5 execute immediate 'ALTER TABLE TMP_MAF_2 ADD col2 varchar(10)';
6 end;
7 /
Procedure created.
SQL> drop table TMP_MAF_2;
Table dropped.
SQL> create table TMP_MAF_2 (id integer);
Table created.
SQL> exec alter_table_test
PL/SQL procedure successfully completed.
SQL> desc TMP_MAF_2
Name Null? Type
-------------------------------- -------- ----------------------
ID NUMBER(38)
COL1 VARCHAR2(10 CHAR)
COL2 VARCHAR2(10 CHAR
] 
Blog
Search
Help
Members
Register
Login
Home
