Get Oracle Sequence Value

Description:

The code below shows how to get a value from an Oracle database sequence. You would typically do this in order to set a field value to a number that was unique database-wide.

 

The code assumes the Editor is in use, to determine the workspace of the sequence. Alternatively, the workspace could be derived from a feature class or table.

How to use:

1. Paste the macro into VBA.
2. Modify the code to point to the desired sequence and run the macro.

Public Sub GetSequenceNumber()Sub GetSequenceNumber()
  Dim pEditor As IEditor
  Dim pFeatWorkspace As IFeatureWorkspace
  Dim pQueryDef As IQueryDef
  Dim pCursor As ICursor
  Dim pRow As IRow
  
  ' Find the workspace of the sequence
  ' Assume we are in VBA and already editing
  Dim pUID As New esriCore.UID
  pUID = "esriCore.Editor"
  Set pEditor = Application.FindExtensionByCLSID(pUID)
  Set pFeatWorkspace = pEditor.EditWorkspace
  
  ' Define the query
  Set pQueryDef = pFeatWorkspace.CreateQueryDef
  pQueryDef.SubFields = "myowner.mysequence.NEXTVAL"
  'if no 'sys', error message would raise to notice that no table was found
  pQueryDef.Tables = "sys.dual"
  Set pCursor = pQueryDef.Evaluate
  
  'Get the sequence value from the returned row
  Set pRow = pCursor.NextRow
  MsgBox pRow.Value(0)
End Sub