Configuring Metadata of SQL statements manually

When schema retrieval fails due to nonstandard SQL, you must manually configure the metadata (by setting the Manually Configure Metadata option to true).

Sql statements that end with a semicolon(;) are auto parsed and the metadata is populated in the table field. However, the auto parsing does not support some sql statements (like ROWNUM, Insert ALL). For example, the following query results in a parsing error: 

SELECT * FROM employees WHERE ROWNUM < 10;

ROWNUM is Oracle Database specific clause and above query results in parsing error.

To manually configure the metadata of the SQL statement, perform the following steps:

  1. Set Manually Configure Metadata to true.
  2. To retrieve the metadata, enter a simplified query in the text box and click Fetch. 
SELECT * FROM employees;

Modifying Table Schema

When Manually Configure Metadata is set to true, the Table Schema is editable. You can modify the Table Schema by adding or deleting rows or changing the selections.

Note: When modifying the table schema ensure that the changes made are supported by valid SQL statement entered in the Textarea.
  1. Set Manually Configure Metadata to true.
  2. Make the modifications as required:
    • To modify table schema, add or delete rows as required and change the selections (by selecting or clearing check boxes).
      • The selected table fields are displayed on the Output tab of the activity.
      • The fields selected in the Parameter column are displayed on the Input tab of the activity.
    • To clear metadata information in the table, click Clear Table.
  3. Click Save.