It is possible to execute an Oracle SQL file from PowerShell by leveraging SQLPlus and the PowerShell Execution Method.
NOTE: SQLPlus must be installed on the server where the Job will execute. Some script modification required.
Creating a PowerShell Job to run Oracle SQL files
- Create a job using the PowerShell Execution Method.
- Create three Parameters on the Job. These Parameters will be referenced in the Job Source.
Users may set Default values for these Parameters as necessary.
- OracleUser - The User that performs the actual query. This must be a defined JAMS User with authentication permissions to Oracle.
- ScriptFile - The location of the Oracle SQL script file, on the server where the Job will run.
- OracleDatabase - The Database that the query file will run on.
- Navigate to the Source Tab of the Job, and paste in the script below. Modify the script to reference the relevant Oracle Home information.
Import-Module JAMS $creds = (Get-JAMSCredential <<OracleUser>>).GetCredential($null,$null)
#Customize Oracle Home as necessary
set ORACLE_HOME=C:\app\oracle\product\22.214.171.124\client_1 $sql = @" @<<ScriptFile>> "@ $password = $creds.Password $userName = $creds.UserName $connection = $userName + "/" + $password + "@<<OracleDatabase>>" $sql | sqlplus $connection
- Apply other Job settings as desired.
- Save and Close the Job.