Search notes:

Powershell module: SQL Developer

The aim of the PowerShell module SQL Developer is to manage Oracle SQL Developer settings from the command line. 
set-SQLDeveloperFontFamily                Consolas
set-SQLDeveloperFontSize                  11

set-SQLDeveloperArrayFetchSize            100

set-SQLDeveloper_NLS_DATE_FORMAT         'yyyy-mm-dd hh24:mi:ss'
set-SQLDeveloper_NLS_TIMESTAMP_FORMAT    'yyyy-mm-dd hh24:mi:ssXff'
set-SQLDeveloper_NLS_TIMESTAMP_TZ_FORMAT 'yyyy-mm-dd hh24:mi:ssXff tzr'

set-SQLDeveloper_NULL_text               '-'
set-SQLDeveloper_NULL_color               NONE

set-SQLDeveloperUseCaseDelimitedWord     $false

get-SQLDeveloperFontFamily
get-SQLDeveloperFontSize

add-SQLDeveloperCodeTemplate             trf  "select value trace_file from v`$diag_info where name = 'Default Trace File';"

get-SQLDeveloperUserConnectionsPSObjects  | select-object name, password

General get- functions

get-SQLDeveloperUserInformationDirectory Returns the user information directory as a System.IO.DirectoryInfo object
get-SQLDeveloperUserPreferencesFileName Returns the full path to the user preference file as a string (for example C:\Users\Rene\AppData\Roaming\SQL Developer\system19.2.1.247.2212/o.sqldeveloper/product-preferences.xml)
get-SQLDeveloperUserPreferencesXml Returns the content of the user preference file as a System.Xml.XmlDocument object.
get-SQLDeveloperUserConnectionsFileName Returns the name of the connections file (if it is a JSON file), for example C:\Users\Rene\AppData\Roaming\SQL Developer\system19.2.1.247.2212/o.jdeveloper.db.connection/connections.json
get-SQLDeveloperUserConnectionsPSObjects Returns the content of the connections file as a psObject object.
get-SQLDeveloperDBSystemId Returns the DB System ID.
add-SQLDeveloperCodeTemplate Adds a code template. See menu Tools -> Preferences -> Code Editor -> Code Templates)

get- and set- functions for settings

The following list includes the get- and set- functions for settings found in the user preference file (whose name can be determined with the function get-SQLDeveloperUserPreferencesFileName).
The names must be prependeded by get-SQLDeveloper or get-SQLDeveloper_ (for example get-SQLDeveloperFontSize).
FontSize
FontFamily
TNS_NAMES_directory
NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, NLS_TZ_FORMAT
NULL_text The value that is displayed in the result grid for null values.
NULL_color The color with which a null value is displayed.
caseDelimitedWord Set to $false in order to jump over underscors when using ctrl+arrow (rather than to treat them as word boundaries). Corresponds to Use Change of Case As Word Boundary in Tools -> Preferences -> Code Editor. see also Jeff Smith's tweet)
NewWorksheetConnection
ArrayFetchSize
glogin A boolean that controls if the glogin.sql file is sourced on startup
StartupScript The path to a script that is executed when SQL developer starts up (configured under the menu Tools -> Preferences -> Database: Filename for connection startup script)
UsethickDriver A boolean that controls if the Java thick driver is used to establish a connection
KerberosThinConfigFile
KerberosThinCredentialCache

Source code

The source code for this module is hosted in this GitHub repository.

SQL-Developer.psm1

set-strictMode -version 3

function get-SQLDeveloperUserInformationDirectory {

   $dir = get-childItem -attributes directory "$env:appdata\SQL Developer\system*" | where-object {$_.name -match '^system(\d+)\.(\d+)\.(\d+)\.(\d+)\.(\d+)$' }

   if ($dir -eq $null) {
      write-textInConsoleErrorColor "User information directory does not exist or was not found."
      return $null
   }

   if ($dir -is [array]) {
      write-textInConsoleErrorColor "Unable to determine user information directory uniquely, following directories were found:"
      foreach ($d in $dir) {
          write-host "   $d"
      }

      return $null
   }

   return $dir
}

# { User preference file

function get-SQLDeveloperUserPreferencesFileName {

   $userInfoDir = get-SQLDeveloperUserInformationDirectory
   if ($userInfoDir -eq $null) {
      return
   }

   $userPreferencesFile = "$userInfoDir/o.sqldeveloper/product-preferences.xml"

   if (! (test-path $userPreferencesFile) ) {
      write-textInConsoleErrorColor "User Preferences file ($userPreferencesFile) not found."
      return $null
   }

   return $userPreferencesFile
}

function get-SQLDeveloperUserPreferencesXml {

   $productPreferencesFile = get-SQLDeveloperUserPreferencesFileName
   if ($productPreferencesFile -eq $null) {
      return
   }

   [xml] $doc = new-object xml
   $doc.Load($productPreferencesFile)

   return $doc

}

# }

# { User connections file

function get-SQLDeveloperUserConnectionsFileName {

   $userInfoDir = get-SQLDeveloperUserInformationDirectory
   if ($userInfoDir -eq $null) {
      return
   }

   $connectionsJsonFileName = "$userInfoDir/o.jdeveloper.db.connection/connections.json"

   if (test-path $connectionsJsonFileName) {
      return $connectionsJsonFileName
   }

   $connectionsXMLfileName = "$userInfoDir/o.jdeveloper.db.connection/connections.xml"
   if (test-path $connectionsXMLfileName) {
      return $connectionsXMLfileName
   }

   write-textInConsoleErrorColor "Neither $connectionsJsonFileName nor $connectionsXMLfileName found"
   return $null
}

function get-SQLDeveloperUserConnectionsPSObjects {

   function local:null-if-nothing($x, $y) {if ($x.psObject.properties.name -contains $y){$x.$y} else {$null} }

   $connections = (convertFrom-json (get-content (get-SQLDeveloperUserConnectionsFileName))).connections

   foreach ($connection in $connections) {
      $ret = new-object psObject -property ([ordered] @{
         name                             = $connection.name
         type                             = $connection.type
         subtype                          = null-if-nothing   $connection.info  subtype
         role                             = null-if-nothing   $connection.info  role
         SavePassword                     = null-if-nothing   $connection.info  SavePassword
         OracleConnectionType             = null-if-nothing   $connection.info  OracleConnectionType
         ProxyType                        = null-if-nothing   $connection.info  PROXY_TYPE
         RaptorConnectionType             = null-if-nothing   $connection.info  RaptorConnectionType
         customUrl                        = null-if-nothing   $connection.info  customUrl
         oraDriverType                    = null-if-nothing   $connection.info  oraDriverType
         NoPasswordConnection             = null-if-nothing   $connection.info  NoPasswordConnection
         password                         = null-if-nothing   $connection.info  password
         hostname                         = null-if-nothing   $connection.info  hostname
         driver                           = null-if-nothing   $connection.info  driver
         port                             = null-if-nothing   $connection.info  port
         OSAuthentication                 = null-if-nothing   $connection.info  OS_AUTHENTICATION
         IS_PROXY                         = null-if-nothing   $connection.info  IS_PROXY
         KerberosAuthentication           = null-if-nothing   $connection.info  KerberosAuthentication
         ProxyUserName                    = null-if-nothing   $connection.info  PROXY_USER_NAME
         user                             = null-if-nothing   $connection.info  user

         sqlserver_default_password       = null-if-nothing   $connection.info  sqlserver_default_password
         sqlserver_domain                 = null-if-nothing   $connection.info  sqlserver_domain
         sqlserver_windows_authentication = null-if-nothing   $connection.info  sqlserver_windows_authentication

      })

      $ret
   }
}

# }

function get-SQLDeveloperDBSystemId {
 #
 # TODO: Similar code in function get-preferences
 #
   [xml] $doc = get-SQLDeveloperUserPreferencesXml

   $nameTable = new-object System.Xml.NameTable
   $nsMgr     = new-object System.Xml.XmlNamespaceManager $nameTable
   $nsMgr.AddNamespace('ide', 'http://xmlns.oracle.com/ide/hash')

   $valueName = 'db.system.id'

   $value = $doc.SelectSingleNode('/ide:preferences//value[@n="' + $valueName + '"]', $nsMgr)

   if ($value -eq $null) {
      return $null
   }
   return $value.GetAttribute('v')
}

# { Internal helper functions

function set-preference {

   param (
      [string] $hashName,
      [string] $valueName,
      [object] $newValue
   )

   if ($newValue -is [bool]) {
      if ($newValue) { $newValue_ = 'true' } else { $newValue_ = 'false' }
   }
   else {
      $newValue_ = $newValue
   }

   $productPreferencesFile = get-SQLDeveloperUserPreferencesFileName
   if ($productPreferencesFile -eq $null) {
      return
   }

   $nameTable = new-object System.Xml.NameTable
   $nsMgr     = new-object System.Xml.XmlNamespaceManager $nameTable
   $nsMgr.AddNamespace('ide', 'http://xmlns.oracle.com/ide/hash')

   [xml] $doc = get-SQLDeveloperUserPreferencesXml

   [System.Xml.XmlElement] $preferences = $doc.SelectSingleNode('/ide:preferences', $nsMgr)

   if ($preferences -eq $null) {
      write-textInConsoleErrorColor "Node /ide:preferences was not found in $productPreferencesFile"
   }

   $hashElem = $preferences.SelectSingleNode('hash[@n="' + $hashName + '"]', $nsMgr) # FontSizeOptions"]', $nsMgr)
   if ($hashElem -eq $null) {
      $hashElem = $doc.CreateElement('hash')
      $hashElem.SetAttribute('n', $hashName)
      $null = $preferences.AppendChild($hashElem)
   }

   $valueElem = $hashElem.SelectSingleNode('value[@n="' + $valueName + '"]')
   if ($valueElem -eq $null) {
      $valueElem = $doc.CreateElement('value')
      $valueElem.SetAttribute('n', $valueName)
      $null = $hashElem.AppendChild($valueElem)
   }

   $valueElem.SetAttribute('v', $newValue_)

   write-xmlToFile $doc $productPreferencesFile
<# Replaced in version 6
 #
 # Use Stream writer to control line ending:
 #
   $sw = new-object System.IO.StreamWriter $productPreferencesFile
   $sw.NewLine = "`n"

   $xw = new-object System.Xml.XmlTextWriter $sw
   $xw.Formatting  = 'Indented'
   $xw.IndentChar  = ' '
   $xw.Indentation = 3

   $doc.Save($xw)
   $sw.Close()
#>
}

function get-preference {

 #
 # TODO: Similar code in get-SQLDeveloperDBSystemId
 #

   param (
      [string] $hashName,
      [string] $valueName
   )

   [xml] $doc = get-SQLDeveloperUserPreferencesXml

   $nameTable = new-object System.Xml.NameTable
   $nsMgr     = new-object System.Xml.XmlNamespaceManager $nameTable
   $nsMgr.AddNamespace('ide', 'http://xmlns.oracle.com/ide/hash')

   $value = $doc.SelectSingleNode('/ide:preferences/hash[@n="' + $hashName + '"]/value[@n="' + $valueName + '"]', $nsMgr)


   if ($value -eq $null) {
      return $null
   }
   return $value.GetAttribute('v')

}

function print-warning-unless-file-exists {
   param (
      [string] $supposedFileName
   )

   if ($supposedFileName) {
      if (! (test-path -pathType leaf $supposedFileName) ) {
         write-textInConsoleWarningColor "File $supposedFileName does not exist"
      }
   }
}

   function write-xmlToFile {

      param (
         [xml]    $xml,
         [string] $file
      )

      $sw = new-object System.IO.StreamWriter $file
      $sw.NewLine = "`n"

      $xw = new-object System.Xml.XmlTextWriter $sw
      $xw.Formatting  = 'Indented'
      $xw.IndentChar  = ' '
      $xw.Indentation = 3

      $xml.Save($xw)
      $sw.Close()
   }

# }

# { get, set a setting

# { Font …

# { FontFamily

function get-SQLDeveloperFontFamily {
   return (get-preference FontSizeOptions fontFamily)
}

function set-SQLDeveloperFontFamily {

   param (
      [string] $newFontName
   )

   set-preference FontSizeOptions fontFamily $newFontName

}

# }

# { FontSize

function get-SQLDeveloperFontSize {
   return (get-preference FontSizeOptions fontSize)
}

function set-SQLDeveloperFontSize {

   param (
      [int] $newFontSize
   )

   set-preference FontSizeOptions fontSize $newFontSize

   <# TODO: Remove this!

   $nameTable = new-object System.Xml.NameTable
   $nsMgr     = new-object System.Xml.XmlNamespaceManager $nameTable
   $nsMgr.AddNamespace('ide', 'http://xmlns.oracle.com/ide/hash')

   [xml] $doc = new-object xml

   $productPreferencesFile = "$(get-SQLDeveloperUserInformationDirectory)/o.sqldeveloper/product-preferences.xml"

   if (! (test-path $productPreferencesFile)) {
      write-textInConsoleErrorColor "product preference file $productPreferencesFile not found"
      return
   }
   $doc.Load($productPreferencesFile)

   $preferences = $doc.SelectSingleNode('/ide:preferences', $nsMgr)

 # $fontSizeOptions = $doc.SelectSingleNode('/ide:preferences/hash[@n="FontSizeOptions"]', $nsMgr)
   $hash_fontSizeOptions = $preferences.SelectSingleNode('hash[@n="FontSizeOptions"]', $nsMgr)
   if ($hash_fontSizeOptions -eq $null) {
      $hash_fontSizeOptions = $doc.CreateElement('hash')
      $hash_fontSizeOptions.SetAttribute('n', 'FontSizeOptions')
      $null = $preferences.AppendChild($hash_fontSizeOptions)
   }

   $value_fontSize = $hash_fontSizeOptions.SelectSingleNode('value[@n="fontSize"]')
   if ($value_fontSize -eq $null) {
      $value_fontSize = $doc.CreateElement('hash')
      $value_fontSize.SetAttribute('n', 'fontSize')
      $null = $hash_fontSizeOptions.AppendChild($value_fontSize)
   }

   $value_fontSize.SetAttribute('v', $newFontSize)

 #
 # Use Stream writer to control line ending:
 #
   $sw = new-object System.IO.StreamWriter $productPreferencesFile
   $sw.NewLine = "`n"

   $xw = new-object System.Xml.XmlTextWriter $sw
   $xw.Formatting  = 'Indented'
   $xw.IndentChar  = ' '
   $xw.Indentation = 3

   $doc.Save($xw)
   $sw.Close()

#>
}
# }

# { useCaseDelimitedWord

function get-SQLDeveloperUseCaseDelimitedWord {
   return (get-preference EditorOptions useCaseDelimitedWord)
}

function set-SQLDeveloperUseCaseDelimitedWord {

   param (
      [bool] $useCaseDelimitedWord
   )

   set-preference EditorOptions useCaseDelimitedWord $useCaseDelimitedWord
}

# }

# }
# { TNS_NAMES_directory

function set-SQLDeveloper_TNS_NAMES_directory {
   param (
      [string] $newValue
   )

 #
 # TODO: use print-warning-unless-file-exists to check for existence of directory
 #

   set-preference DBConfig TNS_NAMES_DIR $newValue

}

function get-SQLDeveloper_TNS_NAMES_directory {
   get-preference DBConfig TNS_NAMES_DIR
}

# }
# { NLS …
# { NLS_DATE_FORMAT

function set-SQLDeveloper_NLS_DATE_FORMAT {
   param (
      [string] $newFormat
   )

   set-preference DBConfig NLS_DATE_FORM $newFormat
}

function get-SQLDeveloper_NLS_DATE_FORMAT {
   get-preference DBConfig NLS_DATE_FORM
}

# }
# { NLS_TIMESTAMP_FORMAT

function set-SQLDeveloper_NLS_TIMESTAMP_FORMAT {
   param (
      [string] $newFormat
   )

   set-preference DBConfig NLS_TS_FORM $newFormat
}

function get-SQLDeveloper_NLS_TIMESTAMP_FORMAT {
   get-preference DBConfig NLS_TS_FORM
}

# }
# { NLS_TIMESTAMP_TZ_FORMAT

function set-SQLDeveloper_NLS_TIMESTAMP_TZ_FORMAT {
   param (
      [string] $newFormat
   )

   set-preference DBConfig NLS_TS_TZ_FORM $newFormat
}

function get-SQLDeveloper_NLS_TIMESTAMP_TZ_FORMAT {
   get-preference DBConfig NLS_TS_TZ_FORM
}

# }
# }
# { NULL…
# { NULL_text

function set-SQLDeveloper_NULL_text {
   param (
      [string] $newFormat
   )

   set-preference DBConfig NULLDISPLAY $newFormat
}

function get-SQLDeveloper_NULL_text {
   get-preference DBConfig NULLDISPLAY
}

# }
# { NULL_color

function set-SQLDeveloper_NULL_color {
   param (
      [string] $newValue
   )

   set-preference DBConfig NULLCOLOR $newValue
}

function get-SQLDeveloper_NULL_color {
   get-preference DBConfig NULLCOLOR
}

# }
# }
# { NewWorksheetConnection

function set-SQLDeveloperNewWorksheetConnection {
   param (
      [bool] $newValue
   )

   set-preference DBConfig UNSHAREDWORKSHEETOPEN $newValue
}

function get-SQLDeveloperNewWorksheetConnection {

   get-preference DBConfig UNSHAREDWORKSHEETOPEN
}

# }
# { glogin

function get-SQLDeveloper_glogin {
  (get-preference DBConfig GLOGIN) -eq 'true'
}

function set-SQLDeveloper_glogin {
   param (
      [boolean] $newValue
   )

#  if ($newValue) { $newValue_ = 'true' } else { $newValue_ = 'false'}

   set-preference DBConfig GLOGIN $newValue
}

# }
# { Startup Script

function get-SQLDeveloperStartupScript {
   return (get-preference DBConfig '')
}

function set-SQLDeveloperStartupScript {
   param (
      [string] $newValue
   )

   print-warning-unless-file-exists $newValue

   set-preference DBConfig '' $newValue
}

# }
# { ArrayFetchSize

function get-SQLDeveloperArrayFetchSize {
 #
 # If null, defaults to 50
 #
   return (get-preference DBConfig ARRAYFETCHSIZE)
}

function set-SQLDeveloperArrayFetchSize {
   param (
      [int] $newValue
   )

   if ($newValue -lt 50 -or $newValue -gt 200) {
      write-textInConsoleWarningColor "Value must be between 50 and 200"
      return
   }
   set-preference DBConfig ARRAYFETCHSIZE $newValue
}
# }
# { UseThickDriver

function get-SQLDeveloperUseThickDriver {
   return (set-preference DBConfig USE_THICK_DRIVER)
}

function set-SQLDeveloperUseThickDriver {
   param (
      [bool] $newValue
   )
   set-preference DBConfig USE_THICK_DRIVER $newValue
}

# }
# { Kerberos …
# { KerberosThinConfigFile

function get-SQLDeveloperKerberosThinConfigFile {
   return (get-preference DBConfig KERBEROS_CONFIG)
}

function set-SQLDeveloperKerberosThinConfigFile {
   param (
      [string] $nevValue
   )

   print-warning-unless-file-exists $newValue
   set-preference DBConfig KERBEROS_CONFIG
}
# }
# { KerberosThinCredentialCache

function get-SQLDeveloperKerberosThinCredentialCache {
   return (get-preference DBConfig KERBEROS_CACHE)
}

function set-SQLDeveloperKerberosThinCredentialCache {
   param (
      [string] $nevValue
   )
   set-preference DBConfig KERBEROS_CACHE
}

# }
# }

# }

function add-SQLDeveloperCodeTemplate {

   param(
      [string] $abbreviation,
      [string] $expansion
   )

   $codeTemplateFile = "$env:appdata/SQL Developer/CodeTemplate.xml"

   [xml] $doc = new-object xml
   $doc.Load($codeTemplateFile)

   $rows = (select-xml -xml $doc -xPath '/rows').node

   $newAbbr   = $doc.CreateElement('row'  )
   $newKey    = $doc.CreateElement('key'  )
   $newValue  = $doc.CreateElement('value')

   $newKey.InnerText   = $abbreviation
   $newValue.InnerText = $expansion

   $null = $newAbbr.AppendChild($newKey  )
   $null = $newAbbr.AppendChild($newValue)
   $null = $rows.AppendChild($newAbbr)

   write-xmlToFile $doc $codeTemplateFile

}
Github repository ps-module-SQL-Developer, path: /SQL-Developer.psm1

Manifest file (SQL-Developer.psd1)

@{
   RootModule        = 'SQL-Developer.psm1'
   ModuleVersion     = '0.6'

   RequiredModules   = @(
      'console'
   )

   FunctionsToExport = @(
     'get-SQLDeveloperUserInformationDirectory',

     'get-SQLDeveloperUserPreferencesFileName'    , 'get-SQLDeveloperUserPreferencesXml',
     'get-SQLDeveloperUserConnectionsFileName'    , 'get-SQLDeveloperUserConnectionsPSObjects',

     'get-SQLDeveloperDBSystemId',

     'set-SQLDeveloperFontFamily'                 , 'get-SQLDeveloperFontFamily',
     'set-SQLDeveloperFontSize'                   , 'get-SQLDeveloperfontSize',

     'set-SQLDeveloperUseCaseDelimitedWord'       , 'get-SQLDeveloperUseCaseDelimitedWord',

     'set-SQLDeveloper_TNS_NAMES_directory'       , 'get-SQLDeveloper_TNS_NAMES_directory',

     'set-SQLDeveloper_NLS_DATE_FORMAT'           , 'get-SQLDeveloper_NLS_DATE_FORMAT',
     'set-SQLDeveloper_NLS_TIMESTAMP_FORMAT'      , 'get-SQLDeveloper_NLS_TIMESTAMP_FORMAT',
     'set-SQLDeveloper_NLS_TIMESTAMP_TZ_FORMAT'   , 'get-SQLDeveloper_NLS_TIMESTAMP_TZ_FORMAT',

     'set-SQLDeveloper_NULL_text'                 , 'get-SQLDeveloper_NULL_text',
     'set-SQLDeveloper_NULL_color'                , 'get-SQLDeveloper_NULL_color',

     'set-SQLDeveloperNewWorksheetConnection'     , 'get-SQLDeveloperNewWorksheetConnection'     ,

     'set-SQLDeveloperArrayFetchSize'             , 'get-SQLDeveloperArrayFetchSize'             ,
     'set-SQLDeveloperUseThickDriver'             , 'get-SQLDeveloperUseThickDriver'             ,
     'set-SQLDeveloperStartupScript'              , 'get-SQLDeveloperStartupScript'              ,
     'set-SQLDeveloperKerberosThinConfigFile'     , 'get-SQLDeveloperKerberosThinConfigFile'     ,
     'set-SQLDeveloperKerberosThinCredentialCache', 'get-SQLDeveloperKerberosThinCredentialCache',

     'set-SQLDeveloper_glogin'                    , 'get-SQLDeveloper_glogin'                    ,

     'add-SQLDeveloperCodeTemplate'

   )

   AliasesToExport   = @()
}
Github repository ps-module-SQL-Developer, path: /SQL-Developer.psd1

History

V.2 return XML file if .json not found in get-SQLDeveloperUserConnectionsFileName.
V.3 set- and get-SQLDeveloperArrayFetchSize / Fix bug in set-preference
V.4 set- and get-SQLDeveloperUseCaseDelimitedWord
V.5 Print directories if get-SQLDeveloperUserInformationDirectory finds more than one (2021-11-26)
V.6 add-SQLDeveloperCodeTemplate (2021-11-26)

TODO

Try to implement the 19.2 encrypt/decrypt algorithm from maaaaz/sqldeveloperpassworddecryptor

See also

René's simple PowerShell modules

Index