Powershell Connection to Oracle Database

Use the server:port/service syntax.

$dataSource="server.network:1522/service1.x.y.z"

Here is my (Powershell, but you can adapt to C#) code to do this with ODP.NET managed data access.... which I load directly using the Powershell Add-Type but in C# would be a using/reference...

1. create a connection, using the connection builder class to ensure the resulting connection string is exactly as desired. Of course you can simplify but this is self-documenting:

function New - OracleConnection { <
  #
  .SYNOPSIS# generate a well - formed connection string with individual properties
  Create and open a new Oracle connection using optional connectionstring argument

    .DESCRIPTION
  Create and open a new Oracle connection using optional connectionstring argument

    .EXAMPLE
  New - OracleConnection
  New - OracleConnect - connectionString "My well-formed Oracle connections string"

    .NOTES
  Connection is opened here by
  default# > #Add - Type - Path ".\Oracle.ManagedDataAccess.dll" [OutputType([Oracle.ManagedDataAccess.Client.OracleConnection])]
  Param(
    [Parameter(Mandatory = $false)]
    [string] $theConnectionString, [Parameter(Mandatory = $false)]
    [string] $openOnCreate = "1"#
    means true - open the connection...
  )[Oracle.ManagedDataAccess.Client.OracleConnection] $con = New - Object - TypeName Oracle.ManagedDataAccess.Client.OracleConnection;
  if ([string]::IsNullOrEmpty($theConnectionString)) {#
    $dataSource = "*********:1521/******;"

    $conStringBuilder = New - Object Oracle.ManagedDataAccess.Client.OracleConnectionStringBuilder;
    $conStringBuilder["Data Source"] = $dataSource;
    $conStringBuilder["User ID"] = "*******";
    $conStringBuilder["Password"] = "*******";
    $conStringBuilder["Persist Security Info"] = $True;
    $conStringBuilder["Connection Lifetime"] = 180;
    $conStringBuilder["Connection Timeout"] = 10;
    $conStringBuilder["Pooling"] = $true;
    $conStringBuilder["Min Pool Size"] = 10;
    $conStringBuilder["Max Pool Size"] = 20;
    $conStringBuilder["Incr Pool Size"] = 5;
    $conStringBuilder["Decr Pool Size"] = 2;
    $conStringBuilder["Statement Cache Size"] = 200;
    $conStringBuilder["Statement Cache Purge"] = $false;#
    default

    $con.ConnectionString = $conStringBuilder.ConnectionString;
  } else {
    $con.ConnectionString = $theConnectionString;
  }

  if (Get - IsTrue - yesNoArg $openOnCreate) {
    if (-not(Get - ConnectionStateIsOpen($con))) {#
      attempt open, ignore error
      if already open.State is normally open after successful create
      try {
        $con.Open();
      } catch {
        $null;
      }
    }
  }

  Write - Output - NoEnumerate $con;

}

2. get a connection:

$con = New - OracleConnection;

3. open / close as necessary(it defaults to open...)

... ... if ($con.State - ne[System.Data.ConnectionState]::Open) { $con.Open();# no arguments that I know of ... }

4. use it and then close/dispose...

    $sql = "SELECT * FROM YOUR_TABLE t WHERE t.SOMETHING = :the_parm";    
        [Oracle.ManagedDataAccess.Client.OracleCommand]$cmd = New-Object -TypeName Oracle.ManagedDataAccess.Client.OracleCommand;    
        $cmd.Connection = Get-OracleConnection;
        $cmd.CommandText = $sql;
        $cmd.BindByName = $true;
        $dbType = ([Oracle.ManagedDataAccess.Client.OracleDbType]::VarChar2);
       
        $cmd.Parameters.Add(( New-Param -name "the_parm" -type $dbType -size 15  )).Value = $the_criteria_value  ;
        Write-Output -NoEnumerate ($cmd.ExecuteScalar());
        $cmd.Connection.Close();
        $cmd.Dispose();
        

It could very well be an issue with oracle 12.2.x
I had to add the following lines to the sqlnet.ora file on the database server to allow connections from older oracle clients: SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8 SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

Once added I could login with oracle 10g and 11g clients

Probably irrelevant but this parameter: [string] $openOnCreate = "1"#

according to the PS goddesses, the recommended syntax should be: [switch] $OpenOnCreate ...

...where the presence of the switch means $True....

and the check should look like:

if ($OpenOnCreate) ...

so the call should look like this to just create a new connection (to be opened and used later) ... $con = New-OracleConnection

or, if opening the connection as part of the create, just include the [switch]

$con = New-OracleConnection -OpenOnCreate

All of which does not detract from the solution. I would also offer that the code creating the connection string inside this might be moved to a separate dedicated function?

I had the exact same issue and I switched from ODAC version 12.2.0.1.0 to version 11.2.0.3 and it worked like a charm to Open connection and read data. Hope this helps. Thanks, SK