Oracle Database Discussions

We have PL/SQL scripts which modify the data structure, add data etc to en existing database. These scripts basically ensure that the db is compatible with what the software expects.

The reason for doing it in PL/SQL rather than SQL script is A) The scripts are launched using GUI so that they are more user friendly. sqlplus is launched as a background process which executes these scripts. All the scripts have EXIT FAILURE WHENEVER SQLERROR as first line to ensure that the control does not hang in the sqlplus background process.

Going from one version to other, we have added a few tables to the database and also modified a few tables. since (i think) DDL is not allowed in PL/SQL block, we had to resort to putting them in EXECUTE IMMEDIATE enclosures.

Now for the real question,
If I create a table using EXECUTE IMMEDIATE clause, I can immediately have insert as a next statement to insert data in this table. but, if I alter the table and add a column to the existing table, I cannot immediately add data to that column, it throws an error saying 'invalid identifier'

At code level, the following is allowed :
EXECUTE IMMEDIATE 'CREATE TABLE SP_TEST_TABLE
(
ID NUMBER,
NAME Varchar2(40)
)';

INSERT INTO SP_TEST_TABLE(ID, NAME) Values(1, 'SP');

but I get error for the following :
EXECUTE IMMEDIATE 'ALTER TABLE SP_TEST_TWO ADD
(
ANOTHER_COLUMN number
)';

UPDATE SP_TEST_TWO SET ANOTHER_COLUMN = 1;

In this case, it says ANOTHER_COLUMN invalid identifier

Does anybody know why?

any workaround will be greatly appreciated.

--SP