Failed ArcSDE Schema creation in Oracle when NLS_COMP=LINGUISTIC!

[mihailmihaylov]

Hi there,
I am facing a problem with sde repository creation when certain parameters in Oracle database are modified.
No matter if it's Oracle 11 R1 or Oracle 10 R2(I haven't tried with other versions - 8 or 9, as they don't support LINGUSTING setting for NLS_COMP parameter).
When I change the database initialization parameters NLS_COMP to LINGUISTIC and NLS_SORT to BINARY_CI, in order to do case-insensitive searches, I am unable to create the SDE schema in that database.
When I use the same Oracle machine, installation and configuration, but I don't modify those parameters everything is going just fine and after successful sde repository creation, if i change NLS_COMP to LINGUISTIC and NLS_SORT to BINARY_CI, the esri_sde service wont start anymore and even if it starts the GDB is useless and broken. My goal is to have case-insensitive search using "LIKE" operator in Oracle GDB, any ideas?

This is the sde_setup.log error:

[Tue Apr 27 11:59:10 2010] SQL Stmt: <drop package SDE.st_geometry_shapelib_pkg>
[Tue Apr 27 11:59:10 2010] ERROR in creating system stored procedures.
Error: -88
[Tue Apr 27 11:59:10 2010] DBMS error code: 4043
ORA-04043: object ST_GEOMETRY_SHAPELIB_PKG does not exist


[Tue Apr 27 11:59:10 2010] SDE schema object install not completed.
[Tue Apr 27 11:59:10 2010] ERROR installing/upgrading ArcSDE, Error = -1

[Anonymous User]

Hi,

I was able to find an older enhancement request to allow these two parameters to be changed to LINGUISTIC and BINARY_CI, but it is unrelated to the schema creation process.

Are you able to successfully create the schema when these two parameters remain with their default settings then change them after the post install is successful? If so are you also able to successfully preview, query use any feature classes without any problems? The reason I ask is because the bug I mentioned above is related to not being able to preview the data.

NIM006880 Setting the "nls_comp" & "nls_sort" parameters in Oracle via a logon trigger causes errors in ArcGIS 9.2 while viewing featureclass with st_geometry.

I think it would be a good idea to open an incident with ESRI Support and we can investigate this further.

Thanks,

Despina
ESRI Support Services

[mihailmihaylov]

Hi despina
and thank you for answering me. My first attempt was only to change those two parameters in a working GDB. After that sde_service won't start anymore. Here I want to mention that changing those parameters in SPfiles in a Window's Oracle it is not enough, also they should be added as environment variables. Exactly at that point (when I set environment variable NLS_COMP=LINGUSTIC) the service does not start anymore, I could not recreate it and if I run another postinstall it fails.
After that I tried to create a new Oracle DB with those parameters set from the beginning in the initialization parameters, and the results were the same.
I followed your advice and opened an incident with ESRI Support.