About the OML4SQL Examples

The OML4SQL examples illustrate typical approaches to data preparation, algorithm selection, algorithm tuning, testing, and scoring.

You can learn a great deal about the OML4SQL application programming interface from the OML4SQL examples. The examples are simple. They include extensive inline comments to help you understand the code. They delete all temporary objects on exit so that you can run the examples repeatedly without setup or cleanup.

The OML4SQL examples are available on GitHub at https://github.com/oracle/oracle-db-examples/tree/master/machine-learning/sql/. Select the Database release (for example 23ai) to see the examples.

The OML4SQL examples create a set of machine learning models in the user's schema. The following table lists the file name of the example and the mining_function value and algorithm the example uses.

Table 5-1 Models Created by Examples

File Name	MINING_FUNCTION	Algorithm
`oml4sql-anomaly-detection-1class-svm.sql`	`CLASSIFICATION`	`ALGO_SUPPORT_VECTOR_MACHINE`
`oml4sql-anomaly-detection-em.sql`	`CLASSIFICATION`	`ALGO_EXPECTATION_MAXIMIZATION`
`oml4sql-association-rules.sql`	`ASSOCIATION`	`ALGO_APRIORI_ASSOCIATION_RULES`
`oml4sql-classification-decision-tree.sql`	`CLASSIFICATION`	`ALGO_DECISION_TREE`
`oml4sql-classification-glm.sql`	`CLASSIFICATION`	`ALGO_GENERALIZED_LINEAR_MODEL`
`oml4sql-classification-naive-bayes.sql`	`CLASSIFICATION`	`ALGO_NAIVE_BAYES`
`oml4sql-classification-neural-networks.sql`	`CLASSIFICATION`	`ALGO_NEURAL_NETWORK`
`oml4sql-classification-random-forest.sql`	`CLASSIFICATION`	`ALGO_RANDOM_FOREST`
`oml4sql-classification-regression-xgboost.sql`	`CLASSIFICATION`	`ALGO_XGBOOST`
`oml4sql-classification-svm.sql`	`CLASSIFICATION`	`ALGO_SUPPORT_VECTOR_MACHINES`
`oml4sql-classification-text-analysis-svm.sql`	`CLASSIFICATION`	`ALGO_SUPPORT_VECTOR_MACHINES`
`oml4sql-clustering-expectation-maximization.sql`	`CLUSTERING`	`ALGO_EXPECTATION_MAXIMIZATION`
`oml4sql-clustering-kmeanms-star-schema.sql`	`CLUSTERING`	`ALGO_KMEANS`
`oml4sql-clustering-kmeans.sql`	`CLUSTERING`	`ALGO_KMEANS`
`oml4sql-clustering-ocluster.sql`	`CLUSTERING`	`ALGO_O_CLUSTER`
`oml4sql-cross-validation-decision-tree.sql`	`CLASSIFICATION`	`ALGO_DECISION_TREE`
`oml4sql-feature-extraction-cur.sql`	`ATTRIBUTE_IMPORTANCE`	`ALGO_CUR_DECOMPOSITION`
`oml4sql-feature-extraction-nmf.sql`	`FEATURE_EXTRACTION`	`ALGO_NONNEGATIVE_MATRIX_FACTOR`
`oml4sql-feature-extraction-svd.sql`	`FEATURE_EXTRACTION`	`ALGO_SINGULAR_VALUE_DECOMP`
`oml4sql-feature-extraction-text-mining-esa.sql`	`FEATURE_EXTRACTION`	`ALGO_EXPLICIT_SEMANTIC_ANALYS`
`oml4sql-feature-extraction-text-mining-nmf.sql`	`FEATURE_EXTRACTION`	`ALGO_NONNEGATIVE_MATRIX_FACTOR`
`oml4sql-feature-extraction-text-term-extraction.sql`	`FEATURE_EXTRACTION`	`ALGO_EXPLICIT_SEMANTIC_ANALYSIS`
`oml4sql-partitioned-models-svm.sql`	`CLASSIFICATION`	`ALGO_SUPPORT_VECTOR_MACHINES`
`oml4sql-regression-glm.sql`	`REGRESSION`	`ALGO_GENERALIZED_LINEAR_MODEL`
`oml4sql-regression-neural-networks.sql`	`REGRESSION`	`ALGO_NEURAL_NETWORK`
`oml4sql-regression-random-forest.sql`	`REGRESSION`	`ALGO_RANDOM_FOREST`
`oml4sql-regression-svm.sql`	`REGRESSION`	`ALGO_SUPPORT_VECTOR_MACHINES`
`oml4sql-singular-value-decomposition.sql`	`REGRESSION`	`ALGO_SINGULAR_VALUE_DECOMPOSITION`
`oml4sql-survival-analysis-xgboost.sql`	`REGRESSION`	`ALGO_XGBOOST`
`oml4sql-time-series-esm-auto-model-search.sql`	`TIME_SERIES`	`ALGO_EXPONENTIAL_SMOOTHING`
`oml4sql-time-series-exponential-smoothing.sql`	`TIME_SERIES`	`ALGO_EXPONENTIAL_SMOOTHING`
`oml4sql-time-series-mset.sql`	`CLASSIFICATION`	`ALGO_MSET_SPRT`
`oml4sql-time-series-regression-dataset.sql`	-	This is a dataset to construct time series regression model.
`oml4sql-time-series-regression.sql`	`TIME_SERIES` and `REGRESSION`	Uses `ALGO_EXPONENTIAL_SMOOTHING`, `ALGO_GENERALIZED_MODEL`, and `ALGO_XGBOOST`

A few examples other than those listed in the table above are: oml4sql-attribute-importance.sql, which uses the DBMS_PREDICTIVE_ANALYTICS.EXPLAIN procedure to find the importance of attributes that independently impact the target attribute. oml4sql-feature-extraction-text-term-extraction.sql example, which uses the CTX.DDL package for text extraction.

Another set of examples demonstrates the use of the ALGO_EXTENSIBLE_LANG algorithm to register R language functions and create R models. The following table lists the R Extensibility examples. It shows the file name of the example and the MINING_FUNCTION value and R function used.

File Name	MINING_FUNCTION	R Function
`oml4sql-r-extensible-algorithm-registration.sql`	`CLASSIFICATION`	`glm`
`oml4sql-r-extensible-association-rules.sql`	`ASSOCIATION`	`apriori`
`oml4sql-r-extensible-attribute-importance-via-rf.sql`	`REGRESSION`	`randomForest`
`oml4sql-r-extensible-glm.sql`	`REGRESSION`	`glm`
`oml4sql-r-extensible-kmeans.sql`	`CLUSTERING`	`kmeans`
`oml4sql-r-extensible-principal-components.sql`	`FEATURE_EXTRACTION`	`prcomp`
`oml4sql-r-extensible-regression-tree.sql`	`REGRESSION`	`rpart`
`oml4sql-r-extensible-regression-neural-networks.sql`	`REGRESSION`	`nnet`

Install the OML4SQL Examples

Learn how to install OML4SQL examples.

The OML4SQL examples require:

Oracle Database (on-premises, Oracle Database Cloud Service, or Oracle Autonomous Database)
Oracle Database sample schemas
A user account with the privileges described in Grant Privileges for Oracle Machine Learning for SQL.
Running of dmshgrants.sql by a system administrator
Running of dmsh.sql by the OML4SQL user

Follow these steps to install the OML4SQL examples:

Install or obtain access to an Oracle Database 23ai instance. To install the database, see the installation instructions for your platform at Oracle Database 23ai.
Ensure that the sample schemas are installed in the database. See Oracle Database Sample Schemas for details about the sample schemas.
Download the example code files from GitHub at https://github.com/oracle/oracle-db-examples/tree/master/machine-learning/sql. Select the Database edition. Place the files in a directory to which you have access on the Oracle Database server. For example, $ORACLE_HOME/demo/schema. $ORACLE_HOME is the home path where you have installed the database. Typically, /scratch/u01/app/oracle/product/23.0.0/dbhome_1.
Verify that your user account has the required privileges described in Grant Privileges for Oracle Machine Learning for SQL.
Ask your system administrator to run the dmshgrants.sql script, or run it yourself if you have administrative privileges. The script grants the privileges that are required for running the examples. These include SELECT access to tables in the SH schema as described in OML4SQL Sample Data and the system privileges.

Connect as SYSDBA:
```
CONNECT sys / as sysdba
Enter password: sys_password
Connected.
```
Pass the name of the OML4SQL user to dmshgrants:
```
@<location_of_examples>/dmshgrants oml_user
```
Connect to the database and run the dmsh.sql script. This script creates views of the sample data in the schema of the OML4SQL user.
```
CONNECT oml_user
Enter password: oml_user_password
Connected.
```
Issue the following to run the script:
```
@<location_of_examples>/dmsh.sql
```

Related Topics

Oracle Database Sample Schemas

OML4SQL Sample Data

The data used by the OML4SQL examples is based on these tables in the SH schema.

Those tables are:

SH.CUSTOMERS 
SH.SALES 
SH.PRODUCTS 
SH.SUPPLEMENTARY_DEMOGRAPHICS
SH.COUNTRIES

The dmshgrants script grants SELECT access to the tables in the SH schema. The dmsh.sql script creates views of the SH tables in the schema of the OML4SQL user. The views are described in the following table.

Table 5-2 Views Created by dmsh.sql

View Name	Description
`MINING_DATA`	Joins and filters data
`MINING_DATA_BUILD_V`	Data for building models
`MINING_DATA_TEST_V`	Data for testing models
`MINING_DATA_APPLY_V`	Data to be scored
`MINING_BUILD_TEXT`	Data for building models that include text
`MINING_TEST_TEXT`	Data for testing models that include text
`MINING_APPLY_TEXT`	Data, including text columns, to be scored
`MINING_DATA_ONE_CLASS_V`	Data for anomaly detection

The association rules example creates its own transactional data.