ODBC Driver for Oracle Sales

Build 21.0.7930

Execute Queries on a Linked Server

This section describes how to execute queries to the Oracle Sales linked server from any tool that can connect to SQL Server.

When executing queries from a linked server, you must use a four-part name format, as shown below.

[<Linked Server Name>].[<DSN Name>].[<Schema Name (normally but not always the service name)>].[<Table Name>]

Where:

  • Linked Server Name is the name of the linked server that you created.
  • DSN Name is the name of the data source.
  • Schema Name is the name of the system schema or user-defined schema.
  • Table Name is the name of the table you are copying data from.

SELECT Statements

To create new records:

SELECT * FROM [Linked Server Name].[CData OracleSalesCloud Sys].[OracleSalesCloud].[Opportunities] WHERE CreatedBy = 'Jack'

INSERT, UPDATE, and DELETE Statements

To insert, update, or delete a single row of data (that is, not a batch of records), you need to use the four-part name. For example:

INSERT INTO [Linked Server Name].[CData OracleSalesCloud Sys].[OracleSalesCloud].[Opportunities] (CreatedBy) VALUES ('Jack');

To retrieve newly created IDs from an INSERT operation, you can query the sys_identity system table. For example:

SELECT * FROM OPENQUERY([Linked Server Name], 'SELECT * FROM sys_identity');

Bulk Operations

To perform bulk operations, you can use syntax similar to the following:

You can run a bulk INSERT using a SELECT query in place of a VALUES list.

INSERT INTO [Linked Server Name].[CData OracleSalesCloud Sys].[OracleSalesCloud].[Opportunities] ([CreatedBy])
SELECT [CreatedBy] from [CData].[dbo].[Local_Opportunities]

You can run a bulk UPATE using a JOIN with a local table.

UPDATE [Linked Server Name].[CData OracleSalesCloud Sys].[OracleSalesCloud].[Opportunities] SET [Opportunities].[CreatedBy] = CData.dbo.[Local_Opportunities].[Local_CreatedBy]
FROM CData.dbo.[Local_Opportunities] INNER JOIN [Linked Server Name].[CData OracleSalesCloud Sys].[OracleSalesCloud].[Opportunities] ON CData.dbo.[Local_Opportunities].[Id] = [Opportunities].[Id]

You can run a bulk DELETE using the IN filter with sub-SELECT query.

DELETE FROM [Linked Server Name].[CData OracleSalesCloud Sys].[OracleSalesCloud].[Opportunities] WHERE [Id] IN (SELECT [Id] FROM CData.dbo.Local_Opportunities)

Execute Stored Procedures

To execute stored procedures, you must first enable RPC and RPC Out in the settings for the linked server. Then, you can use the following set of commands to run the stored procedure:

DECLARE @RunStoredProcSQL VARCHAR(1000);
SET @RunStoredProcSQL = 'EXEC SelectEntries ObjectName=''Account''';
EXEC (@RunStoredProcSQL) AT [Linked Server Name];

Copyright (c) 2021 CData Software, Inc. - All rights reserved.
Build 21.0.7930