Automate Oracle Sales Integration Tasks from PowerShell

Ready to get started?

Download for a free trial:

Download Now

Learn more:

Oracle Sales ADO.NET Provider

Rapidly create and deploy powerful .NET applications that integrate with Oracle Sales marketing automation platform including Leads, Contacts, Opportunities, Accounts, and more!



Are you looking for a quick and easy way to access Oracle Sales data from PowerShell? We show how to use the Cmdlets for Oracle Sales and the CData ADO.NET Provider for Oracle Sales to connect to Oracle Sales data and synchronize, automate, download, and more.

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.

Cmdlets or ADO.NET?

The cmdlets are not only a PowerShell interface to the Oracle Sales API, 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.

After obtaining the needed connection properties, accessing Oracle Sales data in PowerShell consists of three basic 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 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 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 System.Data.CData.OracleSalesCloud.OracleSalesCloudParameter("@myId","001d000000YBRseAAH")) $cmd.ExecuteNonQuery()