Error 27506.Error executing SQL script (ORA-00900).

Irina_Galka · ‎Jan 21, 2008

Hello!
I have got the error, which occures when the sql script runs during installation.
Oracle database server connection is established.

Here is some part of the log file:

1: Determining if the SQL script 'sp.sql' will be executed...
1: The SQL script will be executed because no schema version is found on the target database.
1: Prepare executing SQL script file: 'sp.sql'. Path: 'C:\DOCUME~1\galka\LOCALS~1\Temp\~30E.tmp'
Action 18:20:02: Progress Custom Action. Creating CASHDB database...
1: Executing SQL at Line 2 >>>
1: Executing SQL at Line 5 >>>
1: ADO Error: Number: -2147217900 Description: [Microsoft][ODBC driver for Oracle][Oracle]ORA-00900: ??????? ??????????? ?? ????? SQL Source: Microsoft OLE DB Provider for ODBC Drivers SQL State: 37000 Native Error: 900
1: There was a SQL scripting error at line 5. Aborting the setup.
Error 27506.Error executing SQL script sp.sql. Line 5. [Microsoft][ODBC driver for Oracle][Oracle]ORA-00900: ??????? ??????????? ?? ????? SQL (900)
MSI (s) (4C!00) [18:22:49:663]: Product: ILS Cashier 3.0 -- Error 27506.Error executing SQL script sp.sql. Line 5. [Microsoft][ODBC driver for Oracle][Oracle]ORA-00900: ??????? ??????????? ?? ????? SQL (900)
Action ended 18:22:50: InstallFinalize. Return value 3.

SQL script begins from:

CREATE OR REPLACE PACKAGE "PKG_CASHDB" AS
SUBTYPE money IS NUMBER(19,4);

-- Password
FUNCTION pwdencrypt( v_password IN VARCHAR2) RETURN VARCHAR2;
FUNCTION pwdcompare( v_password1 IN VARCHAR2,v_password2 IN VARCHAR2,v_action INTEGER) RETURN INTEGER;

PROCEDURE GetTag(tagname IN VARCHAR2,val OUT VARCHAR2);

I have read about ORA-00900: invalid SQL statement.
And determined that the Procedural Option is installed, because of opening an Oracle session using SQL*Plus > the PL/SQL banner displays.

I cannot understand, which statement is not a valid in line 5 or before.

Please, help me.

hidenori · ‎Jan 21, 2008

Would it be possible for you to post your entire SQL script here, or email me at hidenoriy@macrovision.com?

hidenori · ‎Jan 22, 2008

In order to allow ; delimiter used in the package body, you need to use a different string for the batch separator which can be changed in the Advanced tab of SQL connections. The following sample illustrates how to use / for a batch separator in a script:

CREATE OR REPLACE PACKAGE "PKG_TEST" AS
  SUBTYPE money IS NUMBER(19,4);

	FUNCTION MyFunc(param1 IN VARCHAR2) RETURN VARCHAR2;
	PROCEDURE MyProc(param1 IN VARCHAR2, param2 OUT VARCHAR2);

END "PKG_TEST";
/
commit
/

Error 27506.Error executing SQL script (ORA-00900).

InstallShield 2008