In the realm of database management and administration, Oracle remains a stalwart player, powering countless applications and systems worldwide.

When working with Oracle databases, PowerShell has emerged as a versatile tool for managing and automating tasks.

In the recent past we have given a task to connect oracle database and run the stored procedure via PowerShell.

We were able to accomplish it in our DEV and TEST environment successfully without any issues.

Here is the code snippet we were using: (we will share the entire code in some other blog)

####################################################################

$datasource = “devdb” #######here we entered the db name only

$connectionString = ‘User Id=’ + $username + ‘;Password=’ + $password + ‘;Data Source=’ + $datasource

$connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)

# Open Connection

$connection.open()

####################################################################

When moving the code to prod environment we encountered below error:

Exception calling “Open” with “0” argument(s): “ORA-12154: TNS:could not resolve the connect identifier specified”

As we were new to Oracle, we had tried multiple things mentioned on the internet and also thought it might be related to some security things and involved security but found no resolution.

What finally resolved the error was adding the server name:port in the data source.

####################################################################

$datasource = “devserver:1521/ devdb” #######here we entered the db name with the server it is hosted on so bypassing the name resolution

$connectionString = ‘User Id=’ + $username + ‘;Password=’ + $password + ‘;Data Source=’ + $datasource

$connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)

# Open Connection

$connection.open()

####################################################################

I hope this small TIP will assist anyone who is facing the same issue and will save lot of hours of troubleshooting.

 

 

Thanks for reading…

Tech Wizard

 

https://techwizard.cloud

https://syscloudpro.com/

PowerShell Fast Track