DBMS_DATA_MINING

https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html
Admin
DBMS_DATA_MINING is an Oracle PL/SQL package API for creating, evaluating, and querying machine...
Tokens:101,434
Snippets:689
Trust Score:9.7
Update:9 months ago
Show doc for...
Tokens:
Raw
### Oracle SQL: Build Association Model and Get Frequent Itemsets

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

This example demonstrates building an association model using CREATE_MODEL and then retrieving frequent itemsets with GET_FREQUENT_ITEMSETS. It includes preparing a settings table, updating confidence, building the model, and querying the itemsets.

```sql
-- prepare a settings table to override default settings
CREATE TABLE market_settings AS
    SELECT * from TABLE(DBMS_DATA_MINING.GET_DEFAULT_SETTINGS)
 WHERE setting_name LIKE 'ASSO_%';
BEGIN
-- update the value of the minimum confidence
UPDATE market_settings
   SET setting_value = TO_CHAR(0.081)
 WHERE setting_name = DBMS_DATA_MINING.asso_min_confidence;

/* build a AR model */
DBMS_DATA_MINING.CREATE_MODEL(
  model_name           => 'market_model',
  function             => DBMS_DATA_MINING.ASSOCIATION,
  data_table_name      => 'market_build',
  case_id_column_name  => 'item_id',
  target_column_name   => NULL,
  settings_table_name  => 'market_settings');
END;
/ -- View the (unformatted) Itemsets from SQL*Plus
SELECT itemset_id, items, support, number_of_items
  FROM TABLE(DBMS_DATA_MINING.GET_FREQUENT_ITEMSETS('market_model'));
```

--------------------------------

### Example Table Creation for Apply Results (SQL)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

An example SQL statement to create a table that can serve as the 'apply_result_table_name' for the COMPUTE_LIFT procedure. This table should contain the case ID and the prediction scores.

```SQL
CREATE TABLE apply_result_table_name AS (
            case_id_column_name            VARCHAR2, 
            score_column_name              VARCHAR2,
```

--------------------------------

### Example: Creating Data Mining Models with Transformations

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

This example demonstrates creating two data mining models. The first model ('mod1') is trained with automatic data preparation (ADP). The second model ('mod2') is trained using a transformation list obtained from 'mod1', ensuring both models have the same embedded transformation expressions.

```PL/SQL
CREATE OR REPLACE VIEW mining_data AS
     SELECT cust_id, cust_year_of_birth, cust_income_level, cust_credit_limit
     FROM sh.customers;
 
describe mining_data
 Name                                      Null?    Type
 -----------------------------------------
 CUST_ID                                   NOT NULL NUMBER
 CUST_YEAR_OF_BIRTH                        NOT NULL NUMBER(4)
 CUST_INCOME_LEVEL                                  VARCHAR2(30)
 CUST_CREDIT_LIMIT                                  NUMBER

CREATE TABLE setmod1(setting_name  VARCHAR2(30),setting_value VARCHAR2(30));
BEGIN
   INSERT INTO setmod1 VALUES (dbms_data_mining.algo_name, dbms_data_mining.algo_naive_bayes);
   INSERT INTO setmod1 VALUES (dbms_data_mining.prep_auto,dbms_data_mining.prep_auto_on);
   dbms_data_mining.CREATE_MODEL (
               model_name            => 'mod1',
               mining_function       => dbms_data_mining.classification,
               data_table_name       => 'mining_data',
               case_id_column_name   => 'cust_id',
               target_column_name    => 'cust_income_level',
               settings_table_name   => 'setmod1');
    COMMIT;
END;
/
CREATE TABLE setmod2(setting_name  VARCHAR2(30),setting_value VARCHAR2(30));
BEGIN
  INSERT INTO setmod2
      VALUES (dbms_data_mining.algo_name, dbms_data_mining.algo_naive_bayes);
  COMMIT;
END;
/
DECLARE
  v_xform_list       dbms_data_mining_transform.TRANSFORM_LIST;
  dmxf               DM_TRANSFORMS;
BEGIN
   EXECUTE IMMEDIATE
    'SELECT dm_transform(attribute_name, attribute_subname,expression, reverse_expression)
     FROM TABLE(dbms_data_mining.GET_MODEL_TRANSFORMATIONS (''mod1''))'
     BULK COLLECT INTO dmxf;
   dbms_data_mining.GET_TRANSFORM_LIST (
        xform_list             =>  v_xform_list,
        model_xforms           =>  dmxf);
   dbms_data_mining.CREATE_MODEL(
         model_name            => 'mod2',
         mining_function       =>  dbms_data_mining.classification,
         data_table_name       => 'mining_data',
         case_id_column_name   => 'cust_id',
         target_column_name    => 'cust_income_level',
         settings_table_name   => 'setmod2',
         xform_list            =>  v_xform_list);
END;
/

```

--------------------------------

### Retrieve Cluster Model Details (SQL Example)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

This SQL example demonstrates how to use the GET_MODEL_DETAILS_OC function to retrieve model details for a clustering model named 'OC_SH_Clus_sample'. It selects cluster ID, attribute name, operator, and string value for the split predicate, limiting the output to the top 10 rows.

```sql
SELECT clu_id, attribute_name, op, s_value
     FROM (
         SELECT a.id clu_id, sp.attribute_name, sp.conditional_operator op,
                  sp.attribute_str_value s_value
             FROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_OC(
                    'OC_SH_Clus_sample')) a,
                  TABLE(a.split_predicate) sp
           ORDER BY a.id, op, s_value
     )
     WHERE ROWNUM < 11;
```

--------------------------------

### RANK_APPLY Example Usage in PL/SQL

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

This PL/SQL code demonstrates how to use the RANK_APPLY procedure. It includes comments guiding through the process of building a model, pre-processing data, applying the model, and then calling RANK_APPLY with relevant parameters such as the apply result table, case ID column, prediction column, and probability column.

```plsql
BEGIN
/* build a model with name census_model.
 * (See example under CREATE_MODEL)
 */ 

/* if training data was pre-processed in any manner,
 * perform the same pre-processing steps on apply
 * data also.
 * (See examples in the section on DBMS_DATA_MINING_TRANSFORM)
 */

/* apply the model to data to be scored */
DBMS_DATA_MINING.RANK_APPLY(
  apply_result_table_name       => 'census_apply_result',
  case_id_column_name           => 'person_id',
  score_column_name             => 'prediction',
  score_criterion_column_name   => 'probability
```

--------------------------------

### Example of Ranked Apply Result Configuration (PL/SQL)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

This code snippet demonstrates the configuration for ranked apply results, specifying the output table name, the top N results to retrieve, and the cost matrix table name.

```sql
ranked_apply_result_tab_name  => 'census_ranked_apply_result',
  top_N                         => 3,
  cost_matrix_table_name        => 'census_cost_matrix');
END;
/
```

--------------------------------

### Retrieve Model Transformations Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

This SQL example demonstrates how to retrieve transformation expressions embedded in a model named 'new_model'. It queries the result of the GET_MODEL_TRANSFORMATIONS function, converting the expression and reverse_expression CLOB fields to CHAR for display.

```SQL
SELECT attribute_name, TO_CHAR(expression), TO_CHAR(reverse_expression)
      FROM TABLE (dbms_data_mining.GET_MODEL_TRANSFORMATIONS('new_model'));
```

--------------------------------

### Setup Settings for Naive Bayes Model

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

This PL/SQL block inserts settings into the 'settings_nb' table to configure the naive Bayes algorithm and disable automatic data preparation for model creation.

```PL/SQL
BEGIN
     INSERT INTO settings_nb (setting_name, setting_value) VALUES
           (dbms_data_mining.algo_name, dbms_data_mining.algo_naive_bayes);
     INSERT INTO settings_nb (setting_name, setting_value) VALUES
           (dbms_data_mining.prep_auto, dbms_data_mining.prep_auto_off);
     COMMIT;
END;
/
```

--------------------------------

### Retrieve SVM Model Details - PL/SQL Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

This PL/SQL example demonstrates how to use the GET_MODEL_DETAILS_SVM function to retrieve and display details for an SVM classification model. It uses Common Table Expressions (CTEs) to process the pipelined output and selects the top 10 attributes by the absolute value of their coefficients.

```sql
WITH
  mod_dtls AS (
  SELECT * 
    FROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_SVM('SVMC_SH_Clas_sample'))
  ),
  model_details AS (
  SELECT D.class, A.attribute_name, A.attribute_value, A.coefficient
    FROM mod_dtls D,
  	    TABLE(D.attribute_set) A
    ORDER BY D.class, ABS(A.coefficient) DESC
  )
  SELECT class, attribute_name aname, attribute_value aval, coefficient coeff
    FROM model_details
    WHERE ROWNUM < 11;
```

--------------------------------

### Compute Lift Using COMPUTE_LIFT_PART Procedure

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

Executes the COMPUTE_LIFT_PART procedure to calculate lift statistics. This example uses probabilities as the scoring criterion and specifies input and output table names, case ID, target column, and number of quantiles.

```plsql
BEGIN
	  DBMS_DATA_MINING.COMPUTE_LIFT_PART (
             apply_result_table_name     => 'nb_apply_results',
             target_table_name           => 'mining_data_test_v',
             case_id_column_name         => 'cust_id',
             target_column_name          => 'affinity_card',
             lift_table_name             => 'nb_lift',
             positive_target_value       =>  to_char(1),
             score_column_name           => 'PREDICTION',
             score_criterion_column_name => 'PROBABILITY',
             score_partition_column_name => 'PARTITITON_NAME',
             num_quantiles               =>  10,
             cost_matrix_table_name      =>  null,
             apply_result_schema_name    =>  null,
             target_schema_name          =>  null,
             cost_matrix_schema_name     =>  null,
             score_criterion_type        =>  'PROBABILITY');
END;
/

```

--------------------------------

### Get Model Signature (Oracle PL/SQL)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

The GET_MODEL_SIGNATURE function returns a list of columns from the input table that were utilized during the model training process. It outputs the attribute name and its data type. This function is deprecated starting from Oracle Database 12c Release 2.

```PL/SQL
FUNCTION get_model_signature (model_name IN VARCHAR2)
RETURN DM_Model_Signature PIPELINED;
```

--------------------------------

### Get Model Settings (Oracle PL/SQL)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

The GET_MODEL_SETTINGS function retrieves the configuration settings used to build a specified model. It returns a set of rows detailing each setting's name and value. This function is deprecated starting from Oracle Database 12c Release 2.

```PL/SQL
FUNCTION get_model_settings(model_name IN VARCHAR2)
  RETURN DM_Model_Settings PIPELINED;
```

--------------------------------

### Apply Model and Store Predictions (SQL)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

Creates a table 'nb_apply_results' by applying a pre-existing model ('nb_sh_clas_sample') to test data ('mining_data_test_v'). It stores customer ID, prediction, and probability for each record. This step is necessary before computing lift based on probabilities.

```sql
CREATE TABLE nb_apply_results AS
    SELECT cust_id, t.prediction, t.probability
    FROM mining_data_test_v, TABLE(PREDICTION_SET(nb_sh_clas_sample USING *)) t;
```

--------------------------------

### Get EM Model Details Function Signature

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

The signature for the GET_MODEL_DETAILS_EM_COMP table function, which retrieves details about expectation maximization model parameters. This function is deprecated starting from Oracle Database 12c Release 2. It takes the model name and an optional partition name as input and returns a set of rows representing model components.

```SQL
DBMS_DATA_MINING.get_model_details_em_comp(
      model_name IN VARCHAR2,
      partition_name IN VARCHAR2 DEFAULT NULL)
  RETURN DM_EM_COMPONENT_SET PIPELINED;
```

--------------------------------

### Get Decision Tree Model Details XML in SQL

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

This SQL code snippet demonstrates how to retrieve the XML details of a decision tree model using the DBMS_DATA_MINING.get_model_details_xml function. It is important to note that this function is deprecated starting from Oracle Database 12c Release 2. The output is formatted for SQL*Plus and may require further processing for proper display.

```sql
column dt_details format a320
SELECT 
 dbms_data_mining.get_model_details_xml('dt_sh_clas_sample') 
 AS DT_DETAILS
FROM dual;
```

--------------------------------

### Build Association Model and Retrieve Rules (SQL)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

This snippet demonstrates how to prepare settings, build an association model using DBMS_DATA_MINING.CREATE_MODEL, and then retrieve all association rules using the GET_ASSOCIATION_RULES table function. It showcases overriding default settings for minimum confidence.

```SQL
CREATE TABLE market_settings AS
SELECT *
  FROM TABLE(DBMS_DATA_MINING.GET_DEFAULT_SETTINGS)
 WHERE setting_name LIKE 'ASSO_%';
BEGIN
-- update the value of the minimum confidence
UPDATE market_settings
   SET setting_value = TO_CHAR(0.081)
 WHERE setting_name = DBMS_DATA_MINING.asso_min_confidence;

-- build an AR model 
DBMS_DATA_MINING.CREATE_MODEL(
  model_name => 'market_model',
  function => DBMS_DATA_MINING.ASSOCIATION,
  data_table_name => 'market_build',
  case_id_column_name => 'item_id',
  target_column_name => NULL,
  settings_table_name => 'market_settings');
END;
/  
-- View the (unformatted) rules 
SELECT rule_id, antecedent, consequent, rule_support,
       rule_confidence
  FROM TABLE(DBMS_DATA_MINING.GET_ASSOCIATION_RULES('market_model'));
```

--------------------------------

### Set Partitioning for Data Mining Models (SQL)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

These SQL statements demonstrate how to configure partitioning for data mining models. The first statement sets the columns to be used for partitioning, and the second sets the maximum number of partitions allowed for a model. These are essential for building large or partitioned datasets.

```SQL
INSERT INTO settings_table VALUES (‘ODMS_PARTITION_COLUMNS’, ‘GENDER, AGE’);
INSERT INTO settings_table VALUES ('ODMS_MAX_PARTITIONS’, '10’);
```

--------------------------------

### Get k-means model details using DBMS_DATA_MINING.GET_MODEL_DETAILS_KM (PL/SQL)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

This PL/SQL code demonstrates how to use the GET_MODEL_DETAILS_KM function to retrieve details for a k-means clustering model named 'KM_SH_Clus_sample'. The function returns a set of rows containing information about clusters, including their IDs, record counts, parent clusters, tree levels, and dispersions. The example limits the output to the first 5 rows.

```PL/SQL
SELECT T.id           clu_id,
       T.record_count rec_cnt,
       T.parent       parent,
       T.tree_level   tree_level,
       T.dispersion   dispersion
  FROM (SELECT *
          FROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_KM(
                     'KM_SH_Clus_sample'))
        ORDER BY id) T
 WHERE ROWNUM < 6;
```

--------------------------------

### Oracle PL/SQL: Dropping and Creating a Model with Settings

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

This PL/SQL code snippet demonstrates how to drop an existing model named 'AR_SH_SAMPLE' and then prepares to create a new model using DBMS_DATA_MINING.CREATE_MODEL2. It initializes a setting list for model creation parameters.

```PL/SQL
BEGIN DBMS_DATA_MINING.DROP_MODEL('AR_SH_SAMPLE');
EXCEPTION WHEN OTHERS THEN NULL; END;
/
DECLARE
    v_setlst DBMS_DATA_MINING.SETTING_LIST;
BEGIN
```

--------------------------------

### Apply Regression Model using SQL

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

This SQL example demonstrates how to apply a GLM Regression model to a data view and store the results in a table. It also shows how to query the results table to retrieve predictions for specific cases. Ensure the model name, data table name, case ID column, and result table name are correctly specified.

```sql
SQL> BEGIN
       DBMS_DATA_MINING.APPLY (
       model_name     => 'glmr_sh_regr_sample',
       data_table_name     => 'mining_data_apply_v',
       case_id_column_name => 'cust_id',
       result_table_name   => 'regression_apply_result');
    END;
    /
 
SQL> SELECT * FROM regression_apply_result WHERE cust_id >  101485;
 
   CUST_ID PREDICTION
---------- ----------
    101486 22.8048824
    101487 25.0261101
    101488 48.6146619
    101489   51.82595
    101490 22.6220714
    101491 61.3856816
    101492 24.1400748
    101493  58.034631
    101494 45.7253149
    101495 26.9763318
    101496 48.1433425
    101497 32.0573434
    101498 49.8965531
    101499  56.270656
    101500 21.1153047
```

--------------------------------

### Import Model to Same Schema (SQL)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

This SQL snippet demonstrates exporting a model to a dump file and then importing it back into the same schema using `DBMS_DATA_MINING.EXPORT_MODEL` and `DBMS_DATA_MINING.IMPORT_MODEL`. It assumes the model 'NMF_SH_SAMPLE' exists and uses 'DATA_PUMP_DIR' as the directory object.

```sql
SQL> connect oml_user2
Enter password: oml_user2_password
Connected.
SQL> select model_name from user_mining_models;
 
MODEL_NAME
------------------------------
NMF_SH_SAMPLE
SVMO_SH_CLAS_SAMPLE
SVMR_SH_REGR_SAMPLE

-- export the model called NMF_SH_SAMPLE to a dump file in same schema
SQL>EXECUTE DBMS_DATA_MINING.EXPORT_MODEL (
            filename =>'NMF_SH_SAMPLE_out',
            directory =>'DATA_PUMP_DIR',
            model_filter => 'name = \'NMF_SH_SAMPLE\'');

-- import the model back into the same schema
SQL>EXECUTE DBMS_DATA_MINING.IMPORT_MODEL (
            filename => 'NMF_SH_SAMPLE_out01.dmp',
            directory => 'DATA_PUMP_DIR',
            model_filter => 'name = \'NMF_SH_SAMPLE\'');
```

--------------------------------

### GET_MODEL_DETAILS_NMF Function Syntax and Example (PL/SQL)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

This snippet shows the PL/SQL syntax for the GET_MODEL_DETAILS_NMF function and an example query to retrieve model details. The function is deprecated since Oracle Database 12c Release 2. It requires the model name and optionally a partition name, returning feature details.

```PL/SQL
DBMS_DATA_MINING.get_model_details_nmf(
      model_name IN VARCHAR2,
      partition_name VARCHAR2 DEFAULT NULL)
   RETURN DM_NMF_Feature_Set PIPELINED;

SELECT * FROM (
SELECT F.feature_id,
       A.attribute_name,
       A.attribute_value,
       A.coefficient
  FROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_NMF('NMF_SH_Sample')) F,
       TABLE(F.attribute_set) A
ORDER BY feature_id,attribute_name,attribute_value
) WHERE ROWNUM < 11;
```

--------------------------------

### Create Association Model with Aggregation in PL/SQL

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

Demonstrates how to create an association rules model using DBMS_DATA_MINING in PL/SQL. This snippet includes setting parameters for algorithm name, data preparation, support, confidence, rule length, item ID column, and crucially, specifies columns for aggregation using ASSO_AGGREGATES. It also shows the exception handling for dropping an existing model and the data query setup.

```plsql
BEGIN DBMS_DATA_MINING.DROP_MODEL('AR_SH_SAMPLE');
EXCEPTION WHEN OTHERS THEN NULL; END;
/
DECLARE
    v_setlst DBMS_DATA_MINING.SETTING_LIST;
BEGIN
    v_setlst('ALGO_NAME')               := 'ALGO_APRIORI_ASSOCIATION_RULES';
    v_setlst('PREP_AUTO')               := 'ON';
    v_setlst('ASSO_MIN_SUPPORT')        := '0.04';
    v_setlst('ASSO_MIN_CONFIDENCE')     := '0.1';
    v_setlst('ASSO_MAX_RULE_LENGTH')    := '2';
    v_setlst('ODMS_ITEM_ID_COLUMN_NAME'):= 'PROD_NAME';
    v_setlst('ASSO_AGGREGATES')         := 'AMOUNT_SOLD';


    DBMS_DATA_MINING.CREATE_MODEL2(
        MODEL_NAME        => 'AR_SH_SAMPLE',
        MINING_FUNCTION   => 'ASSOCIATION',
        DATA_QUERY        => 'select * from SALES_TRANS_CUST',
        SET_LIST          => v_setlst,
        CASE_ID_COLUMN_NAME => 'CUST_ID');
END;
```

--------------------------------

### Register Algorithm with Metadata (SQL)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

Registers a new algorithm using the REGISTER_ALGORITHM procedure. It requires the algorithm name, metadata in JSON format, and an optional description. The metadata specifies the algorithm's language, mining function, and associated build and score functions.

```sql
BEGIN
  DBMS_DATA_MINING.register_algorithm(
    algorithm_name                 =>   't1',
    algorithm_metadata            =>
    '{"function_language" : "R",
      "mining_function" : 
        { "mining_function_name" : "CLASSIFICATION",
           "build_function" : {"function_body": "function(dat, formula, family) { set.seed(1234);
                                          mod <- glm(formula = formula, data=dat,  
                                                              family=eval(parse(text=family))); mod}"},
           
           "score_function" :  {"function_body": "function(mod, dat) {
                                             res <- predict(mod, newdata = dat, type=''response'');
                                             res2=data.frame(1-res, res); res2}"}}
    }',
    algorithm_description  => 't1');
END;
/
```

--------------------------------

### JSON Object Example for Registration Procedure

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

This JSON example demonstrates an object that needs to be passed to the registration procedure, likely for registering a new algorithm or function within the Oracle DBMS Data Mining framework. It includes details such as 'algo_name_display', 'function_language', 'mining_function', and 'algo_setting'. The 'mining_function' includes the 'build_function' and 'score_function' with R code embedded within.

```json
{
  "algo_name_display"   :     {"English", "t1"},
                         "function_language"    :      "R",
                         "mining_function" : {
  "mining_function_name" : "CLASSIFICATION",
                         "build_function" : {"function_body": "function(dat, formula, family) {                                                                                                    set.seed(1234);
                                          mod <- glm(formula = formula, data=dat,
                                                      family= eval(parse(text=family))); mod}"},                                        
           "score_function" :  { "function_body": "function(mod, dat) {
                                             res <- predict(mod, newdata = dat, type=''response                                                                                                                              '');
                                             res2=data.frame(1-res, res); res2}"}}
                          },
                          "algo_setting" :   [{"name"                : "dbms_data_mining.odms_m                                                                                                                                             issing_value_treatment",
                            "name_display"   : {"English", "dbms_data_mining.odms_missing_value                                                   _treatment"},
                            "type"                  : "string",
                            "optional"            :  "TRUE",
                            "value"                : "dbms_data_mining.odms_missing_value_mean_mode",
                            "categorical choices"   : [    "dbms_data_mining.odms_missing_value_mean_mode",
                                                                         "dbms_data_mining.odms_missing_value_auto",
                                                                         "dbms_data_mining.odms_missing_value_delete_row"],
                            "description"                : {"English",
                                                                     "how to treat missing values"}
                         },
                                                       {"name"                : "RALG_PARAMETER_FAMILY",
                            "name_display"   : {"English", "RALG_PARAMETER_FAMILY"},
                            "type"                  : "string",
                            "optional"            :  "TRUE",
                            "value"                :  "",
                            "description"       : {"English", "R family parameter in build function"}
                         }
],
                        }
```

--------------------------------

### Compute Lift Using Probabilities (PL/SQL)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

Executes the COMPUTE_LIFT procedure to calculate lift statistics. It takes the apply results table, target table, and model details as input. This example specifically computes lift based on probabilities and stores the results in 'nb_lift'. Dependencies include the 'nb_apply_results' table created previously.

```plsql
BEGIN
	   DBMS_DATA_MINING.COMPUTE_LIFT (
  	      apply_result_table_name	        => 'nb_apply_results',
  	      target_table_name 	             => 'mining_data_test_v',
  	      case_id_column_name	            => 'cust_id',
  	      target_column_name	             => 'affinity_card',
  	      lift_table_name	                => 'nb_lift',
  	      positive_target_value	          =>  to_char(1),
  	      score_column_name 	             => 'PREDICTION',
  	      score_criterion_column_name    => 'PROBABILITY',
  	      num_quantiles	                  =>  10,
  	      cost_matrix_table_name	         =>  null,
  	      apply_result_schema_name	       =>  null,
  	      target_schema_name	             =>  null,
  	      cost_matrix_schema_name	        =>  null,
  	      score_criterion_type	           =>  'PROBABILITY');
  	END;
  	/

```

--------------------------------

### GET_MODEL_SETTINGS Function

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

The GET_MODEL_SETTINGS function returns the settings used to build the given model. This function is deprecated starting from Oracle Database 12c Release 2.

```APIDOC
## GET_MODEL_SETTINGS Function

### Description
Returns the settings used to build the given model. Deprecated starting from Oracle Database 12c Release 2.

### Syntax
```sql
FUNCTION get_model_settings(model_name IN VARCHAR2)
  RETURN DM_Model_Settings PIPELINED;
```

### Parameters
#### Path Parameters
- **model_name** (VARCHAR2) - Required - Name of the model in the form [schema_name.]model_name. If you do not specify a schema, then your own schema is used.

### Return Values
#### Success Response (200)
- **DM_MODEL_SETTINGS** (DM_MODEL_SETTINGS) - A set of rows of type `DM_MODEL_SETTINGS`. The rows have the following columns:
    - **SETTING_NAME** (VARCHAR2(30)) - The name of the setting.
    - **SETTING_VALUE** (VARCHAR2(4000)) - The value of the setting.

### Request Example
```sql
-- Example of how to call the function (conceptual, actual usage depends on SQL context)
SELECT setting_name, setting_value
FROM TABLE(DBMS_DATA_MINING.GET_MODEL_SETTINGS('MY_MODEL'));
```

### Response Example
```text
SETTING_NAME                   SETTING_VALUE
------------------------------ ------------------------------
ALGO_NAME                       ALGO_NAIVE_BAYES
PREP_AUTO                       ON
ODMS_MAX_PARTITIONS             1000
NABS_SINGLETON_THRESHOLD       0
CLAS_WEIGHTS_BALANCED          OFF
NABS_PAIRWISE_THRESHOLD        0
ODMS_PARTITION_COLUMNS         GENDER,Y_BOX_GAMES
ODMS_MISSING_VALUE_TREATMENT   ODMS_MISSING_VALUE_AUTO
ODMS_SAMPLING                  ODMS_SAMPLING_DISABLE

9 rows selected.
```
```

--------------------------------

### Get Transformation List Procedure Signature

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

This snippet shows the signature of the GET_TRANSFORM_LIST procedure from the DBMS_DATA_MINING package. It takes an OUT parameter for the transformation list and an IN parameter for model transformations.

```PL/SQL
DBMS_DATA_MINING.GET_TRANSFORM_LIST (
      xform_list           OUT NOCOPY TRANSFORM_LIST,
      model_xforms         IN  DM_TRANSFORMS);

```

--------------------------------

### Import Model from Remote Database (SQL)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

This SQL snippet illustrates importing a model ('R1MOD') from a remote database ('R1DB') into the 'xuser' schema. It involves creating a database link, granting necessary privileges (`CREATE DATABASE LINK`, `imp_full_database`), and then using `dbms_data_mining.import_model` with `remote_link` and schema/tablespace remapping. The user 'xuser' has 'SYSAUX' tablespace, and the remote user 'oml_user' has 'TBS_1'.

```sql
CONNECT / AS SYSDBA;
GRANT CREATE DATABASE LINK TO xuser;
GRANT imp_full_database TO xuser;
CONNECT xuser/xuserpassword 
CREATE DATABASE LINK oml_user_link 
         CONNECT TO oml_user IDENTIFIED BY oml_userpassword USING 'R1DB';
EXEC dbms_data_mining.import_model (
    NULL, 
   'oml_user_DIR', 
   'R1MOD',
    remote_link => 'oml_user_LINK', schema_remap => 'oml_user:XUSER', 
                    tablespace_remap => 'TBS_1:SYSAUX' );
SELECT name FROM dm_user_models;
 
NAME
-----------------------------------------------------------------------------
R1MOD
```

--------------------------------

### CREATE_MODEL2 - Association Rules with Inclusion Criteria

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

Demonstrates how to create an association rule model using CREATE_MODEL2, specifying inclusion criteria for the consequent part of the rules.

```APIDOC
## POST /api/dbms_data_mining/create_model2

### Description
Creates an association rule model with specified parameters, including the `ASSO_CONS_IN_RULES` setting to define items that must appear in the consequent of each rule.

### Method
POST

### Endpoint
`/api/dbms_data_mining/create_model2`

### Parameters
#### Request Body
- **MODEL_NAME** (string) - Required - The name of the model to be created.
- **MINING_FUNCTION** (string) - Required - The type of mining function, e.g., 'ASSOCIATION'.
- **DATA_QUERY** (string) - Required - The SQL query to retrieve the mining data.
- **SET_LIST** (object) - Required - A collection of settings for the mining function.
  - **ALGO_NAME** (string) - Specifies the algorithm, e.g., 'ALGO_APRIORI_ASSOCIATION_RULES'.
  - **PREP_AUTO** (string) - Automated preprocessing setting, e.g., 'ON'.
  - **ASSO_MIN_SUPPORT** (string) - Minimum support threshold for rules.
  - **ASSO_MIN_CONFIDENCE** (string) - Minimum confidence threshold for rules.
  - **ASSO_MAX_RULE_LENGTH** (string) - Maximum number of items in a rule.
  - **ASSO_CONS_IN_RULES** (string) - Required/Optional - Comma-separated string of items that must be included in the consequent of each rule. Example: "'a','b'"
  - **ODMS_ITEM_ID_COLUMN_NAME** (string) - The name of the column that identifies items.
  - **ASSO_AGGREGATES** (string) - Specifies aggregate columns, e.g., 'AMOUNT_SOLD'.
- **CASE_ID_COLUMN_NAME** (string) - Required - The name of the column that identifies cases.

### Request Example
```json
{
  "MODEL_NAME": "AR_SH_SAMPLE",
  "MINING_FUNCTION": "ASSOCIATION",
  "DATA_QUERY": "select * from SALES_TRANS_CUST",
  "SET_LIST": {
    "ALGO_NAME": "ALGO_APRIORI_ASSOCIATION_RULES",
    "PREP_AUTO": "ON",
    "ASSO_MIN_SUPPORT": "0.04",
    "ASSO_MIN_CONFIDENCE": "0.1",
    "ASSO_MAX_RULE_LENGTH": "2",
    "ASSO_CONS_IN_RULES": "'a','b'",
    "ODMS_ITEM_ID_COLUMN_NAME": "PROD_NAME",
    "ASSO_AGGREGATES": "AMOUNT_SOLD"
  },
  "CASE_ID_COLUMN_NAME": "CUST_ID"
}
```

### Response
#### Success Response (200)
- **status** (string) - Operation status message.

#### Response Example
```json
{
  "status": "Model AR_SH_SAMPLE created successfully."
}
```
```

--------------------------------

### Enable/Disable EM Component Removal (SQL)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

Determines if the EM algorithm can remove small components from the solution. The default setting is to enable component removal. Example shows enabling it.

```SQL
SET_PARAMETER(parameter => 'EMCS_REMOVE_COMPONENTS', value => 'EMCS_REMOVE_COMPS_ENABLE');
-- To disable:
-- SET_PARAMETER(parameter => 'EMCS_REMOVE_COMPONENTS', value => 'EMCS_REMOVE_COMPS_DISABLE');
```

--------------------------------

### Import All Models with Schema and Tablespace Remap (PL/SQL)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

This PL/SQL block demonstrates importing all models from a dump file ('model_exp_001.dmp') created by user 'SCOTT' into the schema of user 'MARY'. It remaps the tablespace from 'USERS' to 'USER2' and specifies 'DM_DUMP' as the directory object. The user 'MARY' needs the `IMP_FULL_DATABASE` privilege.

```plsql
-- import all models
DECLARE
  file_name  VARCHAR2(40);
BEGIN
  file_name := 'model_exp_001.dmp';
  DBMS_DATA_MINING.IMPORT_MODEL(
            filename=> 'file_name',
            directory=>'DM_DUMP',
            schema_remap=>'SCOTT:MARY',
            tablespace_remap=>'USERS:USER2');
  DBMS_OUTPUT.PUT_LINE(
           'DBMS_DATA_MINING.IMPORT_MODEL of all models from SCOTT done!');
END;
/
```

--------------------------------

### GET_MODEL_DETAILS_AI Function

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

Retrieves details of an attribute importance model. This function is deprecated starting from Oracle Database 12c Release 2 and recommends using model detail views instead.

```APIDOC
## GET_MODEL_DETAILS_AI Function

### Description
Returns a set of rows providing the details of an attribute importance model. Note: This function is deprecated starting from Oracle Database 12c Release 2. It is recommended to use model detail views instead.

### Method
SQL Function Call

### Endpoint
N/A (SQL Function)

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Parameters
- **model_name** (VARCHAR2) - Required - Name of the model in the format `[schema_name.]model_name`. If the schema is not specified, the current user's schema is used.
- **partition_name** (VARCHAR2) - Optional, Default: NULL - Specifies a partition in a partitioned model.

### Return Values
- **DM_RANKED_ATTRIBUTES** (TABLE of DM_RANKED_ATTRIBUTE) - A collection of rows, each containing attribute details.
  - **attribute_name** (VARCHAR2(4000)) - The name of the attribute.
  - **attribute_subname** (VARCHAR2(4000)) - The subname of the attribute, if applicable.
  - **importance_value** (NUMBER) - The importance score of the attribute.
  - **rank** (NUMBER(38)) - The rank of the attribute based on its importance.

### Request Example
```sql
SELECT attribute_name, importance_value, rank
FROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_AI('AI_SH_sample'))
ORDER BY RANK;
```

### Response
#### Success Response (TABLE of DM_RANKED_ATTRIBUTE)

#### Response Example
```
ATTRIBUTE_NAME                           IMPORTANCE_VALUE       RANK
---------------------------------------- ---------------- ----------
HOUSEHOLD_SIZE                                 .151685183          1
CUST_MARITAL_STATUS                            .145294546          2
YRS_RESIDENCE                                   .07838928          3
AGE                                            .075027496          4
Y_BOX_GAMES                                    .063039952          5
EDUCATION                                      .059605314          6
HOME_THEATER_PACKAGE                           .056458722          7
OCCUPATION                                     .054652937          8
CUST_GENDER                                    .035264741          9
BOOKKEEPING_APPLICATION                        .019204751         10
PRINTER_SUPPLIES                                        0         11
OS_DOC_SET_KANJI                               -.00050013         12
FLAT_PANEL_MONITOR                             -.00509564         13
BULK_PACK_DISKETTES                            -.00540822         14
COUNTRY_NAME                                   -.01201116         15
CUST_INCOME_LEVEL                              -.03951311         16
```
```

--------------------------------

### Get Model Transformations Syntax

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

This is the syntax for the GET_MODEL_TRANSFORMATIONS function in Oracle's DBMS_DATA_MINING package. It takes the model name and optionally a partition name as input and returns a DM_Transforms object.

```SQL
DBMS_DATA_MINING.get_model_transformations(
      model_name IN VARCHAR2,
      partition_name IN VARCHAR2 DEFAULT NULL)
  RETURN DM_Transforms PIPELINED;
```

--------------------------------

### List All User Mining Models (SQL)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING.html/index

This SQL query lists all the data mining models created within the current user's schema. It displays the model name, its designated mining function (e.g., CLASSIFICATION), and the algorithm used for its construction, providing a summary of available models.

```SQL
SELECT model_name, mining_function, algorithm FROM user_mining_models;
```
Tokens:
Raw