Defining Programatically Generated Lists of Values and Value Validation

You can define Source Code within a Program to generate a list of values for a Parameter, or to validate a user-entered value. You can use the same Source Code for both purposes.

When you search for Source Code to use for a Parameter's programmatic LOV or value validation, the system does not impose the normal security requirements; you can select any sharable Source Code from any Program, whether or not you have standard security access to the Program.

The source code you write must be in PL/SQL and must reference CDR_VALS_COLL, a predefined Oracle database object of type Collection whose structure is effectively the same as a table's, with rows and columns. CDR_VALS_COLL has two columns, one for position (row number in the table) and one for value (the value to derive for the list of values or validation). You must write source code that creates as many positions as necessary and populates each with an appropriate value.

Note:

If the source code generates only one value, the system assigns that value to the Parameter automatically.

CDR_VALS_COLL is effectively empty except when a Program populates it within the context of a particular user's session. Each user session "sees" its own collection only.

See example source code below.

To create a programmatically generated list of values for a Parameter, or to programmatically validate a Parameter value, do the following:

1. Create an Oracle LSH Program of type PL/SQL. See Creating a Program.

2. Add a Source Code definition to the PL/SQL Program. See Defining Source Code and Defining PL/SQL Programs.

3. In the Oracle Package field, enter the package name.

4. In the Oracle Procedure field, enter the function name. You are writing a function, not a procedure, but enter the function name in this field.

5. Add a source Table Descriptor for each Table instance your code will read from, if any. Map each Table Descriptor to the appropriate Table instance. See Defining Table Descriptors and Mapping Table Descriptors to Table Instances.

6. Enter your PL/SQL source code in the Source Code field.

   As with other PL/SQL source code in Oracle LSH, you must include the package spec as well as the package body in your source code.

   Be sure to use the same names for the package and function that you entered in the Oracle Package and Oracle Procedure fields.

   The syntax in Example 6-1 is required verbatim up to the beginning of the function, except for the package and function names. See the examples for further information.

7. Apply your changes, check in the Program definition, and install the Program instance in its Work Area.

8. In the Parameter definition, specify the name of the Program and Source Code that contain the code that will generate the LOV or validate the Parameter value. See Defining Allowed Values and Setting Validation Rules.

Example 6-1 Simple Example

In this example, the source code creates two rows in the collection CDR_VALS_COLL. Position 1 has the value Yes and position 2 has the value No. This example shows the essential elements required in as simple a way as possible. However, you would not want to use this particular source code because you could accomplish the same thing much more simply by defining a static list of values with two values: Yes and No.

The package spec is the first five lines of code, up to the slash (/). The package name is LOV and the function name is returnValues. Except for these names, for which you can substitute any names you choose, all the code above the beginning of the actual function (at begin) is required as is.

For each row in the collection the source code does the following in succession:

• Creates the row itself by using an EXTEND command

• Specifies the values for that row of the POSITION column

• Specifies the values for that row of the VALUE column

• Specifies that the function returns the value for position n

Example source code:

CREATE OR REPLACE package LOV as
FUNCTION returnValues (pi_tParam IN cdr_parameter_obj_type ,pi_cParamColl    IN cdr_parameter_coll) RETURN CDR_VALS_COLL;
end LOV;
/
CREATE OR REPLACE package body LOV as
FUNCTION returnValues (pi_tParam IN cdr_parameter_obj_type ,pi_cParamColl    IN cdr_parameter_coll) RETURN CDR_VALS_COLL IS                                                                          
     valuesRecord     CDR_VAL_OBJ_TYPE  := new CDR_VAL_OBJ_TYPE(NULL,NULL);
     LOVCollection    CDR_VALS_COLL     := CDR_VALS_COLL();
 begin                                                                                                                        
        LOVCollection.EXTEND;
        valuesRecord.POSITION := 1;
        valuesRecord.VALUE    := 'Yes';
        LOVCollection(1) := valuesRecord;
     
        LOVCollection.EXTEND;
        valuesRecord.POSITION := 2;
        valuesRecord.VALUE    := 'No';
        LOVCollection(2) := valuesRecord;
                                                                                                                        
     RETURN (LOVCollection);                                                                                                                    
end returnValues;
end LOV;
/

Example 6-2 Pulling Column Values from a Table Instance

In the real world you would use a programmatically generated LOV to pull the current values of a column in an Oracle LSH Table instance that meet a certain set of criteria. In this case, you add a source Table Descriptor to the Program and map it to the Table instance whose Column values you want to read.

In your source code you write a SELECT statement and WHERE clause to describe the criteria for the values you want to retrieve. You then use the EXTEND command to create n rows in the collection, where n is the number of values retrieved, and populate each row with a POSITION value and a VALUE value.

Example 6-3 Pulling Values from an External System

You can pull values from a table in an external system if you include the necessary remote location connection information so that the system can read data in the external system.