/*===============================================================
ORACLE 23AI COMPLETE AI + SQL + MACHINE LEARNING MASTER SCRIPT
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
PART 10
NATURAL LANGUAGE PROCESSING (NLP)
=================================================================
Natural Language Processing (NLP) allows Oracle databases
to analyze human language text stored in tables.
Common Use Cases
Customer review sentiment analysis
Email classification
Document categorization
Complaint analysis
Social media analytics
In this section we will build a model to classify
customer reviews into:
Positive
Neutral
Negative
Training Table
customer_reviews
Columns
review_id
review_text
sentiment
===============================================================*/
/*===============================================================
SECTION 1
VIEW TRAINING DATA
===============================================================*/
SELECT * FROM customer_reviews;
/*===============================================================
SECTION 2
TEXT CLASSIFICATION SETTINGS
===============================================================*/
DROP TABLE text_settings PURGE;
CREATE TABLE text_settings
(
setting_name VARCHAR2(30),
setting_value VARCHAR2(4000)
);
/* Select text classification algorithm */
INSERT INTO text_settings VALUES
(
'ALGO_NAME',
'ALGO_NAIVE_BAYES'
);
/* Enable text mining */
INSERT INTO text_settings VALUES
(
'PREP_AUTO',
'ON'
);
COMMIT;
/*===============================================================
SECTION 3
DROP MODEL IF EXISTS
===============================================================*/
BEGIN
DBMS_DATA_MINING.DROP_MODEL('REVIEW_SENTIMENT_MODEL');
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/
/*===============================================================
SECTION 4
CREATE TEXT CLASSIFICATION MODEL
===============================================================*/
BEGIN
DBMS_DATA_MINING.CREATE_MODEL
(
model_name => 'REVIEW_SENTIMENT_MODEL',
mining_function => DBMS_DATA_MINING.CLASSIFICATION,
data_table_name => 'CUSTOMER_REVIEWS',
case_id_column_name => 'REVIEW_ID',
target_column_name => 'SENTIMENT',
settings_table_name => 'TEXT_SETTINGS'
);
END;
/
/*===============================================================
VERIFY MODEL CREATION
===============================================================*/
SELECT
model_name,
mining_function,
algorithm
FROM user_mining_models
WHERE model_name='REVIEW_SENTIMENT_MODEL';
/*===============================================================
SECTION 5
PREDICT SENTIMENT FOR NEW REVIEWS
===============================================================*/
SELECT
review_id,
review_text,
PREDICTION
(
REVIEW_SENTIMENT_MODEL
USING review_text
) AS predicted_sentiment
FROM new_reviews_data;
/*===============================================================
SECTION 6
SENTIMENT PROBABILITY
===============================================================*/
SELECT
review_id,
review_text,
PREDICTION_PROBABILITY
(
REVIEW_SENTIMENT_MODEL,
'Positive'
USING review_text
) AS probability_positive
FROM new_reviews_data;
/*===============================================================
SECTION 7
FULL PREDICTION DETAILS
===============================================================*/
SELECT
review_id,
PREDICTION_SET
(
REVIEW_SENTIMENT_MODEL
USING review_text
) AS prediction_details
FROM new_reviews_data;
/*===============================================================
SECTION 8
TEXT ANALYTICS USE CASES
===============================================================
Businesses use NLP models for
Customer satisfaction analysis
Brand sentiment monitoring
Support ticket classification
Automatic email routing
Product review analysis
===============================================================*/
/*===============================================================
SECTION 9
TEXT SEARCH USING REGEXP
===============================================================*/
SELECT
review_id,
review_text
FROM customer_reviews
WHERE REGEXP_LIKE(review_text,'product','i');
/*===============================================================
SECTION 10
WORD FREQUENCY ANALYSIS
===============================================================*/
SELECT
review_id,
LENGTH(review_text) AS text_length
FROM customer_reviews;
/*===============================================================
END OF PART 10
===============================================================
Disclaimer and Limitation of Liability
The author provides all scripts, queries, and related materials βas isβ, without any express or implied
warranties, including but not limited to warranties of accuracy, completeness, reliability,
merchantability, or fitness for a particular purpose.
By using, executing, or implementing any part of this material, the user acknowledges and agrees
that they do so at their own risk. The author shall not be held liable for any direct, indirect,
incidental, consequential, special, or exemplary damages, including but not limited to loss of data,
loss of profits, system failures, security breaches, or any other damages or losses arising from the
use or misuse of these materials.
Users are strongly advised to thoroughly review, validate, and test all scripts, queries, and
configurations in a controlled, non-production (test) environment prior to deploying them in any
live or production systems.
It is the sole responsibility of the user to ensure that the use of these materials complies with
all applicable local, national, and international laws, regulations, and organizational policies.
By proceeding to use these materials, the user agrees to indemnify and hold harmless the author
from any claims, damages, liabilities, or expenses arising from their use.
===============================================================*/