PDA

View Full Version : Error 27506 Running Oracle SQL Script



wjoconnor
09-06-2005, 10:14 AM
I am new to InstallShield 11 and I haven't used the SQL scripting functionality before. The scripts I use have been tested using SQLPlus and work properly, but when I attempt to run them from within the MSI, they fail. One of the scripts in question is:



/*
This script is run during uninstall and when rolling back the installation.
*/

DEFINE system_connect = &&1
SET ECHO OFF

CONNECT &&system_connect

-----------------------------------------------------------------
DROP ROLE "ROLE_ZBIR_COMMS_USER";
DROP ROLE "ROLE_ZBIR_FRWKDB";
DROP ROLE "ROLE_ZBIR_SECADMIN_USER";
DROP ROLE "ROLE_ZBIR_SESSION";
DROP ROLE "ROLE_ZBIR_TMIPOR";
DROP ROLE "ROLE_ZBIR_TMIPOR_USER";

DROP USER ZBIR_SCADB CASCADE;
DROP USER ZBIR_COMMDB CASCADE;
DROP USER ZBIR_SECADMIN CASCADE;
DROP USER ZBIR_COMMS CASCADE;
DROP USER ZBIR_FRWKDB CASCADE;
DROP USER ZBIR_TMIPOR CASCADE;

------------------------------------------------------------------
DROP TABLESPACE TBS_COMMDB INCLUDING CONTENTS;
DROP TABLESPACE TBS_FRWKDB INCLUDING CONTENTS;
DROP TABLESPACE TBS_SCADB INCLUDING CONTENTS;


I read elsewhere that the /* */ comments were causing problems in IS 10.5, but the script fails both with and without the comment.

I use the following settings for the script:
Run Script During Rollback
Run Script During Uninstall
On Error Abort Installation
Text Replacements:
Find &&1 Replace with [IS_SQLSERVER_USERNAME]/[IS_SQLSERVER_PASSWORD]@[IS_SQLSERVER_DATABASE]

The error I receive is:
MSI (s) (20!30) [10:36:17:265]: Product: ZBIR -- Error 27506.Error executing SQL script RemoveZBIRSchema. Line 28. [Microsoft][ODBC driver for Oracle][Oracle]ORA-00900: invalid SQL statement (900)

Line 28 is the last line of the script.

Any ideas as to what could be causing this?

wjoconnor
09-06-2005, 10:35 AM
Some additional information as I think of it....

I add the appropriate reference into the TNSNAMES.ORA file prior to the SQLLogin dialog. The user Id and password authenticates properly. I have the following default values applied to the properties:

IS_SQLSERVER_AUTHENTICATION 1
IS_SQLSERVER_DIALOG 0
IS_SQLSERVER_DATABASE ZBIR
IS_SQLSERVER_PASSWORD (Empty)
IS_SQLSERVER_SERVER ZBIR
IS_SQLSERVER_USERNAME system

The following information is placed in TNSNAMES.ORA

ZBIR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = LDB)
(SERVER=DEDICATED)
)
)

hidenori
09-06-2005, 11:12 AM
You probably need to remove the CONNECT command from your script. It is a SQLPlus command to establish a connection to an Oracle server, not a SQL statement. InstallShield connects to a server using the Open() method of ADO when clicking the Next button on the SQLLogin dialog. You only need to describe SQL statements in the SQL Scripts view.

Hope this helps.

wjoconnor
09-06-2005, 12:25 PM
Thanks for the reply, Hidenori.
So, since I have several sections in the main script (not shown above) which require different connect strings, I need to break those sections into their own scripts with different connection settings? Since these scripts are sensitive to which order they are run, how do I ensure which order IS runs the scripts?

hidenori
09-06-2005, 12:35 PM
You need a separate connection for each section. Also, InstallShield will open connections and execute SQL scripts by the order as shown in the tree. If you want to change the execution order, you can move up/down the nodes from the shortcut menu of the SQL Scripts view.