clean up orphaned connection

set SERVEROUTPUT ON
exec dbms_output.enable(100000);

DECLARE

CURSOR process_list IS
SELECT sde_id, owner, nodename FROM sde.process_information;

lock_name VARCHAR2(30);
lock_handle VARCHAR2(128);
lock_status INTEGER;
cnt INTEGER DEFAULT 0;

BEGIN

FOR check_locks IN process_list LOOP

lock_name := 'SDE_Connection_ID#' || TO_CHAR (check_locks.sde_id);
DBMS_LOCK.ALLOCATE_UNIQUE (lock_name,lock_handle);
lock_status := DBMS_LOCK.REQUEST (lock_handle,DBMS_LOCK.X_MODE,0,TRUE);

IF lock_status = 0 THEN
DELETE FROM sde.process_information WHERE sde_id = check_locks.sde_id;
DELETE FROM sde.state_locks WHERE sde_id = check_locks.sde_id;
DELETE FROM sde.table_locks WHERE sde_id = check_locks.sde_id;
DELETE FROM sde.object_locks WHERE sde_id = check_locks.sde_id;
DELETE FROM sde.layer_locks WHERE sde_id = check_locks.sde_id;
cnt := cnt + 1;
dbms_output.put_line('Removed entry ('||check_locks.sde_id||'): '||check_locks.owner||'/'||check_locks.nodename||'');
END IF;

END LOOP;

/* Remove any orphaned lock entries... */

DELETE FROM sde.state_locks WHERE sde_id NOT IN (SELECT sde_id FROM sde.process_information);
DELETE FROM sde.table_locks WHERE sde_id NOT IN (SELECT sde_id FROM sde.process_information);
DELETE FROM sde.object_locks WHERE sde_id NOT IN (SELECT sde_id FROM sde.process_information);
DELETE FROM sde.layer_locks WHERE sde_id NOT IN (SELECT sde_id FROM sde.process_information);

COMMIT;

dbms_output.put_line('Removed '||cnt||' entries.');

END;

script to get the duct info blob

'getting the value from Blob field
Dim pBlob As IMemoryBlobStream
iCount = iCount + 1
If IsNull(pfeat.Value(nBlob)) Then
GoTo myEnd
End If
Set pBlob = pfeat.Value(nBlob)

'create the duckbankconfig and ductview objecs
Dim pDuctBankConfig As IMMDuctBankConfig
Dim pDuctView As IMMDuctView
Set pDuctBankConfig = New MMDuctBankConfig
Set pDuctView = New MMDuctView
'asociate list objects with duct configuration object
Dim pDuctBankConfigList As ID8List
Dim pDuctViewList As ID8List
Set pDuctViewList = pDuctView
Set pDuctBankConfigList = pDuctBankConfig

Dim pMMListItem As IMMPersistentListItem
Set pMMListItem = pDuctBankConfigList
'load the blob into list objects
pMMListItem.LoadFromStream pBlob

Dim pDuctDefinition As IMMDuctDefinition


If Not (pDuctBankConfigList Is Nothing) Then 'And (Not IsNull(pDuctBankConfigList))
pDuctBankConfigList.Reset
Do
Set pDuctViewList = pDuctBankConfigList.Next
If Not (pDuctViewList Is Nothing) Then 'And (Not IsNull(pDuctViewList))
pDuctViewList.Reset
Do
Set pDuctDefinition = pDuctViewList.Next
If Not (pDuctDefinition Is Nothing) Then 'And (Not IsNull(pDuctDefinition))
'MsgBox pfeat.OID & "-" & pDuctDefinition.availability & "-" & pDuctDefinition.ductID & "-" & pDuctDefinition.diameter
TextStream.WriteLine iCount & "," & pfeat.OID & "," & pDuctDefinition.availability & "," & pDuctDefinition.ductID & "," & pDuctDefinition.diameter & "," & pDuctDefinition.ductNumber & "," & pDuctDefinition.Material


End If
Loop While Not (pDuctDefinition Is Nothing) ' And (Not IsNull(pDuctDefinition))
End If
Loop While Not (pDuctViewList Is Nothing) ' And (Not IsNull(pDuctViewList))
End If
Set pDuctView = Nothing
Set pDuctBankConfig = Nothing
Set pBlob = Nothing
myEnd:
Set pfeat = pftCursor.NextFeature
Loop
TextStream.Close
Set TextStream = Nothing
Set FileSystem = Nothing
End Sub

code to break blob

Sub Test_break_blob()'create a file to save the broken information'it will be replaced by writing a database tablestrFilename = "C:\testBreakBlob.txt"
'need to set a reference to Microsoft Scripting LibraryDim FileSystem As FileSystemObject, TextStream As TextStreamSet FileSystem = CreateObject("Scripting.FileSystemObject")Set TextStream = FileSystem.CreateTextFile(strFilename)'set up the system'getting the layer and selectionset'in the future, we may consider write an AutoUpdater
Dim pMap As IMapDim pmxDoc As IMxDocumentSet pmxDoc = ThisDocumentSet pMap = pmxDoc.ActiveViewDim pftLayer As IFeatureLayerSet pftLayer = pMap.Layer(0) ' assume one layer, but in the future should find the layer from TOC
Dim pftClass As IFeatureClassSet pftClass = pftLayer.FeatureClassDim pfeat As IFeatureDim pftCursor As IFeatureCursor'getting all featuresSet pftCursor = pftClass.Search(Nothing, False)
Set pfeat = pftCursor.NextFeatureDo While Not (pfeat Is Nothing) 'finding the configuration field Dim pField As IField Dim nBlob As Long nBlob = pftClass.FindField("configuration") Set pField = pfeat.Fields.Field(nBlob)

'getting the value from Blob field Dim pBlob As IMemoryBlobStream Set pBlob = pfeat.Value(nBlob)
'create the duckbankconfig and ductview objecs Dim pDuctBankConfig As IMMDuctBankConfig Dim pDuctView As IMMDuctView Set pDuctBankConfig = New MMDuctBankConfig Set pDuctView = New MMDuctView 'asociate list objects with duct configuration object Dim pDuctBankConfigList As ID8List Dim pDuctViewList As ID8List Set pDuctViewList = pDuctView Set pDuctBankConfigList = pDuctBankConfig Dim pMMListItem As IMMPersistentListItem Set pMMListItem = pDuctBankConfigList 'load the blob into list objects pMMListItem.LoadFromStream pBlob Dim pDuctDefinition As IMMDuctDefinition
If Not (pDuctBankConfigList Is Nothing) Then 'And (Not IsNull(pDuctBankConfigList)) pDuctBankConfigList.Reset Do Set pDuctViewList = pDuctBankConfigList.Next If Not (pDuctViewList Is Nothing) Then 'And (Not IsNull(pDuctViewList)) pDuctViewList.Reset Do Set pDuctDefinition = pDuctViewList.Next If Not (pDuctDefinition Is Nothing) Then 'And (Not IsNull(pDuctDefinition)) 'MsgBox pfeat.OID & "-" & pDuctDefinition.availability & "-" & pDuctDefinition.ductID & "-" & pDuctDefinition.diameter TextStream.WriteLine pfeat.OID & "," & pDuctDefinition.availability & "," & pDuctDefinition.ductID & "," & pDuctDefinition.diameter & "," & pDuctDefinition.ductNumber & "," & pDuctDefinition.Material pductdefinition. End If Loop While Not (pDuctDefinition Is Nothing) ' And (Not IsNull(pDuctDefinition)) End If Loop While Not (pDuctViewList Is Nothing) ' And (Not IsNull(pDuctViewList)) End If Set pDuctView = Nothing Set pDuctBankConfig = Nothing Set pBlob = Nothing Set pfeat = pftCursor.NextFeatureLoopTextStream.CloseSet TextStream = NothingSet FileSystem = NothingEnd Sub

List data source

Sub List_Source()
Dim mxDoc As IMxDocument
Dim pMap As iMap
Set mxDoc = ThisDocument
Set pMap = mxDoc.FocusMap
Dim pLayer As ILayer
Dim pDataLayer As IDataLayer2
Dim pftlayer As IFeatureLayer

For i = 0 To pMap.LayerCount - 1
Set pLayer = pMap.Layer(i)
If TypeOf pLayer Is IDataLayer Then
Set pDataLayer = pLayer
Dim pdataSrc As IDatasetName
Set pdataSrc = pDataLayer.DataSourceName
Debug.Print pLayer.Name & "," & pdataSrc.WorkspaceName.BrowseName & "," & pdataSrc.WorkspaceName.PathName & "," & pdataSrc.WorkspaceName.Type
'Debug.Print pDataLayer.DataSourceName
End If
Next

End Sub