This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
- Revenera Community
- :
- InstallShield
- :
- InstallShield Forum
- :
- Trouble executing an SQL script against an Oracle 10g database
Subscribe
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Mar 24, 2008
10:09 AM
Trouble executing an SQL script against an Oracle 10g database
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 :
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:
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?
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?
(2) Replies
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Mar 24, 2008
06:03 PM
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@;
/
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Mar 26, 2008
10:03 AM
Still getting the same error after those changes. Any other ideas?
![](/skins/images/8CE4FAD0DE6F3A6F9D0567434D2D2DAE/responsive_peak/images/icon_anonymous_message.png)