SQL & PL/SQL

hello I'm a newbie in Oracle/PLSQL

I have a database init/setup script which looks like this:

DECLARE
count_item int;

AdminId numeric(19,0);
GuestId numeric(19,0);
AdminGroupId numeric(19,0);

CreateTimestamp date;
+ +
BEGIN
+ +
+ dbms_output.put_line('scheuma setup ...');+
+ +
+ ... create tables ...+

+ EXECUTE IMMEDIATE 'CREATE SEQUENCE GENERICCOMBOLISTITEM_SEQ+
+ MINVALUE 1+
+ MAXVALUE 999999999999999999999999999+
+ START WITH 1+
+ INCREMENT BY 1+
+ CACHE 20';+
+ dbms_output.put_line('GENERICCOMBOLISTITEM_SEQ created');+


+ SELECT count(1) into count_item FROM user_tables WHERE table_name = 'GENERICCOMBOLISTITEM';+
+ IF count_item > 0 THEN+
+ begin+
+ dbms_output.put_line('drop table GENERICCOMBOLISTITEM');+
+ EXECUTE IMMEDIATE ('DROP TABLE GENERICCOMBOLISTITEM');+
+ end;+
+ ELSE+
+ dbms_output.put_line('no need to drop table GENERICCOMBOLISTITEM');+
+ END IF;+
+ +
+ +
+ EXECUTE IMMEDIATE '+
+ CREATE TABLE GENERICCOMBOLISTITEM (+
+ ...+
+ )';+

+ dbms_output.put_line('table GENERICCOMBOLISTITEM created');+
+ +
+ ... create more tables ...+
+ +
+ dbms_output.put_line('scheuma setup completed');+
+ +
+ dbms_output.put_line('data setup ...');+
+ +
+ ... INSERT data ...+
+ +
+ select sysdate into CreateTimestamp from dual;+
+ +
+ ...+
+ +
+ +
+ INSERT INTO GenericComboListItem (Id, ObjectClassifier, stringValue, CreateDate, CreatedBy) VALUES (GENERICCOMBOLISTITEM_SEQ.nextVal, 'Person', 'country', CreateTimestamp, 1);+
+ INSERT INTO GenericComboListItem (Id, ObjectClassifier, stringValue, CreateDate, CreatedBy) VALUES (GENERICCOMBOLISTITEM_SEQ.nextVal, 'Person', 'state', CreateTimestamp, 1);+
+ INSERT INTO GenericComboListItem (Id, ObjectClassifier, stringValue, CreateDate, CreatedBy) VALUES (GENERICCOMBOLISTITEM_SEQ.nextVal, 'Person', 'zipCode', CreateTimestamp, 1);+
+ ...+
+ +
+ dbms_output.put_line('data setup completed'); +
+ +
dbms_output.put_line('setup completed');
+ +
EXCEPTION
+ WHEN OTHERS THEN+
+ dbms_output.put_line('*** setup exception detected! ***');+
dbms_output.put_line('error code: ' || sqlcode);
dbms_output.put_line('stack trace: ' || dbms_utility.format_error_backtrace);
END;

I'm not sure why when I run it it keeps saying table or view does not exists?

ERROR at line 580:
ORA-06550: line 580, column 14:
PL/SQL: ORA-00942: table or view does not exist

To get around this, I run only the first part of the script (i.e. create tables), then I run the entire script (create table + insert data into the tables), then things are fine. Perhaps this is because "CREATE TABLE" and "INSERT INTO TABLE" resides in same block? That by the time "INSERT INTO" is called "CREATE TABLE" has NOT yet been committed?

I've tried:
1. adding COMMIT in script
... CREATE TABLE ..
COMMIT;
... INSERT...
2. set AUTOCOMMIT IMMEDIATE

Didn't help unfortunately.

Edited by: normanfung on Apr 6, 2009 12:44 AM