Browse, search, and get Oracle Database metadata
TheOracle Database adapter surfaces metadata from the Oracle database that describes the message structure for communicating with the Oracle database using the adapter. The Oracle Database adapter supports two interfaces for retrieving metadata.
MetadataExchange provided by Windows Communication Foundation (WCF). WCF provides a metadata-exchange endpoint for all WCF bindings, which enables clients to get metadata from the Oracle database.
IMetadataRetrievalContract provided by the Microsoft Windows Communication Foundation (WCF) Line of Business (LOB) Adapter SDK, which supports the metadata browsing and searching capabilities of the adapter.
The Oracle Database adapter surfaces the Oracle database artifacts and respective operations that the adapter clients can invoke. The adapter also surfaces the SQLEXECUTE, POLLINGSTMT, and Notification operations that can be used to perform specific operations on the Oracle database. These operations are discussed later in this topic.
Adapter clients can browse, search, and retrieve metadata by using the WCF channel model, by using the WCF service model, or by creating a BizTalk project in Visual Studio. When using the WCF service model, you must use the Add Adapter Service Reference Visual Studio Plug-in to generate the proxy classes for performing operations on the Oracle database. When using a BizTalk project, you must use the Consume Adapter Service BizTalk Project Add-in or the Add Adapter Metadata Wizard to generate metadata for the operations that you want to perform on the Oracle database. For more information about browsing, searching, and retrieving metadata using Add Adapter Service Reference Plug-in, Consume Adapter Service Add-in or Add Adapter Metadata Wizard, see Get metadata for Oracle Database operations in Visual Studio.
Browsing Metadata
The Oracle Database adapter enables adapter clients to browse database tables, table views, stored procedures, functions, and packages that are available in the Oracle database. As part of the metadata browse operation, the adapter also surfaces the operations that can be performed on the Oracle database, including some custom operations supported by the adapters. These operations are available from the Add Adapter Service Reference Plug-in or the Consume Adapter Service Add-in. The Oracle Database adapter surfaces the following operations:
Outbound Operations
Contains a list of schemas in the underlying Oracle database. Expand a schema node to see the following artifacts:
Table: A list of all the tables in the schema. Select a table to view the Insert, Select, Update, and Delete operations.
Procedure: A list of stored procedures in the schema that are exposed as operations.
Function: A list of functions in the schema that are exposed as operations.
Package: A list of all the packages in the schema. Select a package to view the procedures and functions inside the package that are exposed as operations.
View: A list of all the views in the schema. Select a view to view the Insert, Select, Update, and Delete operations.
Apart from this, the Oracle Database adapter also exposes the SQLEXECUTE outbound operation, which enables the adapter clients to execute any generic data manipulation language (DML) or stored procedure in an Oracle database. The SQLEXECUTE operation is available when you select the root node (/). Note that the output of SQLEXECUTE is an array of data readers (output as array of generic records). As a result, any simple out parameters are not surfaced using the SQLEXECUTE operation. For more information about the operation, see SQLEXECUTE Operation in Oracle Database.
Inbound Operations
Contains a list of schemas in the underlying Oracle database. Expand a schema node to see the following artifacts:
Procedure: A list of stored procedures in the schema that are exposed as operations for polling.
Function: A list of functions in the schema that are exposed as operations for polling.
Package: A list of packages in the schema. Select a package to view the packaged procedures and functions that are exposed as operations for polling.
Apart from this, the Oracle Database adapter also exposes the POLLINGSTMT and Notification inbound operations. The POLLINGSTMT operation enables adapter clients to obtain inbound data from the Oracle database based on a query polling mechanism supported by the adapter. The Notification operation enables adapter clients to register a SELECT statement as the notification query on the database, and the database sends a notification to the adapter client as and when the result set of the SELECT statement changes. The POLLINGSTMT and Notification operations are available when you select the root node (/). For more information about the operations, see Support for Receiving Polling-based Data-changed Messages.md) and Considerations for Receiving Database Changer Notifications Using the Oracle Database adapter.
For more information about how the metadata is categorized, see Metadata Node IDs.
Searching Metadata
With the Oracle Database adapter, it is possible to perform a search query on the Oracle database by using the Oracle search expressions that are compatible with the LIKE operator. For example, adapter clients can use a search expression such as “EMP%” to obtain tables starting with EMP. The adapter converts this to the following SQL query:
SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME LIKE 'EMP%' AND OWNER = 'SCOTT'
Where, SCOTT is the schema with a collection of Oracle database artifacts.
The following table lists the special characters that can be used for search and their interpretation by the Oracle Database adapter.
| Special character | Interpretation |
|---|---|
| _ (underscore) | Matches exactly one character For example, A_ matches AB, AC, AD. |
| % (percentage) | Matches zero or more characters. For example, A% matches A, AB, ABC. |
| \ (escape) | Escapes the special meaning of % and _ For example, A\_B matches A_B. |
Important
The metadata search scope is restricted to the level immediately under the node at which the search operation is performed. For example, to search for a function, you must be searching under \[Schema]\Functions. Recursive search is not supported.
Retrieving Metadata
When retrieving metadata, the Oracle Database adapter can extract metadata under a schema, including all or a subset of database objects with the respective object and operation parameters. The adapter presents the entities from the Oracle database as element names in XML. Because underscores are the only permissible special characters that can be included, all other special characters in the element names are encoded using underscores. For example, emp$name is encoded as emp_x0024_name.
See Also
Overview of BizTalk Adapter for Oracle Database
Understand the BizTalk Adapter for Oracle Database
Get metadata for Oracle Database operations in Visual Studio
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for