How to execute an Oracle SQL file from PowerShell

Follow

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

  1. Create a job using the PowerShell Execution Method.
  2. 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.
    1. OracleUser - The User that performs the actual query. This must be a defined JAMS User with authentication permissions to Oracle.
    2. ScriptFile - The location of the Oracle SQL script file, on the server where the Job will run.
    3. OracleDatabase - The Database that the query file will run on. 
  3. 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\12.1.0.2\client_1 $sql = @" @<<ScriptFile>> "@ $password = $creds.Password $userName = $creds.UserName $connection = $userName + "/" + $password + "@<<OracleDatabase>>" $sql | sqlplus $connection
  4. Apply other Job settings as desired.
  5. Save and Close the Job.

 

Have more questions? Submit a request

Comments