Oracle Database Insert

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.

As you begin typing a query, the auto-suggest dropdown menu displays relevant keywords, table names, and column names that you can select from. To choose a column name from the dropdown menu, first type the table name followed by a period (.), which shows the list of available column names for that table.

If you are unfamiliar with the tables present in the database schema, press the CTRL + space key in the query editor to obtain auto-suggestion of all the tables and keywords present in the database schema.

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.

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

• Values:https://github.com/TIBCOSoftware/tci-flogo/tree/master/samples/app-dev/array.forEach.sample
• Parameters: https://github.com/TIBCOSoftware/tci-flogo/tree/master/samples/app-dev/loops.sample

For more details about Data Mappings, see Mapping JSON Data with the json.path() Function.

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 being unresponsive.

Examples of INSERT query:

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

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

• INSERT INTO SYSTEM.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 theInsert ALL query, in the Input tab, the values node is not present.

• Call Procedure and Call Functions Syntax:
• With no parameters
  

  Call  Procedure_Name( );
  
  Select Function_Name( );
  

• With prepared parameters
  

  Call  Procedure_Name(?Param1 , ?Param2);
  
  Select Function_Name(?Param1, ?Param2);