Validation Type 'Table' in Value sets in Oracle

Definition:- A table-validated value set provides a predefined list of values like an independent set, but its values are stored in an application table

You define which table you want to use, along with a WHERE cause to limit the values you want to use for your set.

Lets create a table validated value set.


Table Validation type value set in Oracle

Press "Edit Information" button to add details about table/view/query used for table validation.

Validation Type 'Table' in value sets in Oracle


Table Application:- Mention the relevant application to which table belongs.
Table Name:- You may mention the table name, view or query in this field.

To enter query, enter it in this form i.e. (select employee_number en, full_name fn from per_all_people_f). In my point of view, if data is not fully accessible through table, use a view rather than query.

Because once you entered the query and saved the value set, and applied it to many fields, you will not be able to edit the value sets' query. You will have to remove value set from all the fields.

Value:- Table column values which are shown for selection on the field.
Meaning:- Displays the value outside field, once any selection is made on the field.
ID:- The value which is returned to be saved in the table, or is returned in the report.

Where/Order by:- Name defines itself. To put where clause or order by clause. But you need to follow the order.

Additional Columns:- Any column which is to be shown parallel to one we select in Value field.

NOTE:
if you want to show multiple columns in parallel, keep the meaning field empty and place column names separated by comma in Additional Columns field and also specify their width.

If you specify only the SQL fragment but no alias or width, your column does not show up.

Specify Additional Columns in this format i.e.


column_name_1 "Column 1 Title"(width), column_name_2 "Column 2 Title"(width), ... 


Test and save the value set.

After i applied the value set to a field, result was like shown below.

Table Validation in value sets in Oracle

Also see: Dependent Value sets in Oracle

Value Sets in Oracle Apps

Leave a comment for any query.



6 comments:

  1. goood work...thanx for sharing..

    ReplyDelete
  2. what is the behind query you have written to populate list of values(LOV) in form ,i also created like above but not able to populate list of values.

    please provide me query .

    very urgent.

    Thanks in adnvane

    ReplyDelete