-- An Example Using the PL/SQL API

--------------------------------------------------------------------
-- Data Preparation
--------------------------------------------------------------------


BEGIN
-- create a bin boundary table
dbms_data_mining_transform.create_bin_num 
     (bin_table_name => 'test_data_num_boundary');
-- Create boundaries for age and occupation (10 bins)
  dbms_data_mining_transform.insert_bin_num_eqwidth (
    bin_table_name    => 'test_data_num_boundary',
    data_table_name   => 'test_build_data',
    bin_num           => 10,
    exclude_list      => dbms_data_mining_transform.column_list 
                         ('CAMERABUYER'),
    round_num         => 0
  );    
END;
/

describe test_data_num_boundary;

SELECT * FROM test_data_num_boundary;


BEGIN
  -- create categorical bin boundary table
  dbms_data_mining_transform.create_bin_cat (
    bin_table_name => 'test_data_cat_boundary');        

  -- Categorical Bin for AGE
  dbms_data_mining_transform.insert_bin_cat_freq (
    bin_table_name    => 'test_data_cat_boundary',
    data_table_name   => 'test_build_data',
    bin_num           => 2,
    exclude_list      => dbms_data_mining_transform.column_list
                         ('CUSTOMERID', 'OCCUPATION'),
    default_num       => 0);

  -- Categorical bin for OCCUPATION
  dbms_data_mining_transform.insert_bin_cat_freq (
    bin_table_name    => 'test_data_cat_boundary',
    data_table_name   => 'test_build_data',
    bin_num           => 10,
    exclude_list      => dbms_data_mining_transform.column_list
                         ('CUSTOMERID', 'AGE'),
    default_num       => 0);
END;
/


BEGIN
-- Create the transformed view
  dbms_data_mining_transform.xform_bin_cat (
    bin_table_name  => 'test_data_cat_boundary',
    data_table_name => 'test_build_data',
    xform_view_name => 'test_data_build_cat');    
  dbms_data_mining_transform.xform_bin_num (
    bin_table_name  => 'test_data_num_boundary',
    data_table_name => 'test_data_build_cat',
    xform_view_name => 'test_data_build_prepared');    
END;
/

--------------------------------------------------------------------
-- 16.3.4.2	Building a Model
--------------------------------------------------------------------


CREATE TABLE test_data_settings (setting_name  VARCHAR2(30),
                                 setting_value VARCHAR2(30));
 
BEGIN
-- Populate settings table
 INSERT INTO test_data_settings 
        VALUES (dbms_data_mining.algo_name,
                dbms_data_mining.algo_naïve_bayes);
 INSERT INTO test_data_settings 
        VALUES (dbms_data_mining.nabs_pairwise_threshold,'.01');
 INSERT INTO test_data_settings 
        VALUES (dbms_data_mining.nabs_singleton_threshold,'.01');
 COMMIT;
END;
/

BEGIN
 dbms_data_mining.create_model(
    model_name => 'TEST_DATA_PLSQL_MODEL',
    mining_function => dbms_data_mining.classification,
    data_table_name => 'test_data_build_prepared',
    settings_table_name => 'test_data_settings',    
    case_id_column_name => 'customerid',
    target_column_name => 'camerabuyer');
END;
/

--------------------------------------------------------------------
-- Applying a Model
--------------------------------------------------------------------


-- prepare apply data
BEGIN
  dbms_data_mining_transform.xform_bin_cat (
    bin_table_name  => 'test_data_cat_boundary',
    data_table_name => 'test_apply_data',
    xform_view_name => 'test_data_apply_cat');   
  dbms_data_mining_transform.xform_bin_num (
    bin_table_name  => 'test_data_num_boundary',
    data_table_name => 'test_data_apply_cat',
    xform_view_name => 'test_data_apply_prepared');
END;
/

BEGIN
  dbms_output.put_line('Apply on apply data');
  dbms_data_mining.apply(
    model_name => 'TEST_DATA_PLSQL_MODEL',
    data_table_name => 'test_data_apply_prepared',
    case_id_column_name => 'customerid',
    result_table_name => 'test_apply_out_plsql');
  dbms_output.put_line('Completed apply');
END;
/