Result Set Apply Function with Input SQL and Output a Cursor

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.

Syntax

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;

Parameters and Defaults

The procedure has one kind of default:

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.

Usage Notes

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.

Example

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