Using Windows Subsystem for Linux and PowerShell together to access an Oracle Database

AccessOracleFromWSLandPowershell.ps1.md

Quick example on how to mix WSL SQLPlus and WSL (Windows Subsystem for Linux) and powershell to access your database.

Enable WSL for Linux

1. First step is to enable Windows Subsystem for Linux in Windows 10. I like using the Linux tool called "pass" as my password manager. Since this tool works completly in the terminal I can use it to automate my workflows.

2. Once WSL is enabled proceed to install a Linux distribution for your WSL in the Windows Store. This will give you a debian based linux available for installing the next applications.

3. Install gpg for encryption in your WSL instance. This is required by "pass" in order to encrypt your passwords using your generated keys Refer to gnupg to generate your key pairs

sudo apt-get install gpg

4. Install pass on your WSL instance

sudo apt-get install pass

5. Initialize the password manager. This will use your default encryption key for securely storing your passwords. Refer to Pass for learning how to use it.

pass init

6. Install SqlPlus client in your Windows 10.

7. Open PowerShell ISE and create/update $HOME\Documents\WindowsPowerShell\profile.ps1 adding the below snippet.

function sqlplus-prod
{
    sqlplus.exe "ernesto/$(wsl pass oracle/prod/mytnsdbname/ernesto)@mytnsdbname"
}

Exmplain: -- This is a powershell function using sqlplus to execute queries.

• This snippet uses sqlplus.exe to connect to the database.
• In the above example the user name is ernesto
• The database is refered by a TNSNAMES id for this example is mytnsdbname
• The $() will execute powershell script and replace the return string (In this case the password) in the connection string.
• wsl pass is used to invoke the linux tool pass from the Windows Subsystem for Linux.
• oracle/prod/mytnsdbname/ernesto is the path in where I stored my password within the password manager context.

This is one way to execute Oracle SQL queries from powershell terminal, this is not the best way but gives an idea on how to integrate with a terminal based password manager.

Refer to How to run Execute-SQL like commandlets for powershell to acces an oracle database for a more functional way of accessing oracle databases using powershell.

Thanks , this helps a lot