Oracle Database Insert

Use this activity to execute an SQL INSERT statement on Oracle Database. The Oracle Database Insert activity returns information in the form of rows affected.

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
Insert Statement An SQL statement used to insert a record in the table. You can construct prepared SQL queries by using substitution variables (or parameters) of the form ?<fieldname> in the INSERT query statement.

Each substitution variable identifies an input parameter whose mapped value is substituted in the substitution variable at runtime. You can reuse the substitution variable for the same input parameter elsewhere in the query. The type information for the input parameters used in the VALUES clause is fetched from the database using the selected connection for the entered insert query. Similarly, input fields on the Input tab of the activity are also populated based on the SQL Insert statement.

Note: Be sure to include the semicolon (;) at the end of the query. This activity expects an insert query to end with a semicolon to indicate the end of the query. A missing semicolon at the end of the query results in the query hanging.
Note: When substitution variables used in the VALUES clause are identical to the column names in the table, those variables are displayed under the Values array in Input tab.

Map JSON data from previous activity for insert when you use:

For more details about Data Mappings, see "Mapping JSON Data with the json.path() Function" section in TIBCO Flogo Enterprise User's Guide.

You must include a semicolon (;) at the end of the query. This activity expects an insert query to end with a semicolon to indicate the end of the query. A missing semicolon results in the query hanging.

Examples of INSERT query: 

  • INSERT INTO employee (name, salary) VALUES ('test1',
?salary);

    For the above insert query, input field is generated for salary (NUMBER) and name (VARCHAR) under Values[] node as a part of values clause. 

  • INSERT INTO products (product_no, name, price) VALUES
(?product_no, ?name, ?price) ;

    For the above insert query, input fields are generated for product_no (NUMBER), name (VARCHAR), and price (NUMBER). The mapped value for the field product_no, name, price is substituted into the substitution variables ?product_no , ? name , ?price. The parameters node on the Input tab do not have mappings as there is no parameter in the insert query statement.

Insert All clause is also supported. For Insert ALL query, in the Input tab the values node is not present.

Manually Configure Metadata Set the Manually Configure Metadata field to True to fetch table metadata manually. For more information, see Using Manually Configure Metadata Option.
Note: For Flogo Enterprise 2.10.0 or earlier, schema retrieval might fail due to nonstandard SQL.
Table Schema Displays the metadata of the SQL insert 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 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 OracleDatabase 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 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.