This function applies a model where the input is one or more SQL queries and the output is a cursor. The cursor allows you to fetch as many results whenever they are needed.
The cursor apply is a function because it returns apply results as a cursor.
For general information about result set apply functions, see Result Set Apply Functions.
FUNCTION apply_function_name_SC(
case_table_sql IN VARCHAR2,
additional_sql_1 IN VARCHAR2,
additional_sql_n IN VARCHAR2,
model_name IN VARCHAR2 DEFAULT activity_default,
feature_table IN VARCHAR2 DEFAULT activity_default,
RETURN NUMBER;
The procedure has one kind of default:
activity_default
: The default is the value specified in the activity from which code was generated. For example, model_name
is by default the name specified in the activity.Table 1 Apply Function with Input SQL and Output a Cursor Function Parameters
Parameter | Description |
---|---|
apply_function_name_SC |
The name specified for the function when the code was generated with the _SC suffix added. |
case_table_sql |
A SQL query that returns valid case data for the model being applied. |
additional_sql_1 |
The first SQL query that returns valid additional data. This parameter is optional. |
additional_sql_n |
The n-th SQL query that returns valid additional data. This parameter is optional. |
model_name |
The name of the model being applied. |
feature_table |
Feature ID to text mapping table generated by the text transform. Can be used to translate model detail output containing columns affected by a text transformation. |
This procedure includes all necessary data preparation, data transformations, and apply settings. The procedure creates a result set.
You must provide at least one query that returns valid case table data for the model being applied.
You can supply a feature_table
name different from the default name, but you must supply the same name in
both Build and Apply procedures.
This example uses the tables and views used by the Oracle Data Mining sample programs. The cursor lets you get as many results as need whenever they are needed. apply_result
is the name of the apply result.
DECLARE
v_cursor NUMBER;
v_feedback INTEGER;
v_id apply_result.dmr$case_id%TYPE;
v_pred apply_result.prediction%TYPE;
v_prob apply_result.probability%TYPE;
v_cost apply_result.cost%TYPE;
BEGIN
v_cursor := MYAPPLY('SELECT * FROM SH.CUSTOMERS',
'SELECT * FROM SH.SUPPLEMENTARY_DEMOGRAPHICS',
'SELECT * FROM SH.SALES');
DBMS_SQL.DEFINE_COLUMN(v_cursor, 1, v_id);
DBMS_SQL.DEFINE_COLUMN(v_cursor, 2, v_pred);
DBMS_SQL.DEFINE_COLUMN(v_cursor, 3, v_prob);
DBMS_SQL.DEFINE_COLUMN(v_cursor, 4, v_cost);
v_feedback := DBMS_SQL.EXECUTE(v_cursor);
LOOP
-- Fetch a row
IF DBMS_SQL.FETCH_ROWS(v_cursor) > 0 THEN
-- Fetch columns from the row
DBMS_SQL.COLUMN_VALUE(v_cursor, 1, v_id);
DBMS_SQL.COLUMN_VALUE(v_cursor, 2, v_pred);
DBMS_SQL.COLUMN_VALUE(v_cursor, 3, v_prob);
DBMS_SQL.COLUMN_VALUE(v_cursor, 4, v_cost);
DBMS_OUTPUT.PUT_LINE(v_id|| ' | ' ||v_pred|| ' | ' ||v_prob|| ' | ' ||v_cost);
ELSE
EXIT;
END IF;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(v_cursor);
END
Copyright © 2006, 2008, Oracle. All rights reserved.