Trouble executing an SQL script against an Oracle 10g database

[rajohnson]

Hi, I'm running an Installscript installer against an Oracle 10g database and I'm getting a ORA-00911: invalid character (911) on line 12 of the following script :

ALTER USER @DCAUSER_RW@ ACCOUNT LOCK;

DECLARE
BEGIN
   FOR SESSIONS IN (SELECT SID || ',' || SERIAL# AS SID FROM V$SESSION WHERE USERNAME='@DCAUSER_RW@')
   LOOP
        EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || SESSIONS.SID || '''';
   END LOOP;
   --DBMS_LOCK.sleep(30);
END;
/

COMMIT;

DROP USER @DCAUSER_RW@ CASCADE;


CREATE USER @DCAUSER_RW@ PROFILE DEFAULT
  IDENTIFIED BY "@DCAUSER_RW_PASSWORD@"
  DEFAULT TABLESPACE @ETLTOOL_TABLESPACE_NAME@
  TEMPORARY TABLESPACE TEMP
  ACCOUNT UNLOCK;

ALTER USER @DCAUSER_RW@ QUOTA UNLIMITED ON @ETLTOOL_TABLESPACE_NAME@;
--ALTER USER @DCAUSER_RW@ QUOTA 0 ON SYSTEM;

GRANT EXECUTE ANY PROCEDURE TO @DCAUSER_RW@;

GRANT CREATE SESSION, CONNECT TO @DCAUSER_RW@;

GRANT ALTER ANY INDEX TO @DCAUSER_RW@;
GRANT ALTER ANY PROCEDURE TO @DCAUSER_RW@;
GRANT ALTER ANY SEQUENCE TO @DCAUSER_RW@;
GRANT ALTER ANY TABLE TO @DCAUSER_RW@;

GRANT CREATE ANY TYPE TO @DCAUSER_RW@;
GRANT CREATE ANY INDEX TO @DCAUSER_RW@;
GRANT CREATE ANY PROCEDURE TO @DCAUSER_RW@;
GRANT CREATE ANY SEQUENCE TO @DCAUSER_RW@;
GRANT CREATE ANY TABLE TO @DCAUSER_RW@;
GRANT CREATE ANY VIEW TO @DCAUSER_RW@;
GRANT CREATE ANY MATERIALIZED VIEW TO @DCAUSER_RW@;
GRANT CREATE ANY SYNONYM TO @DCAUSER_RW@;

GRANT DROP ANY TYPE TO @DCAUSER_RW@;
GRANT DROP ANY INDEX TO @DCAUSER_RW@;
GRANT DROP ANY PROCEDURE TO @DCAUSER_RW@;
GRANT DROP ANY SEQUENCE TO @DCAUSER_RW@;
GRANT DROP ANY TABLE TO @DCAUSER_RW@;
GRANT DROP ANY VIEW TO @DCAUSER_RW@;
GRANT DROP ANY MATERIALIZED VIEW TO @DCAUSER_RW@;

I'm using Text substitution to replace @DCAUSER_RW@ @DCAUSER_RW_PASSWORD@ and @ETLTOOL_TABLESPACE_NAME@.
The script works fine in Sqlplus when I replace these values with standard text. I've set / as my Batch Separator and I've tried changing the Batch Separator to other values with no success. I assumed that error was from using ;'s. If I try to remove all ;'s I get the following error: ORA-00922: missing or invalid option (922).

If I modify the script to just:

CREATE USER @DCAUSER_RW@ PROFILE DEFAULT
  IDENTIFIED BY "@DCAUSER_RW_PASSWORD@"
  DEFAULT TABLESPACE @ETLTOOL_TABLESPACE_NAME@
  TEMPORARY TABLESPACE TEMP
  ACCOUNT UNLOCK

the script works so the text substitution is working correctly. I've also tried switch to using the Oracle ODBC drivers but that hasn't solved anything. Does anyone have any other ideas for me to try?

[hidenori]

Please try to add a batch separator for each statement as follows and see if it solves your issue:

ALTER USER @DCAUSER_RW@ ACCOUNT LOCK;
/
DECLARE
BEGIN
   FOR SESSIONS IN (SELECT SID || ',' || SERIAL# AS SID FROM V$SESSION WHERE USERNAME='@DCAUSER_RW@')
   LOOP
        EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || SESSIONS.SID || '''';
   END LOOP;
   --DBMS_LOCK.sleep(30);
END;
/

COMMIT;
/

DROP USER @DCAUSER_RW@ CASCADE;
/

CREATE USER @DCAUSER_RW@ PROFILE DEFAULT
  IDENTIFIED BY "@DCAUSER_RW_PASSWORD@"
  DEFAULT TABLESPACE @ETLTOOL_TABLESPACE_NAME@
  TEMPORARY TABLESPACE TEMP
  ACCOUNT UNLOCK;
/
ALTER USER @DCAUSER_RW@ QUOTA UNLIMITED ON @ETLTOOL_TABLESPACE_NAME@;
--ALTER USER @DCAUSER_RW@ QUOTA 0 ON SYSTEM;
/
GRANT EXECUTE ANY PROCEDURE TO @DCAUSER_RW@;
/
GRANT CREATE SESSION, CONNECT TO @DCAUSER_RW@;
/

[rajohnson]

Still getting the same error after those changes. Any other ideas?