Oracle Database Query

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 SYSTEM.student where name like ?name; 

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 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 SYSTEM.employee;

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

  SELECT description, url, name FROM SYSTEM.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 SYSTEM.PRODUCTS
  WHERE LIST_PRICE > (SELECT AVG( LIST_PRICE ) FROM SYSTEM.PRODUCTS) 
  ORDER BY PRODUCT_NAME;

• 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);
  

Set the Manually Configure Metadata field to True to fetch table metadata manually. For more information, see Using Manually Configure Metadata Option.