Overview

The ClearProcess Oracle connector can be used to connect to any Oracle database, but it has been designed specifically to work with the Oracle E-Business Suite. (Oracle EBS) This guide demonstrates the various configuration options available in the ClearProcess Oracle Connector. There are three Operations available:

  • Select SELECT
  • Call Stored Procedure CALL_PROC
  • Commit COMMIT

Each of these is available through Clear Operations named SELECT, CALL_PROC, and COMMIT. These specific Operation names are important, as they are used by the connector to select the operation to use.

Select SELECT

Performs basic SELECT queries against an Oracle database. There are several limitations to this functionality:

  • Only one table can be selected from per call
  • No joins
  • Only WHERE and ORDER BY clauses supported

These limitations are by design. Queries are kept simple in order to avoid performance degradation on your Oracle database. More complex queries should be designed by your DBAs and implemented as stored procedures, which may then be called by the Oracle Connector.

Parameters

table

The Oracle database table to select from

columns (optional)

A list of columns to select from that table. The columns parameter must be a list. If the columns parameter is omitted, SELECT * will be used.

where (optional)

A list of dictionaries specifying three sub-parameters:

  • column: The column name to operate on
  • operator: Which comparison operator to use. Supported: =, <, >, <=, >=, <>, IN, LIKE
  • value: The value to compare against. For the IN operator, this value must be a list

Note that not all operators are supported. Comparisons are ANDed together. OR is not supported.

order_by (optional)

Order results by the specified column.

order_by_desc (optional)

Use descending order. Defaults to False, i.e. ascending.

Example

{
    "table": "ALL_TAB_COLS",
    "columns": ["COLUMN_NAME", "DATA_TYPE", "COLUMN_ID"],
    "where": [
      {
        "column": "TABLE_NAME", 
        "operator": "=", "value": 
        "{{ user_parameters.TABLE }}"
       }
    ],
    "order_by": "COLUMN_ID",
    "order_by_desc": True
}

Call Stored Procedure CALL_PROC

Performs calls to pre-defined stored procedures on the Oracle database.

Parameters

package

Stored procedures called by the ClearProcess Oracle Connector must be contained in a package. This parameter specifies the package name.

proc

The procedure name in Oracle.

params

A list of parameters to the stored procedure. Parameters that are PL/SQL Collections or Records may contain lists of sub-parameters. Each parameter is represented by a dictionary with the following keys:

  • name: The name of the parameter defined in Oracle
  • db_type: The Oracle type of the parameter (NUMBER, VARCHAR2, etc.) Records and collections should have the values PL/SQL RECORD and PL/SQL COLLECTION respectively.
  • value: The parameter value. For scalar parameters, this is the string, digit, boolean value, etc. For Records and Collections, these are lists of sub-parameters.
  • collection_of: Used only with Collection parameters. This parameter specifies what the collection holds. This may be a scalar type like VARCHAR2 or NUMBER, but frequently this is a Record type. In the case of a Record, specify the fully qualified type name, including package. e.g. PA_PROJECT_PUB.PROJECT_IN_REC_TYPE

Example

{
   "package": "PA_PROJECT_PUB",
   "proc": "CREATE_PROJECT",
   "params": [   
        {"name": "P_API_VERSION_NUMBER", "db_type": "NUMBER", "value": 1.0},
        {"name": "P_COMMIT", "db_type": "VARCHAR2(255)", "value": "F"},
        {"name": "P_INIT_MSG_LIST", "db_type": "VARCHAR2(255)", "value": "T"},
        {"name": "P_MSG_COUNT", "db_type": "NUMBER", "out": True},
        {"name": "P_MSG_DATA", "db_type": "VARCHAR2(255)", "out": True},
        {"name": "P_RETURN_STATUS", "db_type": "VARCHAR2(255)", "out": True},
        {"name": "P_WORKFLOW_STARTED", "db_type": "VARCHAR2(255)", "out": True},
        {
          "name": "P_PM_PRODUCT_CODE", 
          "db_type": "VARCHAR2(255)", 
          "value": "{{ user_parameters.HEADER.PROJECT_TYPE }}"
        },
        {
          "name": "P_OP_VALIDATE_FLAG", 
          "db_type": "VARCHAR2(255)", 
          "value": "F"},
        {
          "name": "P_PROJECT_IN", 
          "db_type": "PL/SQL RECORD", 
          "type_name": "PA_PROJECT_PUB.PROJECT_IN_REC_TYPE", 
          "value": [
            {
              "name": "PROJECT_NAME", 
              "db_type": "VARCHAR2(255)", 
              "value": "Project Name"
            },
            {
              "name": "LONG_NAME", 
              "db_type": "VARCHAR2(255)", 
              "value": "Project Long Name"
            }
        ]},
        {
          "name": "P_PROJECT_OUT", 
          "db_type": "PL/SQL RECORD", 
          "type_name": "PA_PROJECT_PUB.PROJECT_OUT_REC_TYPE", 
          "out": True
        },
        {
          "name": "P_TASKS_IN", 
          "db_type": "PL/SQL COLLECTION", 
          "type_name": "PA_PROJECT_PUB.TASK_IN_TBL_TYPE", 
          "collection_of": "PA_PROJECT_PUB.TASK_IN_REC_TYPE", "value": [
            {
              "db_type": "PL/SQL RECORD", 
              "type_name": "PA_PROJECT_PUB.TASK_IN_REC_TYPE", 
              "value": 
                [
                  {
                    "name": "PM_TASK_REFERENCE", 
                    "db_type": "VARCHAR2(255)", 
                    "value": "Reference"
                  }
                ]
             }
        ]},
        {
          "name": "P_TASKS_OUT", 
          "db_type": "PL/SQL COLLECTION", 
          "type_name": "PA_PROJECT_PUB.TASK_OUT_TBL_TYPE", 
          "out": True
        }
    ]
}

Call Function CALL_FUNC

Performs calls to pre-defined functions on the Oracle database. These calls are similar to stored procedures, but functions are treated slightly differently by Oracle in that they return a result, in addition to any OUT parameters.

Parameters

package

Functions called by the Clear Oracle Adapter must be contained in a package. This parameter specifies the package name.

func

The function name in Oracle.

params

A list of parameters to the function. Parameters that are PL/SQL Collections or Records may contain lists of sub-parameters. Each parameter is represented by a dictionary with the following keys:

  • name: The name of the parameter defined in Oracle
  • db_type: The Oracle type of the parameter (NUMBER, VARCHAR2, etc.) Records and collections should have the values PL/SQL RECORD and PL/SQL COLLECTION respectively.
  • value: The parameter value. For scalar parameters, this is the string, digit, boolean value, etc. For Records and Collections, these are lists of sub-parameters.
  • collection_of: Used only with Collection parameters. This parameter specifies what the collection holds. This may be a scalar type like VARCHAR2 or NUMBER, but frequently this is a Record type. In the case of a Record, specify the fully-qualified type name, including package. e.g. PA_PROJECT_PUB.PROJECT_IN_REC_TYPE

Commit COMMIT

Some stored procedures provided by Oracle EBS allow you to pass a commit parameter, but this is not recommended. In general, APIs defined in Clear should be transactional in nature. i.e. if one step of an API fails, no data should change as a result of the call. For Oracle, this cannot be enforced by the ClearProcess. We must rely on users to supply F flags to commit parameters. Therefore, at the end of your Clear API involving Oracle Stored Procedures, you should call the COMMIT Operation.

There are no parameters for this operation. Simply call the COMMIT Operation and your transaction will be committed.

Parameters

None

DSN Setup

Each System in clear has a DSN. This is not the same as an Oracle DSN. Instead, the Oracle DSN is included as a parameter to the Clear DSN. The parameters for the Clear DSN are as follows:

Parameters

user

The database user that Clear will use to connect to the Oracle EBS database. Note that tasks performed within Oracle EBS are NOT performed by this user, but by the individual user who logs in to Oracle EBS via Clear.

password

The password for the Oracle database user.

oracle_dsn

This is the Oracle DSN that specifies connection parameters for the Oracle database, in the standard format.

oracle_use_saml_auth

If your Clear implementation uses SSO, this should be set to true. With this property enabled, authentication is done by the identity provider (such as Active Directory or Okta) that interfaces with Clear. The Oracle connector relies on that authentication for authentication within Oracle. Authorization is still handled by the Oracle backend. This property tells the Oracle connector to use the Oracle EBS username provided by a SAML assertion, rather than relying on the username used for non-SSO login.

Example

{
    "user": "CLEAR_ADMIN", 
    "password": "Lqb6uIlhZ4", 
    "oracle_dsn": "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.12.41)(PORT=10810))(CONNECT_DATA=(SID=PROD)))", 
    "oracle_use_saml_auth": true
}