Oracle Retail Knowledge Base

A Appendix: Innovation Workbench Workshop

10 min read

Mirror of Oracle documentation

Converted for search and offline reading. Authoritative source: Oracle. Diagrams and some complex tables are simplified — check the PDF when in doubt.

Appendix A Explore Data Analysis

retwsp_rf_cust_attr_vw a) v1, (
-- get the count of subgroups based on target classes
SELECT
EDUCATION_BCKGND_CODE,
COUNT(*) "_partition_target_cnt" FROM
retwsp_rf_cust_attr_vw GROUP BY EDUCATION_BCKGND_CODE) v2
WHERE v1.EDUCATION_BCKGND_CODE = v2.EDUCATION_BCKGND_CODE
-- random sample subgroups based on target classes in respect to the sample size
AND ORA_HASH(v1."_partition_caseid", v2."_partition_target_cnt"-1, 0) <=
(v2."_partition_target_cnt" * 60 / 100));

Test Datasets

create or replace view retwsp_rf_cust_attr_test_vw as (SELECT v1.* FROM (
-- randomly divide members of the population into subgroups based on target classes
SELECT a.*, row_number() OVER (partition by EDUCATION_BCKGND_CODE ORDER BY
ORA_HASH(CUSTOMER_WID)) "_partition_caseid"
FROM
retwsp_rf_cust_attr_vw a) v1, (
-- get the count of subgroups based on target classes
SELECT
EDUCATION_BCKGND_CODE,
COUNT(*) "_partition_target_cnt" FROM
retwsp_rf_cust_attr_vw GROUP BY EDUCATION_BCKGND_CODE) v2
WHERE v1.EDUCATION_BCKGND_CODE = v2.EDUCATION_BCKGND_CODE
-- random sample subgroups based on target classes in respect to the sample size
AND ORA_HASH(v1."_partition_caseid", v2."_partition_target_cnt"-1, 0) <=
(v2."_partition_target_cnt" * 40 / 100));

Explore Data Analysis

Now we have customer reviews we will review, build text to find document frequencies, and display them in charts. We will further extract key text for each review.

Review Customer Behavior Analysis using Text Mining

This script can be found under SQLWorkshop ’ SQL Scripts [Text Mining Feature Extraction]

-------------------------------------------------------------Build Text and Token
--------------------------------------------
DECLARE
    v_policy_name   VARCHAR2(4000);
    v_lexer_name    VARCHAR2(4000);
BEGIN
    v_policy_name := 'RETWSP_POLICY';
    v_lexer_name := 'RETWSP_LEXER';
    ctx_ddl.drop_preference(v_lexer_name);
    ctx_ddl.drop_policy(
        policy_name   => v_policy_name
    );
    ctx_ddl.create_preference(
        v_lexer_name,
        'BASIC_LEXER'
    );
    ctx_ddl.create_policy(
        policy_name   => v_policy_name,
        lexer         => v_lexer_name,
        stoplist      => 'CTXSYS.DEFAULT_STOPLIST'
    );

August 3, 2026 Appendix A-17 of A-47

Appendix A Explore Data Analysis

END;
/
DROP TABLE retwsp_feature;
CREATE TABLE retwsp_feature
    AS
        SELECT
            'REVIEWTEXT' "COLUMN_NAME",
            token,
            value,
            rank,
            count
        FROM
            (
                SELECT
                    token,
                    value,
                    RANK() OVER(
                        ORDER BY value ASC
                    ) rank,
                    count
                FROM
                    (
                        SELECT
                            column_value token,
                            ln(n / COUNT(*) ) value,
                            COUNT(*) count
                        FROM
                            (
                                SELECT
                                    t2.column_value,
                                    t1.n
                                FROM
                                    (
                                        SELECT
                                            COUNT(*) OVER() n,
                                            ROWNUM rn,
                                            odmr_engine_text.dm_policy_tokens(
                                                'RETWSP_POLICY',
                                                reviewtext
                                            ) nt
                                        FROM
                                            retwsp_customer_product_review
                                    ) t1,
                                    TABLE ( t1.nt ) t2
                                GROUP BY
                                    t2.column_value,
                                    t1.rn,
                                    t1.n
                            )
                        GROUP BY
                            column_value,
                            n
                    )
            )
        WHERE
            rank <= 3000;

SELECT
    *
FROM
    retwsp_feature;

August 3, 2026 Appendix A-18 of A-47

Appendix A Explore Data Analysis

create table retwsp_review_df as
SELECT
    *
FROM
    (
        SELECT
            nested_result.attribute_name,
            COUNT(nested_result.attribute_name) count_val
        FROM
            (
                WITH
/* Start of sql for node: RETWSP_CUSTOMER_PRODUCT_REVIEW */ "N$10001" AS (
                    SELECT /*+ inline */
                        "RETWSP_CUSTOMER_PRODUCT_REVIEW"."ASIN",
                        "RETWSP_CUSTOMER_PRODUCT_REVIEW"."REVIEWERID",
                        "RETWSP_CUSTOMER_PRODUCT_REVIEW"."OVERALL",
                        "RETWSP_CUSTOMER_PRODUCT_REVIEW"."REVIEWTEXT"
                    FROM
                        "RETWSP_DEMO_1"."RETWSP_CUSTOMER_PRODUCT_REVIEW"
                )
/* End of sql for node: RETWSP_CUSTOMER_PRODUCT_REVIEW */,
/* Start of sql for node: Build Text */ "N$10002" AS (
                    SELECT /*+ inline */
                        "REVIEWERID",
                        "ASIN",
                        "OVERALL",
                        odmr_engine_text.dm_text_token_features(
                            'RETWSP_POLICY',
                            "REVIEWTEXT",
                            'RETWSP_FEATURE',
                            NULL,
                            50,
                            'IDF'
                        ) "REVIEWTEXT_TOK"
                    FROM
                        "N$10001"
                )
/* End of sql for node: Build Text */ SELECT
                    *
                FROM
                    "N$10002"
            ) output_result,
            TABLE ( output_result.reviewtext_tok ) nested_result
        GROUP BY
            nested_result.attribute_name
    )
ORDER BY count_val DESC;
select * from retwsp_review_df;
-------------------------------------------------------------Feature Extraction and
Feature Comparison --------------------------------------------
-- Create the settings table
DROP TABLE RETWSP_ESA_settings;
CREATE TABLE RETWSP_ESA_settings (
    setting_name VARCHAR2(30),
    setting_value VARCHAR2(30));
     DECLARE  ---------- sub-block begins
        already_exists   EXCEPTION;
        PRAGMA EXCEPTION_INIT(already_exists, -00955);

August 3, 2026 Appendix A-19 of A-47

Appendix A Explore Data Analysis

        v_stmt VARCHAR2(4000);
        v_param_name VARCHAR2(100);
        v_param_value VARCHAR2(100);
     BEGIN
      dbms_output.put_line('Start Populate settings table' ||
dbms_data_mining.algo_name);
      dbms_output.put_line('Start Populate settings table' ||
dbms_data_mining.algo_nonnegative_matrix_factor);
      v_param_name := dbms_data_mining.algo_name;
      v_param_value := dbms_data_mining.ALGO_NONNEGATIVE_MATRIX_FACTOR;
      v_stmt := 'INSERT INTO RETWSP_ESA_settings (setting_name, setting_value) VALUES
(''' || v_param_name || ''',''' || v_param_value || ''')';
      dbms_output.put_line('Start Populate settings table v_stmt --' || v_stmt);
      EXECUTE IMMEDIATE v_stmt;
      v_param_name := dbms_data_mining.prep_auto;
      v_param_value := dbms_data_mining.prep_auto_on;
      v_stmt := 'INSERT INTO RETWSP_ESA_settings (setting_name, setting_value) VALUES
(''' || v_param_name || ''',''' || v_param_value || ''')';
      dbms_output.put_line('Start Populate settings table v_stmt --' || v_stmt);
      EXECUTE IMMEDIATE v_stmt;
      EXCEPTION
          WHEN already_exists THEN
            dbms_output.put_line('Exception not found');
     END;  ------------- sub-block ends
/
create view RETWSP_CUST_PROD_REVIEW_VW as (select reviewid, reviewtext from
RETWSP_CUSTOMER_PRODUCT_REVIEW);

DECLARE
   v_xlst              dbms_data_mining_transform.TRANSFORM_LIST;
   v_policy_name       VARCHAR2(130) := 'RETWSP_POLICY';
   v_model_name        varchar2(50) := 'RETWSP_ESA_MODEL';
BEGIN
    v_xlst := dbms_data_mining_transform.TRANSFORM_LIST();
    DBMS_DATA_MINING_TRANSFORM.SET_TRANSFORM(v_xlst, 'REVIEWTEXT', NULL, 'REVIEWTEXT',
NULL, 'TEXT(POLICY_NAME:'||v_policy_name||')(MAX_FEATURES:3000)(MIN_DOCUMENTS:1)
(TOKEN_TYPE:NORMAL)');
    DBMS_DATA_MINING.DROP_MODEL(v_model_name, TRUE);
    DBMS_DATA_MINING.CREATE_MODEL(
        model_name          => v_model_name,
        mining_function     => DBMS_DATA_MINING.FEATURE_EXTRACTION,
        data_table_name     => 'RETWSP_CUST_PROD_REVIEW_VW',
        case_id_column_name => 'REVIEWID',
        settings_table_name => 'RETWSP_ESA_SETTINGS',
        xform_list          => v_xlst);
END;
/
------------------
-- List top (largest) 3 features that represent are represented in each review.
-- Explain the attributes which most impact those features.
-- This can be used in UI to display all key features in each review.
select REPLACE(xt.attr_name,'"REVIEWTEXT".',''), xt.attr_value, xt.attr_weight,
xt.attr_rank
from (SELECT S.feature_id fid, value val,
       FEATURE_DETAILS(RETWSP_ESA_MODEL, S.feature_id, 5 using T.*) det
FROM
  (SELECT v.*, FEATURE_SET(RETWSP_ESA_MODEL, 3 USING *) fset
    FROM RETWSP_CUSTOMER_PRODUCT_REVIEW v
   WHERE reviewid = 1) T,

August 3, 2026 Appendix A-20 of A-47

Appendix A Innovate

Decision Tree Model Details

Here are the results.

SELECT
 dbms_data_mining.get_model_details_xml('SL_DT_CHURN_MODEL')
 AS DT_DETAILSFROM dual;

Results of the XML is as follows, next step will be to parse the XML and

<PMML version="2.1">
  <Header copyright="Copyright (c) 2004, Oracle Corporation. All rights reserved."/>
  <DataDictionary numberOfFields="4">
    <DataField name="AGE_RANGE" optype="categorical"/>
    <DataField name="ANNL_INCOME_RANGE" optype="categorical"/>
    <DataField name="CHURN_SCORE" optype="categorical"/>
    <DataField name="PARTY_TYPE_CODE" optype="categorical"/>
  </DataDictionary>
  <TreeModel modelName="SL_DT_CHURN_MODEL" functionName="classification"
splitCharacteristic="binarySplit">
    <Extension name="buildSettings">
      <Setting name="TREE_IMPURITY_METRIC" value="TREE_IMPURITY_GINI"/>
      <Setting name="TREE_TERM_MAX_DEPTH" value="7"/>
      <Setting name="TREE_TERM_MINPCT_NODE" value=".05"/>
      <Setting name="TREE_TERM_MINPCT_SPLIT" value=".1"/>
      <Setting name="TREE_TERM_MINREC_NODE" value="10"/>
      <Setting name="TREE_TERM_MINREC_SPLIT" value="20"/>
      <costMatrix>
        <costElement>
          <actualValue>0</actualValue>
          <predictedValue>0</predictedValue>
          <cost>0</cost>
        </costElement>
        <costElement>
          <actualValue>0</actualValue>
          <predictedValue>1</predictedValue>
          <cost>1</cost>
        </costElement>
        <costElement>
          <actualValue>0</actualValue>
          <predictedValue>2</predictedValue>
          <cost>2</cost>
        </costElement>
        <costElement>
          <actualValue>0</actualValue>
          <predictedValue>3</predictedValue>
          <cost>3</cost>
        </costElement>
        <costElement>
          <actualValue>1</actualValue>
          <predictedValue>0</predictedValue>
          <cost>1</cost>
        </costElement>
        <costElement>
          <actualValue>1</actualValue>
          <predictedValue>1</predictedValue>
          <cost>0</cost>
        </costElement>
        <costElement>
          <actualValue>1</actualValue>
          <predictedValue>2</predictedValue>

August 3, 2026 Appendix A-29 of A-47

Appendix A Innovate

          <cost>2</cost>
        </costElement>
        <costElement>
          <actualValue>1</actualValue>
          <predictedValue>3</predictedValue>
          <cost>3</cost>
        </costElement>
        <costElement>
          <actualValue>2</actualValue>
          <predictedValue>0</predictedValue>
          <cost>3</cost>
        </costElement>
        <costElement>
          <actualValue>2</actualValue>
          <predictedValue>1</predictedValue>
          <cost>2</cost>
        </costElement>
        <costElement>
          <actualValue>2</actualValue>
          <predictedValue>2</predictedValue>
          <cost>0</cost>
        </costElement>
        <costElement>
          <actualValue>2</actualValue>
          <predictedValue>3</predictedValue>
          <cost>1</cost>
        </costElement>
        <costElement>
          <actualValue>3</actualValue>
          <predictedValue>0</predictedValue>
          <cost>3</cost>
        </costElement>
        <costElement>
          <actualValue>3</actualValue>
          <predictedValue>1</predictedValue>
          <cost>2</cost>
        </costElement>
        <costElement>
          <actualValue>3</actualValue>
          <predictedValue>2</predictedValue>
          <cost>1</cost>
        </costElement>
        <costElement>
          <actualValue>3</actualValue>
          <predictedValue>3</predictedValue>
          <cost>0</cost>
        </costElement>
      </costMatrix>
    </Extension>
    <MiningSchema>
      <MiningField name="AGE_RANGE" usageType="active"/>
      <MiningField name="ANNL_INCOME_RANGE" usageType="active"/>
      <MiningField name="CHURN_SCORE" usageType="predicted"/>
      <MiningField name="PARTY_TYPE_CODE" usageType="active"/>
    </MiningSchema>
    <Node id="0" score="2" recordCount="100427">
      <True/>
      <ScoreDistribution value="2" recordCount="46479"/>
      <ScoreDistribution value="1" recordCount="32131"/>
      <ScoreDistribution value="3" recordCount="13794"/>
      <ScoreDistribution value="0" recordCount="8023"/>
      <Node id="1" score="2" recordCount="71604">

August 3, 2026 Appendix A-30 of A-47

Appendix A Innovate

        <CompoundPredicate booleanOperator="surrogate">
          <SimpleSetPredicate field="AGE_RANGE" booleanOperator="isIn">
            <Array type="string">&quot;20-29&quot; &quot;40-49&quot; &quot;50-59&quot;
&quot;70-79&quot; </Array>
          </SimpleSetPredicate>
          <SimpleSetPredicate field="ANNL_INCOME_RANGE" booleanOperator="isIn">
            <Array type="string">&quot;0k-39k&quot; &quot;40k-59k&quot;
&quot;80k-99k&quot; </Array>
          </SimpleSetPredicate>
        </CompoundPredicate>
        <ScoreDistribution value="2" recordCount="46479"/>
        <ScoreDistribution value="3" recordCount="13794"/>
        <ScoreDistribution value="1" recordCount="11331"/>
        <Node id="2" score="2" recordCount="43586">
          <CompoundPredicate booleanOperator="surrogate">
            <SimpleSetPredicate field="AGE_RANGE" booleanOperator="isIn">
              <Array type="string">&quot;40-49&quot; &quot;70-79&quot; </Array>
            </SimpleSetPredicate>
            <SimpleSetPredicate field="PARTY_TYPE_CODE" booleanOperator="isIn">
              <Array type="string">&quot;Cautious Spender&quot; &quot;Mainstream
Shoppers&quot; &quot;Money and Brains&quot; </Array>
            </SimpleSetPredicate>
          </CompoundPredicate>
          <ScoreDistribution value="2" recordCount="29792"/>
          <ScoreDistribution value="3" recordCount="13794"/>
          <Node id="5" score="2" recordCount="41577">
            <CompoundPredicate booleanOperator="surrogate">
              <SimpleSetPredicate field="AGE_RANGE" booleanOperator="isIn">
                <Array type="string">&quot;40-49&quot; </Array>
              </SimpleSetPredicate>
              <SimpleSetPredicate field="ANNL_INCOME_RANGE" booleanOperator="isIn">
                <Array type="string">&quot;0k-39k&quot; &quot;40k-59k&quot;
&quot;60k-79k&quot; &quot;80k-99k&quot; </Array>
              </SimpleSetPredicate>
            </CompoundPredicate>
            <ScoreDistribution value="2" recordCount="27783"/>
            <ScoreDistribution value="3" recordCount="13794"/>
          </Node>
          <Node id="6" score="2" recordCount="2009">
            <CompoundPredicate booleanOperator="surrogate">
              <SimpleSetPredicate field="AGE_RANGE" booleanOperator="isIn">
                <Array type="string">&quot;70-79&quot; </Array>
              </SimpleSetPredicate>
              <SimpleSetPredicate field="ANNL_INCOME_RANGE" booleanOperator="isIn">
                <Array type="string">&quot;100k+&quot; </Array>
              </SimpleSetPredicate>
            </CompoundPredicate>
            <ScoreDistribution value="2" recordCount="2009"/>
          </Node>
        </Node>
        <Node id="3" score="2" recordCount="28018">
          <CompoundPredicate booleanOperator="surrogate">
            <SimpleSetPredicate field="AGE_RANGE" booleanOperator="isIn">
              <Array type="string">&quot;20-29&quot; &quot;50-59&quot; </Array>
            </SimpleSetPredicate>
            <SimpleSetPredicate field="PARTY_TYPE_CODE" booleanOperator="isIn">
              <Array type="string">&quot;Livin Large&quot; &quot;Value Seeker&quot;
&quot;Young Professional&quot; </Array>
            </SimpleSetPredicate>
          </CompoundPredicate>
          <ScoreDistribution value="2" recordCount="16687"/>
          <ScoreDistribution value="1" recordCount="11331"/>

August 3, 2026 Appendix A-31 of A-47

Appendix A Innovate

          <Node id="7" score="2" recordCount="15567">
            <CompoundPredicate booleanOperator="surrogate">
              <SimpleSetPredicate field="AGE_RANGE" booleanOperator="isIn">
                <Array type="string">&quot;20-29&quot; </Array>
              </SimpleSetPredicate>
              <SimpleSetPredicate field="ANNL_INCOME_RANGE" booleanOperator="isIn">
                <Array type="string">&quot;0k-39k&quot; &quot;80k-99k&quot; </Array>
              </SimpleSetPredicate>
            </CompoundPredicate>
            <ScoreDistribution value="2" recordCount="10398"/>
            <ScoreDistribution value="1" recordCount="5169"/>
          </Node>
          <Node id="8" score="2" recordCount="12451">
            <CompoundPredicate booleanOperator="surrogate">
              <SimpleSetPredicate field="AGE_RANGE" booleanOperator="isIn">
                <Array type="string">&quot;50-59&quot; </Array>
              </SimpleSetPredicate>
              <SimpleSetPredicate field="ANNL_INCOME_RANGE" booleanOperator="isIn">
                <Array type="string">&quot;100k+&quot; &quot;40k-59k&quot; </Array>
              </SimpleSetPredicate>
            </CompoundPredicate>
            <ScoreDistribution value="2" recordCount="6289"/>
            <ScoreDistribution value="1" recordCount="6162"/>
          </Node>
        </Node>
      </Node>
      <Node id="4" score="1" recordCount="28823">
        <CompoundPredicate booleanOperator="surrogate">
          <SimpleSetPredicate field="AGE_RANGE" booleanOperator="isIn">
            <Array type="string">&quot;30-39&quot; &quot;60-69&quot; </Array>
          </SimpleSetPredicate>
          <SimpleSetPredicate field="ANNL_INCOME_RANGE" booleanOperator="isIn">
            <Array type="string">&quot;100k+&quot; &quot;60k-79k&quot; </Array>
          </SimpleSetPredicate>
        </CompoundPredicate>
        <ScoreDistribution value="1" recordCount="20800"/>
        <ScoreDistribution value="0" recordCount="8023"/>
        <Node id="9" score="1" recordCount="16772">
          <CompoundPredicate booleanOperator="surrogate">
            <SimpleSetPredicate field="AGE_RANGE" booleanOperator="isIn">
              <Array type="string">&quot;60-69&quot; </Array>
            </SimpleSetPredicate>
            <SimpleSetPredicate field="PARTY_TYPE_CODE" booleanOperator="isIn">
              <Array type="string">&quot;Livin Large&quot; &quot;Value Seeker&quot; </
Array>
            </SimpleSetPredicate>
          </CompoundPredicate>
          <ScoreDistribution value="1" recordCount="16772"/>
        </Node>
        <Node id="10" score="0" recordCount="12051">
          <CompoundPredicate booleanOperator="surrogate">
            <SimpleSetPredicate field="AGE_RANGE" booleanOperator="isIn">
              <Array type="string">&quot;30-39&quot; </Array>
            </SimpleSetPredicate>
            <SimpleSetPredicate field="PARTY_TYPE_CODE" booleanOperator="isIn">
              <Array type="string">&quot;Mainstream Shoppers&quot; &quot;Young
Professional&quot; </Array>
            </SimpleSetPredicate>
          </CompoundPredicate>
          <ScoreDistribution value="0" recordCount="8023"/>
          <ScoreDistribution value="1" recordCount="4028"/>
        </Node>

August 3, 2026 Appendix A-32 of A-47

Appendix A Innovate

      </Node>
    </Node>
  </TreeModel>
</PMML>

Parse XML and insert data into a table <RETWSP_TREE_CHURN_RULES> using SQL.

Create table RETWSP_TREE_CHURN_RULES as
WITH X as
(SELECT * FROM
 XMLTable('for $n in /PMML/TreeModel//Node
            let $rf :=
              if (count($n/CompoundPredicate) > 0) then
                $n/CompoundPredicate/*[1]/@field
              else
                if (count($n/SimplePredicate) > 0) then
                  $n/SimplePredicate/@field
                else
                  $n/SimpleSetPredicate/@field
            let $ro :=
              if (count($n/CompoundPredicate) > 0) then
                if ($n/CompoundPredicate/*[1] instance of
                    element(SimplePredicate)) then
                  $n/CompoundPredicate/*[1]/@operator
                else if ($n/CompoundPredicate/*[1] instance of
                    element(SimpleSetPredicate)) then
                  ("in")
                else ()
              else
                if (count($n/SimplePredicate) > 0) then
                  $n/SimplePredicate/@operator
                else if (count($n/SimpleSetPredicate) > 0) then
                  ("in")
                else ()
            let $rv :=
              if (count($n/CompoundPredicate) > 0) then
                if ($n/CompoundPredicate/*[1] instance of
                    element(SimplePredicate)) then
                  $n/CompoundPredicate/*[1]/@value
                else
                  $n/CompoundPredicate/*[1]/Array/text()
              else
                if (count($n/SimplePredicate) > 0) then
                  $n/SimplePredicate/@value
                else
                  $n/SimpleSetPredicate/Array/text()
            let $sf :=
              if (count($n/CompoundPredicate) > 0) then
                $n/CompoundPredicate/*[2]/@field
              else ()
            let $so :=
              if (count($n/CompoundPredicate) > 0) then
                if ($n/CompoundPredicate/*[2] instance of
                    element(SimplePredicate)) then
                  $n/CompoundPredicate/*[2]/@operator
                else if ($n/CompoundPredicate/*[2] instance of
                    element(SimpleSetPredicate)) then
                  ("in")
                else ()
              else ()
            let $sv :=
              if (count($n/CompoundPredicate) > 0) then

August 3, 2026 Appendix A-33 of A-47

Appendix A Innovate

                if ($n/CompoundPredicate/*[2] instance of
                    element(SimplePredicate)) then
                  $n/CompoundPredicate/*[2]/@value
                else
                  $n/CompoundPredicate/*[2]/Array/text()
              else ()
            return
              <pred id="{$n/../@id}"
                    score="{$n/@score}"
                    rec="{$n/@recordCount}"
                    cid="{$n/@id}"
                    rf="{$rf}"
                    ro="{$ro}"
                    rv="{$rv}"
                    sf="{$sf}"
                    so="{$so}"
                    sv="{$sv}"
              />'
      passing dbms_data_mining.get_model_details_xml('SL_DT_CHURN_MODEL')
            COLUMNS
              parent_node_id   NUMBER PATH '/pred/@id',
              child_node_id    NUMBER PATH '/pred/@cid',
              rec              NUMBER PATH '/pred/@rec',
              score            VARCHAR2(4000) PATH '/pred/@score',
              rule_field       VARCHAR2(4000) PATH '/pred/@rf',
              rule_op          VARCHAR2(20) PATH '/pred/@ro',
              rule_value       VARCHAR2(4000) PATH '/pred/@rv',
              surr_field       VARCHAR2(4000) PATH '/pred/@sf',
              surr_op          VARCHAR2(20) PATH '/pred/@so',
              surr_value       VARCHAR2(4000) PATH '/pred/@sv'))
select pid parent_node, nid node, rec record_count,
      score prediction, rule_pred local_rule, surr_pred local_surrogate,
      rtrim(replace(full_rule,'$O$D$M$'),' AND') full_simple_rule from (
select row_number() over (partition by nid order by rn desc) rn,
 pid, nid, rec, score, rule_pred, surr_pred, full_rule from (
 select rn, pid, nid, rec, score, rule_pred, surr_pred,
   sys_connect_by_path(pred, '$O$D$M$') full_rule from (
  select row_number() over (partition by nid order by rid) rn,
    pid, nid, rec, score, rule_pred, surr_pred,
    nvl2(pred,pred || ' AND ',null) pred from(
   select rid, pid, nid, rec, score, rule_pred, surr_pred,
     decode(rn, 1, pred, null) pred from (
    select rid, nid, rec, score, pid, rule_pred, surr_pred,
     nvl2(root_op, '(' || root_field || ' ' || root_op || ' ' || root_value || ')',
null) pred,
     row_number() over (partition by nid, root_field, root_op order by rid desc) rn from
(
     SELECT
       connect_by_root(parent_node_id) rid,
       child_node_id nid,
       rec, score,
       connect_by_root(rule_field) root_field,
       connect_by_root(rule_op) root_op,
       connect_by_root(rule_value) root_value,
       nvl2(rule_op, '(' || rule_field || ' ' || rule_op || ' ' || rule_value || ')',
null) rule_pred,
       nvl2(surr_op, '(' || surr_field || ' ' || surr_op || ' ' || surr_value || ')',
null) surr_pred,
       parent_node_id pid
       FROM (
        SELECT parent_node_id, child_node_id, rec, score, rule_field, surr_field,
rule_op, surr_op,

August 3, 2026 Appendix A-34 of A-47

  1. RO reget q- eo ke —— ai Ea Kasi Ou to ome eee Comment peated By Capatadna yt al an c # oman ean Sac Tare Mana sumer mary | ae ts * EP i } Fa seas Engocce Data Comorian: fiife Gastar === ik east mans a . = = i” cad Tle Vewny laste Lire ne ae aaa +a a a ”

Appendix A Database Tools

*Action:
Error at Line: 1 Column: 15

How to Execute a Job using DBMS Scheduler

Long running jobs must be executed under RETAILER_WORKSPACE_JOBS. These are executed in resource groups dedicated to the retailer schema.

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'retwsp_churn_model',
job_type => 'STORED_PROCEDURE',
job_action => 'retwsp.pkg_customer_analytics.proc_churn_model',
start_date => '01-JAN-17 07.00.00 PM US/Pacific',
repeat_interval => 'FREQ=YEARLY; BYDATE=0331,0630,0930,1231; ',
end_date => '31-DEC-17 07.00.00 PM US/Pacific',
job_class => 'RETAILER_WORKSPACE_JOBS',
comments => 'Retailer workspace churn model job');
END;
/

August 3, 2026 Appendix A-47 of A-47

In this guide

  • 21 Innovation Workbench — AI Foundation Implementation Guide · shares CHURN_SCORE, CREATE_MODEL, DBMS_DATA_MINING, DROP_MODEL

Graph View

Backlinks