Automate Oracle Sales Integration Tasks from PowerShell

The CData Cmdlets for Oracle Sales are standard PowerShell cmdlets that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to Oracle Sales.

PowerShell Cmdlets or ADO.NET Provider?

The Cmdlets are not only a PowerShell interface to Oracle Sales, but also an SQL interface; this tutorial shows how to use both to create, retrieve, update, and delete Oracle Sales data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for Oracle Sales. To access Oracle Sales data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for Oracle Sales.

Once you have acquired the necessary connection properties, accessing Oracle Sales data in PowerShell can be enabled in three steps.

Oracle Sales uses Basic authentication over SSL; after setting the following connection properties, you are ready to connect:

• Username: Set this to the user name that you use to log into your Oracle Cloud service.
• Password: Set this to your password.
• HostURL: Set this to the Web address (URL) of your Oracle Cloud service.

PowerShell

1. Install the module:

   Install-Module OracleSalesCloudCmdlets

2. Connect:

   $oraclesalescloud = Connect-OracleSalesCloud -HostURL "$HostURL" -Username "$Username" -Password "$Password"
   

3. Search for and retrieve data:

   $createdby = "Jack"
   $opportunities = Select-OracleSalesCloud -Connection $oraclesalescloud -Table "Opportunities" -Where "CreatedBy = `'$CreatedBy`'"
   $opportunities
   

   You can also use the Invoke-OracleSalesCloud cmdlet to execute SQL commands:

   $opportunities = Invoke-OracleSalesCloud -Connection $oraclesalescloud -Query 'SELECT * FROM Opportunities WHERE CreatedBy = @CreatedBy' -Params @{'@CreatedBy'='Jack'}
   

ADO.NET

1. Load the provider's assembly:

   [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for Oracle Sales\lib\System.Data.CData.OracleSalesCloud.dll")
    

2. Connect to Oracle Sales:

    
   $conn= New-Object System.Data.CData.OracleSalesCloud.OracleSalesCloudConnection("HostURL=https://my.host.oraclecloud.com; Username=abc123; Password=abcdef;")
   $conn.Open()
   

3. Instantiate the OracleSalesCloudDataAdapter, execute an SQL query, and output the results:

   $sql="SELECT OptyId, Name from Opportunities"
   
   $da= New-Object System.Data.CData.OracleSalesCloud.OracleSalesCloudDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.optyid $_.name
   }
    

Update Oracle Sales Data

PowerShell

Update-OracleSalesCloud -Connection $OracleSalesCloud -Columns @('OptyId','Name') -Values @('MyOptyId', 'MyName') -Table Opportunities -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.OracleSalesCloud.OracleSalesCloudCommand("UPDATE Opportunities SET CreatedBy='Jack' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.OracleSalesCloud.OracleSalesCloudParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Oracle Sales Data

PowerShell

Add-OracleSalesCloud -Connection $OracleSalesCloud -Table Opportunities -Columns @("OptyId", "Name") -Values @("MyOptyId", "MyName") 

ADO.NET

$cmd = New-Object System.Data.CData.OracleSalesCloud.OracleSalesCloudCommand("INSERT INTO Opportunities (CreatedBy) VALUES (@myCreatedBy)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.OracleSalesCloud.OracleSalesCloudParameter("@myCreatedBy","Jack")))
$cmd.ExecuteNonQuery()

Delete Oracle Sales Data

PowerShell

Remove-OracleSalesCloud -Connection $OracleSalesCloud -Table "Opportunities" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.OracleSalesCloud.OracleSalesCloudCommand("DELETE FROM Opportunities WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.OracleSalesCloud.OracleSalesCloudParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()