Ready to get started?

Learn more about the CData ADO.NET Provider for Oracle Sales Cloud or download a free trial:

Download Now

Automate Oracle Sales Cloud Integration Tasks from PowerShell

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

The CData Cmdlets for Oracle Sales Cloud 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 Cloud.

Cmdlets or ADO.NET?

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

After obtaining the needed connection properties, accessing Oracle Sales Cloud data in PowerShell consists of three basic steps.

OracleSalesCloud 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 Cloud\lib\System.Data.CData.OracleSalesCloud.dll")
  2. Connect to Oracle Sales Cloud:

    $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 Cloud 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 Cloud 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 Cloud 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()