CREATE_GROUP_FROM_QUERY Built-in
Description
Creates a record group with the given name. The record group has columns representing each column you include in the select list of the query. Add rows to the record group with the POPULATE_GROUP Built-in.
Note: If you do not pass a formal column name or alias for a column in the SELECT statement, Oracle Forms creates ICRGGQ with a dummy counter <NUM>. This happens whenever the column name would have been invalid. The first dummy name-counter always takes the number one. For example, the query SELECT 1 + 1 FROM DUAL would result in a column named ICRGGQ_1.
Syntax
FUNCTION CREATE_GROUP_FROM_QUERY
(recordgroup_name VARCHAR2,
query VARCHAR2,
scope NUMBER,
array_fetch_size NUMBER);
Built-in Type unrestricted function
Returns RecordGroup
Enter Query Mode yes
Parameters
- recordgroup_name
-
- The name of the record group. When Oracle Forms creates the record group
object it also assigns the object a unique ID of type RecordGroup.
-
- query
-
- A valid SQL SELECT statement, enclosed in single quotes. Any columns retrieved
as a result of the query take the data types of the columns in the table.
If you restrict the query to a subset of the columns in the table, then Oracle Forms creates only those columns in the record group
-
- scope
-
- Specifies whether tlhe record group can be used only within the
current form or within every form in a multi-form application. Takes the following
constants as arguments:
FORM_SCOPE
Indicates that the record group can by used only within the current form.
This is the default value.
-
- GLOBAL_SCOPE
Indicates that the record group is global, and that it can be used within
all forms in the application. Once created, a global record group persists
for the remainder of the runtime session.
-
- array_fetch_size
-
- Specifies the array fetch size. The default array size is 0.
CREATE_GROUP_FROM_QUERY Restrictions
- If a global record group is created from (or populated with) a query while executing form A, and the query string contains bind variable references which are local to A (:block.item or :PARAMETER.param), when form A terminates execution, the global query record group is converted to a global non-query record group (it retains the data, but a subsequent call to POPULATE_GROUP is considered an error).
CREATE_GROUP_FROM_QUERY Examples
/*
** Built-in: CREATE_GROUP_FROM_QUERY
** Example: Create a record group from a query, and populate it.
*/
DECLARE
rg_name VARCHAR2(40) := 'Salary_Range';
rg_id RecordGroup;
errcode NUMBER;
BEGIN
/*
** Make sure group doesn't already exist
*/
rg_id := Find_Group( rg_name );
/*
** If it does not exist, create it and add the two
** necessary columns to it.
*/
IF Id_Null(rg_id) THEN
rg_id := Create_Group_From_Query( rg_name,
'SELECT SAL-MOD(SAL,1000) BASE_SAL_RANGE,'
||'COUNT(EMPNO) EMPS_IN_RANGE '
||'FROM EMP '
||'GROUP BY SAL-MOD(SAL,1000) '
||'ORDER BY 1');
END IF;
/*
** Populate the record group
*/
errcode := Populate_Group( rg_id );
END;