Oracle Database Query

Use this activity to execute a simple or a complex Query on Oracle Database. The Oracle Database Query activity returns information in the form of rows.

Settings

The Settings tab has the following fields:

Field Description
Oracle Database Connection Name of the Oracle Database connection from which to retrieve information. You can select the connection from the Oracle Database Connection list.

Input Settings

The Input Settings tab has the following fields:

Field Description
Query Statement An SQL statement used to query the database. The query can be a simple query or a complex query. A complex query has nested SQL statements. Prepared SQL queries can be constructed by using substitution variables (or substitution parameters) of the form ?<fieldname> in the query statement.

Example: select * from student where name like ?name;

Each substitution variable identifies an input parameter whose mapped value is substituted in the substitution variable at run time. The substitution variable can be reused for the same input parameter elsewhere in the query. Input parameters (used in the WHERE clause), and output parameters (used in the SELECT clause), and their corresponding type information is automatically fetched from the database using the selected connection for the entered query. Input and output fields on the Input and Output tabs of the activity are also automatically generated.

Ensure that you include the semicolon (;) at the end of the query. This activity expects a query to end with a semicolon to indicate the end of the query. A missing semicolon at the end of the query can make the query unresponsive.

  • Simple query example: 
    SELECT * FROM employee;

    For the above query, the output fields are generated from the table employee's column information. 

    SELECT description, url, name FROM webpages 
WHERE name = ? name and id > ?id ORDER BY name;

    For the above query, output fields are generated for description, url, and name. Also, input fields are generated for name (varchar) and ID (numeric). In addition, the mapped values for the fields name and ID are substituted in the substitution variables ?name and ?id at runtime.

  • Nested query example: 
    SELECT PRODUCT_ID, PRODUCT_NAME, 
LIST_PRICE FROM PRODUCTS
WHERE LIST_PRICE > (SELECT AVG( LIST_PRICE ) FROM PRODUCTS) 
ORDER BY PRODUCT_NAME;
Manually Configure Metadata When schema retrieval fails due to nonstandard SQL, use Manually Configure Metadata. For more information, see Using Manually Configure Metadata Option.
Table Schema Displays the metadata of the SQL query statement. You can modify the column metadata if Manually Configure Metadata is set to True.
Note: If you modify the metadata, ensure that it is in agreement with the query that you added.

Input

The Input tab displays the input schema of the activity as a tree structure. The schema varies according to the object that you selected on the Input Settings tab. You can provide the input values in the Flogo Connector for Oracle Database mapping input fields directly or use Mapper to map the input values to a field from the output of a preceding activity in the flow.

Output

The Output tab displays the output schema of the activity as a tree structure. The output is read-only. The information in the schema varies based on the fields selected on the Input Settings tab. The properties that are displayed in the schema correspond to the output of this activity and can be used as input by subsequent activities in the flow.