CData Python Connector for Oracle Sales

Build 20.0.7745


Describes the SELECT query processing that the provider can offload to the data source.

Collaborative Query Processing

When working with data sources that do not support SQL-92, you can query the sys_sqlinfo view to determine the query capabilities of the underlying APIs, expressed in SQL syntax. The provider offloads as much of the SELECT statement processing as possible to the server and then processes the rest of the query in memory.

See SupportEnhancedSQL for more information on how the provider circumvents API limitations with in-memory client-side processing. See SQL Compliance for SQL syntax details.

Discovering the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. The following result set indicates the SELECT functionality that the provider can offload to the data source or process client side. Your data source may support additional SQL syntax. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.

NameDescriptionPossible Values
COUNTWhether COUNT function is supported.YES, NO
IDENTIFIER_QUOTE_OPEN_CHARThe opening character used to escape an identifier.[
IDENTIFIER_QUOTE_CLOSE_CHARThe closing character used to escape an identifier.]
SUPPORTED_OPERATORSA list of supported SQL operators.=, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BYWhether GROUP BY is supported, and, if so, the degree of support.NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE
OJ_CAPABILITIESThe supported varieties of outer joins supported.NO, LEFT, RIGHT, FULL, INNER, NOT_ORDERED, ALL_COMPARISON_OPS
OUTER_JOINSWhether outer joins are supported.YES, NO
SUBQUERIESWhether subqueries are supported, and, if so, the degree of support.NO, COMPARISON, EXISTS, IN, CORRELATED_SUBQUERIES, QUANTIFIED
REPLICATION_SKIP_TABLESIndicates tables skipped during replication.
REPLICATION_TIMECHECK_COLUMNSA string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication.
IDENTIFIER_PATTERNString value indicating what string is valid for an identifier.
SUPPORT_TRANSACTIONIndicates if the provider supports transactions such as commit and rollback.YES, NO
DIALECTIndicates the SQL dialect to use.
KEY_PROPERTIESIndicates the properties which identify the uniform database.
SUPPORTS_MULTIPLE_SCHEMASIndicates if multiple schemas may exist for the provider.YES, NO
SUPPORTS_MULTIPLE_CATALOGSIndicates if multiple catalogs may exist for the provider.YES, NO
DATASYNCVERSIONThe CData Data Sync version needed to access this driver.Standard, Starter, Professional, Enterprise
DATASYNCCATEGORYThe CData Data Sync category of this driver.Source, Destination, Cloud Destination,
SUPPORTSENHANCEDSQLWhether enhanced SQL functionality beyond what is offered by the API is supported.true, false
SUPPORTS_BATCH_OPERATIONSWhether batch operations are supported.YES, NO
SQL_CAPAll supported SQL capabilities for this, insert, delete, update, transactions, orderby, oauth, assignedid, limit, like,
bulkinsert, count, bulkdelete, bulkupdate, groupby, having, aggs,

offset, replicate, countdistinct, joins,
drop, create, distinct, innerjoins, subqueries, alter, multipleschemas, groupbynorelation, outerjoins, unionall,
union, upsert, getdeleted, crossjoins, groupbycollate, multiplecats, fullouterjoin, merge, jsonextract, bulkupsert, sum,
subqueriesfull, min, max, joinsfull, xmlextract, avg, multistatements, foreignkeys, case, leftjoins, commajoins,
with, literals, rename, nestedtables, execute, batch, basic, index

PREFERRED_CACHE_OPTIONSA string value specifies the preferred cacheOptions.
ENABLE_EF_ADVANCED_QUERYIndicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side.YES, NO
PSEUDO_COLUMNSA string array indicating the available pseudo columns.
MERGE_ALWAYSIf the value is true, The Merge Mode is forcibly executed in Data Sync.true, false
REPLICATION_MIN_DATE_QUERYA select query to return the replicate start datetime.
REPLICATION_MIN_FUNCTIONAllows a provider to specify the formula name to use for executing a server side min.
REPLICATION_START_DATEAllows a provider to specify a replicate startdate.
REPLICATION_MAX_DATE_QUERYA select query to return the replicate end datetime.
REPLICATION_MAX_FUNCTIONAllows a provider to specify the formula name to use for executing a server side max.
IGNORE_INTERVALS_ON_INITIAL_REPLICATEA list of tables which will skip dividing the replicate into chunks on the initial replicate.
CHECKCACHE_USE_PARENTIDIndicates whether the CheckCache statement should be done against the parent key column.true, false
CREATE_SCHEMA_PROCEDURESIndicates stored procedures that can be used for generating schema files.

The following query retrieves the operators that can be used in the WHERE clause:

Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.


Name Type Description
NAME String A component of SQL syntax, or a capability that can be processed on the server.
VALUE String Detail on the supported SQL or SQL syntax.

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