Database Software

========================================
! ok, the problem is solved, it was just a silly mistake ... !
========================================

Cheers,
Tillmann


Dear everyone,

Ok, this problem might be more related to ArcGIS/VBA, but the error message I get is an Oracle one, so maybe I can find some here …?

I am trying to connect from my ArcMap (9.1) document to my Oracle DB (9i) using ArcObjects (VBA). Outside of ArcObjects, the connection through SDE works fine: I can use SDE (9.1) layers without problems in ArcGIS/Catalog. The function that is doing this for me (see below) keeps on producing error messages and I don’t understand why.

Error message (line “Set pWorkspace = pWorkspaceFactory.Open(pPropSet, 0)”):
(Message: MS Visual Basic) Run-time error ‘-2147216072 (80041538)’
Underlying DBMS error [ORA-24314: service handle not initialized No extended error.]

The “sdedc_Oracle.log” gives me this message:
DB_open_instance()::db_connect (OCI8) error: 24314
CAN'T OPEN INSTANCE: sde:oracle9i.
Spatial Engine Connection Failed (-51).
Cannot Get Access to Instance sde:oracle9i

Then, I found a message at support.esri ( http://support.esri.com/index.cfm?fa=knowledgebase.techarticles.articleShow&d=13987 ) saying that I should check the setting for ORACLE_SID in different locations:
1. %SDEHOME%\etc\dbinit.sde
-> this file is empty
2. system environment variables in the Environment tab
-> what’s this?
3. registry
-> there is no such entry at all in that “folder”
4. Oracle Home selector
-> hm, I have not bothered

Well, I am wondering what all this is about and why my connection works fine outside ArcObjects. What do I have to do – is this a common problem? Or, any hint on what else is going wrong here?

And a more general question: The connection, do I need to establish that every time I want to communicate to Oracle or only once a session? Do I need to disconnect and how can I do that?

Thanks, Tillmann


Public Sub AddSdeFeatureClass(SdeFeatureClass As String, LayerName As String)

' connect to Oracle DB
Dim pWorkspaceFactory As IWorkspaceFactory
Dim pWorkspace As IWorkspace

Dim pPropSet As IPropertySet
Set pPropSet = New PropertySet
With pPropSet
.SetProperty "SERVER", "***"
.SetProperty "INSTANCE", "sde:oracle9i"
.SetProperty "USER", "***"
.SetProperty "PASSWORD", "***"
.SetProperty "VERSION", "SDE.DEFAULT"
End With

Set pWorkspaceFactory = New SdeWorkspaceFactory
Set pWorkspace = pWorkspaceFactory.Open(pPropSet, 0)

' and now, open SDE feature
Dim pFeatureWorkspace As IFeatureWorkspace
Set pFeatureWorkspace = pWorkspace

Dim pFeatureClass As IFeatureClass
Set pFeatureClass = pFeatureWorkspace.OpenFeatureClass(SdeFeatureClass)

' Create a layer
Dim pFLayer As IFeatureLayer
Set pFLayer = New FeatureLayer
Set pFLayer.FeatureClass = pFeatureClass
pFLayer.name = LayerName

' Add the layer to the focus map
Dim pMxDoc As IMxDocument
Set pMxDoc = ThisDocument
pMxDoc.FocusMap.AddLayer pFLayer

' refresh view & contents
pMxDoc.ActiveView.Refresh
pMxDoc.UpdateContents

End Sub

Message was edited by:
user456977