PL/SQL 23ai (Oracle 23c) – New Feature Boolean Data Type in SQL

Oracle 23c - BOOLEAN Column Support

'Finally! You can use BOOLEAN as a column type in tables and queries.'

SQLcl: Release 24.4 Production on Wed May 28 23:48:53 2025

Copyright (c) 1982, 2025, Oracle.  All rights reserved.

Last Successful login time: Wed May 28 2025 23:48:54 +05:30

Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.8.0.25.05

SQL> -- Create a table with a BOOLEAN column
SQL> CREATE TABLE employee_status (
  2    emp_id NUMBER PRIMARY KEY,
  3    is_active BOOLEAN
  4  );

Table EMPLOYEE_STATUS created.

SQL>
SQL> -- Insert into BOOLEAN column
SQL> INSERT INTO employee_status VALUES (1001, TRUE);

1 row inserted.

SQL> INSERT INTO employee_status VALUES (1002, FALSE);

1 row inserted.

SQL>
SQL> -- Query BOOLEAN column
SQL> SELECT * FROM employee_status WHERE is_active;

   EMP_ID IS_ACTIVE
_________ ____________
     1001 true

Use Case: Cleaner logic for status flags, without relying on 1/0 or Y/N.

Enhancing Data Security with Oracle 23ai Data Redaction

In today's data-driven landscape, safeguarding sensitive information is paramount. Oracle Database 23ai introduces advanced Data Redaction capabilities, empowering organizations to protect confidential data without altering the underlying database.

---

🔍 What is Oracle Data Redaction?

Oracle Data Redaction allows for the dynamic masking of sensitive data in query results, ensuring that unauthorized users cannot view confidential information. This feature operates transparently, modifying data output at runtime without changing the stored data.

---

🛡️ Key Features in Oracle 23ai

1. Diverse Redaction Methods:

   • Full Redaction: Replaces entire data entries with default values (e.g., zeros for numbers, spaces for text).

   • Partial Redaction: Masks parts of data, such as displaying only the last four digits of a credit card number.

   • Regular Expression Redaction: Utilizes patterns to identify and redact data, ideal for formats like email addresses.

   • Random Redaction: Substitutes data with random values, maintaining data type consistency.

   • Nullify Redaction: Replaces data with null values, effectively hiding it.

   • No Redaction: Allows for testing policies without applying redaction. 

2. Enhanced SQL Support:

   • Redacted columns can now be used in SQL expressions, including CONCAT, SUM, TRIM, MIN, and MAX, within views and inline views.

   • Support for GROUP BY and DISTINCT clauses on redacted columns, facilitating complex queries without compromising data security.

   • Set operations involving redacted columns are now supported, ensuring consistent redaction across combined query results. 

3. Performance Optimizations:

   • Policy expressions evaluating to TRUE (e.g., 1=1) are optimized, reducing unnecessary evaluations and enhancing query performance.

4. Integration with Database Features:

   • Redaction policies can be applied to columns involved in function-based indexes and extended statistics, allowing for comprehensive data protection without hindering database performance. 

---

🔧 Implementing Data Redaction Policies

Oracle provides the DBMS_REDACT PL/SQL package to manage redaction policies. Here's a basic example:

BEGIN
  DBMS_REDACT.ADD_POLICY(
    object_schema => 'HR',
    object_name   => 'EMPLOYEES',
    policy_name   => 'EMP_SALARY_REDACTION',
    column_name   => 'SALARY',
    function_type => DBMS_REDACT.FULL,
    expression    => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') != ''HR_MANAGER'''
  );
END;
/

This policy fully redacts the SALARY column in the EMPLOYEES table for users other than HR_MANAGER. 

---

📌 Use Cases

• Read-Only Applications: Redact sensitive data displayed on dashboards or reports without affecting the underlying data.

• Data Management Tools: Prevent exposure of confidential information during data loading or management operations.

• Analytics and Reporting: Ensure that analysts and report viewers see only redacted data, maintaining compliance with data privacy regulations. 

---

⚠️ Considerations

• Licensing: Data Redaction is part of Oracle's Advanced Security Option, which may require additional licensing.

• Not a Substitute for Access Control: While Data Redaction obscures data in query results, it does not replace robust access control mechanisms. 

---

Oracle Database 23ai's enhanced Data Redaction capabilities provide a robust framework for protecting sensitive information, ensuring that organizations can meet compliance requirements and safeguard data privacy without compromising functionality or performance.

---

Redacting the data in 23ai Oracle Database

 In this post we will learn how to redact Salary column in employees table of 23ai Oracle database.

As Admin user configure the redact policy as -

PL/SQL Code

BEGIN
    DBMS_REDACT.ADD_POLICY(
        object_schema => 'ADMIN',
        object_name   => 'EMP',
        policy_name   => 'REDACT_SENSITIVE_DATA',
        expression    => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''HR'''
    );
END;
/
SET ECHO ON
BEGIN
    DBMS_REDACT.ALTER_POLICY(
        object_schema => 'ADMIN',
        object_name   => 'EMP',
        column_name   => 'SAL',
        policy_name   => 'REDACT_SENSITIVE_DATA',
        function_type => DBMS_REDACT.FULL
    );
END;
/

Original table Employee Records from ADMIN.EMP

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-12-80	800		20
7499	ALLEN	SALESMAN	7698	20-02-81	1600	300	30
7521	WARD	SALESMAN	7698	22-02-81	1250	500	30
7566	JONES	MANAGER	7839	02-04-81	2975		20
7698	BLAKE	MANAGER	7839	01-05-81	2850		30
7782	CLARK	MANAGER	7839	09-06-81	2450		10
7788	SCOTT	ANALYST	7566	19-04-87	3000		20
7839	KING	PRESIDENT		17-11-81	5000		10
7844	TURNER	SALESMAN	7698	08-09-81	1500	0	30
7934	MILLER	CLERK	7782	23-01-82	1300		10

10 rows selected.

Employee Records from app user after policy is configured from ADMIN.EMP

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-12-80	0		20
7499	ALLEN	SALESMAN	7698	20-02-81	0	300	30
7521	WARD	SALESMAN	7698	22-02-81	0	500	30
7566	JONES	MANAGER	7839	02-04-81	0		20
7698	BLAKE	MANAGER	7839	01-05-81	0		30
7782	CLARK	MANAGER	7839	09-06-81	0		10
7788	SCOTT	ANALYST	7566	19-04-87	0		20
7839	KING	PRESIDENT		17-11-81	0		10
7844	TURNER	SALESMAN	7698	08-09-81	0	0	30
7934	MILLER	CLERK	7782	23-01-82	0		10

10 rows selected.

So now you see the normal app user is seeing redacted output and salary as 0, however the original contents of table has salary, please note the ADMIN user will see the original data because he has EXEMPT REDACTION POLICY privilege. So if this privilege EXEMPT REDACTION POLICY is given to someone he or she will be able to see the original contents.

Error Message = ORA-12514: Cannot connect to database.

 I recently encountered a situation where one of my free Oracle autonomous cloud database was throwing Error Message = ORA-12514: Cannot connect to database, while connecting to the database, the reason was simple - The database was hibernated in cloud because i did not access from a long time.

Here is the full error details -

SQLcl: Release 24.4 Production on Thu May 15 23:44:45 2025

Copyright (c) 1982, 2025, Oracle.  All rights reserved.

Connection failed

  USER          = admin

  URL           = jdbc:oracle:thin:@(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.ap-mumbai-1.oraclecloud.com))(connect_data=(service_name=re2d3bgg7ywwtp7_pydb_medium.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))

  Error Message = ORA-12514: Cannot connect to database. Service re2d3bgg7ywwtp7_pydb_medium.adb.oraclecloud.com is not registered with the listener at host adb.ap-mumbai-1.oraclecloud.com port 1522. (CONNECTION_ID=qI3rAjuSRe+lAwcEvdtwcg==)

https://docs.oracle.com/error-help/db/ora-12514/

Solution :- Start the database from cloud console and try again after the database is started -

SQLcl: Release 24.4 Production on Thu May 15 23:48:44 2025

Copyright (c) 1982, 2025, Oracle.  All rights reserved.

Last Successful login time: Thu May 15 2025 23:48:45 +05:30

Connected to:

Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems

Version 23.8.0.25.05

SQL> show user

USER is "ADMIN"

# So there you go , this is how you fix the Error Message = ORA-12514: Cannot connect to database

SQLcl: The Modern Oracle Command-Line Tool You Need to Know About

 In the world of Oracle database management, command-line tools have always played a vital role. Traditionally, SQL*Plus was the go-to utility for interacting with Oracle databases from the terminal. But as technology evolved, Oracle introduced a more powerful, user-friendly, and feature-rich alternative: SQLcl (SQL Command Line).

If you're an Oracle developer, DBA, or even a casual user working with Oracle databases, SQLcl can significantly improve your productivity. In this blog post, we’ll explore what SQLcl is, why it’s worth using, and how it can streamline your database workflows.

---

🌟 What is SQLcl?

SQLcl is a modern command-line interface (CLI) provided by Oracle for working with Oracle databases. It offers everything SQL*Plus does but with more enhancements, better scripting support, and developer-friendly features.

Developed as part of the Oracle Database Utilities, SQLcl is lightweight, cross-platform, and supports powerful capabilities such as:

• Command History: Navigate through previous commands easily.
• Code Formatting: Automatically format your SQL code for readability.
• Scripting Support: Run complex scripts with ease.
• JSON and CSV Output: Export query results in modern data formats.
• DML and DDL Generation: Quickly generate table structures and data.
• REST Integration: Interact with Oracle REST Data Services (ORDS).

---

⚙️ Why Should You Use SQLcl?

Here are some compelling reasons why SQLcl stands out:

1. Enhanced Productivity: With features like command history, tab completion, and syntax highlighting, working in SQLcl feels more intuitive compared to SQL*Plus.

2. Data Export Made Simple: Export query results as CSV, JSON, or even SQL inserts with a single command.

3. Built-in Liquibase Support: You can easily manage schema changes using the popular open-source tool Liquibase, integrated natively into SQLcl.

4. Cross-Platform: Whether you're on Windows, macOS, or Linux, SQLcl works seamlessly.

5. Smarter Scripting: The ability to use JavaScript alongside traditional SQL scripting expands what's possible from the command line.

How to Install SQLcl?

Installing SQLcl is straightforward:

1. Download SQLcl:
   Get the latest version from Oracle's official website:
   👉 SQLcl Download Page

2. Extract the Files:
   Unzip the downloaded file into a directory of your choice.

3. Set Up Environment Variables (Optional but Recommended):

• On Linux/macOS:
• export PATH=$PATH:/path/to/sqlcl/bin
• 
  
• On Windows:
• • Add the sqlcl\bin directory to the system PATH.

• Verify Installation:
  Open a terminal and run:

• sql

Comparison: SQLcl vs. SQL*Plus

Feature	SQL*Plus	SQLcl
Command History	❌	✅
Code Formatting	❌	✅
Export as JSON/CSV	❌	✅
REST Integration	❌	✅
Liquibase Support	❌	✅
Scripting (JavaScript)	❌	✅

Clearly, SQLcl offers a significant upgrade for Oracle users.

Conclusion: Why SQLcl Matters

SQLcl is more than just a replacement for SQL*Plus—it's a productivity powerhouse. Whether you're running ad-hoc queries, automating database tasks, or managing schema changes, SQLcl simplifies your workflow while adding modern features.

💡 Pro Tip: Combine SQLcl with shell scripting or automation tools like Ansible for even greater efficiency.

Setting up sqlcl in your macbook

 In this article you will learn how to setup sqlcl in your macbook -

Step 1) Download the zip file from below url -

https://www.oracle.com/in/database/sqldeveloper/technologies/sqlcl/download/

Step 2) Go to the destination now -

base) karandodwal@Karans-MacBook-Air Downloads % pwd

/Users/karandodwal/Downloads

(base) karandodwal@Karans-MacBook-Air Downloads % ls -ltr sqlcl-24.4.1.042.1221.zip

-rw-r--r--@ 1 karandodwal  staff  92394919 22 Feb 23:05 sqlcl-24.4.1.042.1221.zip

Step 3 ) Unzip the software now

 unzip sqlcl-24.4.1.042.1221.zip

Archive:  sqlcl-24.4.1.042.1221.zip

   creating: sqlcl/

   creating: sqlcl/bin/

   creating: sqlcl/lib/

   creating: sqlcl/lib/ext/

  inflating: sqlcl/NOTICES.txt

  inflating: sqlcl/lib/hk2-api.jar

  inflating: sqlcl/LICENSE.txt

  inflating: sqlcl/lib/javassist.jar

  inflating: sqlcl/THIRD-PARTY-LICENSES.txt

  inflating: sqlcl/lib/jersey-apache-connector.jar

  inflating: sqlcl/lib/sshd-contrib.jar

  inflating: sqlcl/24.4.1.042.1221

  inflating: sqlcl/lib/httpclient.jar

  inflating: sqlcl/bin/version.txt

  inflating: sqlcl/lib/httpcore.jar

  inflating: sqlcl/bin/dependencies.txt

  inflating: sqlcl/lib/jackson-datatype-jsr310.jar

  inflating: sqlcl/bin/sql

  inflating: sqlcl/lib/jackson-databind.jar

  inflating: sqlcl/bin/sql.exe

  inflating: sqlcl/lib/vavr-match.jar

  inflating: sqlcl/lib/oci-java-sdk-databasetools.jar

  inflating: sqlcl/lib/pom.xml

  inflating: sqlcl/lib/oci-java-sdk-common.jar

  inflating: sqlcl/lib/ext/dbtools-cpat.jar

  inflating: sqlcl/lib/oci-java-sdk-identity.jar

  inflating: sqlcl/lib/ext/cpat.jar

  inflating: sqlcl/lib/ext/dbtools-data.jar

  inflating: sqlcl/lib/ext/poi.jar

  inflating: sqlcl/lib/ext/poi-ooxml.jar

  inflating: sqlcl/lib/ext/poi-ooxml-lite.jar

  inflating: sqlcl/lib/ext/xmlbeans.jar

  inflating: sqlcl/lib/ext/commons-collections4.jar

  inflating: sqlcl/lib/ext/log4j-api-2.20.0.jar

  inflating: sqlcl/lib/ext/log4j-core-2.20.0.jar

  inflating: sqlcl/lib/ext/dbtools-datapump.jar

  inflating: sqlcl/lib/ext/dbtools-apex.jar

  inflating: sqlcl/lib/vavr.jar

  inflating: sqlcl/lib/ext/commons-lang3.jar

  inflating: sqlcl/lib/ext/text-tree.jar

  inflating: sqlcl/lib/bcpkix-jdk15to18.jar

  inflating: sqlcl/lib/ext/commons-text.jar

  inflating: sqlcl/lib/sshd-sftp.jar

  inflating: sqlcl/lib/ext/json-flattener.jar

  inflating: sqlcl/lib/bcprov-jdk15to18.jar

  inflating: sqlcl/lib/ext/json-base.jar

  inflating: sqlcl/lib/resilience4j-circuitbreaker.jar

  inflating: sqlcl/lib/ext/commons-compress.jar

  inflating: sqlcl/lib/ext/commons-codec.jar

  inflating: sqlcl/lib/ext/opencsv.jar

  inflating: sqlcl/lib/ext/snakeyaml.jar

  inflating: sqlcl/lib/ext/dbtools-liquibase.jar

  inflating: sqlcl/lib/ext/liquibase-core.jar

  inflating: sqlcl/lib/ext/dbtools-dg.jar

  inflating: sqlcl/lib/resilience4j-core.jar

  inflating: sqlcl/lib/ext/dbtools-aq.jar

  inflating: sqlcl/lib/ext/dbtools-blockchain.jar

  inflating: sqlcl/lib/sshd-scp.jar

  inflating: sqlcl/lib/ext/dbtools-modeler-cli-ext.jar

  inflating: sqlcl/lib/oci-java-sdk-secrets.jar

  inflating: sqlcl/lib/ext/dbtools-modeler-common.jar

  inflating: sqlcl/lib/ext/dbtools-jobs.jar

  inflating: sqlcl/lib/oci-java-sdk-bastion.jar

  inflating: sqlcl/lib/ext/dbtools-copy.jar

  inflating: sqlcl/lib/sshd-osgi.jar

  inflating: sqlcl/lib/ext/dbtools-sqlcl-awr-extension.jar

  inflating: sqlcl/lib/oci-java-sdk-workrequests.jar

  inflating: sqlcl/lib/ext/pushd-popd-dirs.jar

  inflating: sqlcl/lib/sshd-putty.jar

  inflating: sqlcl/lib/ext/dbtools-project.jar

  inflating: sqlcl/lib/ext/org.eclipse.jgit.jar

  inflating: sqlcl/lib/ext/dbtools-mdb.jar

  inflating: sqlcl/lib/ext/ucanaccess.jar

  inflating: sqlcl/lib/ext/jackcess.jar

  inflating: sqlcl/lib/ext/hsqldb.jar

  inflating: sqlcl/lib/ext/dbtools-mle-command-extension.jar

  inflating: sqlcl/lib/dbtools-sqlcl.jar

  inflating: sqlcl/lib/xmlparserv2_sans_jaxp_services.jar

  inflating: sqlcl/lib/antlr-runtime.jar

  inflating: sqlcl/lib/antlr4-runtime.jar

  inflating: sqlcl/lib/jakarta.json-api.jar

  inflating: sqlcl/lib/parsson.jar

  inflating: sqlcl/lib/dbtools-core.jar

  inflating: sqlcl/lib/ojdbc11.jar

  inflating: sqlcl/lib/xdb.jar

  inflating: sqlcl/lib/dbtools-arbori.jar

  inflating: sqlcl/lib/sqlcl-jline.jar

  inflating: sqlcl/lib/jansi.jar

  inflating: sqlcl/lib/jline.jar

  inflating: sqlcl/lib/dbtools-net.jar

  inflating: sqlcl/lib/jackson-annotations.jar

  inflating: sqlcl/lib/jackson-core.jar

  inflating: sqlcl/lib/jackson-jr-objects.jar

  inflating: sqlcl/lib/jackson-jr-stree.jar

  inflating: sqlcl/lib/low-level-api.jar

  inflating: sqlcl/lib/dbtools-common.jar

  inflating: sqlcl/lib/ucp11.jar

  inflating: sqlcl/lib/guava.jar

  inflating: sqlcl/lib/orai18n.jar

  inflating: sqlcl/lib/orai18n-utility.jar

  inflating: sqlcl/lib/orai18n-mapping.jar

  inflating: sqlcl/lib/oraclepki.jar

  inflating: sqlcl/lib/ST4.jar

  inflating: sqlcl/lib/orajsoda.jar

  inflating: sqlcl/lib/commons-io.jar

  inflating: sqlcl/lib/dbtools-http.jar

  inflating: sqlcl/lib/slf4j-jdk14.jar

  inflating: sqlcl/lib/slf4j-api.jar

  inflating: sqlcl/lib/httpclient5.jar

  inflating: sqlcl/lib/httpcore5.jar

  inflating: sqlcl/lib/httpcore5-h2.jar

  inflating: sqlcl/lib/dbtools-oci.jar

  inflating: sqlcl/lib/oci-java-sdk-common-httpclient-jersey3.jar

  inflating: sqlcl/lib/oci-java-sdk-common-httpclient.jar

  inflating: sqlcl/lib/jakarta.ws.rs-api.jar

  inflating: sqlcl/lib/oci-java-sdk-database.jar

  inflating: sqlcl/lib/jakarta.annotation-api.jar

  inflating: sqlcl/lib/jersey-common.jar

  inflating: sqlcl/lib/jakarta.inject-api.jar

  inflating: sqlcl/lib/osgi-resource-locator.jar

  inflating: sqlcl/lib/jersey-client.jar

  inflating: sqlcl/lib/jersey-media-json-jackson.jar

  inflating: sqlcl/lib/hk2-utils.jar

  inflating: sqlcl/lib/jersey-entity-filtering.jar

  inflating: sqlcl/lib/oci-java-sdk-circuitbreaker.jar

  inflating: sqlcl/lib/jcl-over-slf4j.jar

  inflating: sqlcl/lib/jdbcrest.jar

  inflating: sqlcl/lib/jackson-module-jakarta-xmlbind-annotations.jar

  inflating: sqlcl/lib/jakarta.activation.jar

  inflating: sqlcl/lib/jakarta.xml.bind-api.jar

  inflating: sqlcl/lib/jersey-hk2.jar

  inflating: sqlcl/lib/hk2-locator.jar

  inflating: sqlcl/lib/aopalliance-repackaged.jar

  inflating: sqlcl/lib/eddsa.jar

Step 4) launch sqlcl now, make sure your path is correct i.e - /Users/karandodwal/git/sql/sqlcl/bin should be the value of the $PATH -

% echo $PATH

/Users/karandodwal/git/sql/sqlcl/bin

Step 5) Launch sqlcl now with command sql , the key thing to note us sql is command because in the bin folder you will see sql file as an executable as -

 cd /Users/karandodwal/git/sql/sqlcl/bin

(base) karandodwal@Karans-MacBook-Air bin % ls -ltr

total 416

-rwxr-xr-x@ 1 karandodwal  staff   29954 11 Feb 12:20 sql

-rw-r-----@ 1 karandodwal  staff     152 11 Feb 12:23 version.txt

-rw-r-----@ 1 karandodwal  staff    3704 11 Feb 12:23 dependencies.txt

-rwxr-xr-x@ 1 karandodwal  staff  169984 11 Feb 12:24 sql.exe

-rw-------@ 1 karandodwal  staff       0 22 Feb 23:10 history.log

Now fire the sql command with ./sql or sql as our path is already sorted above.

(base) karandodwal@Karans-MacBook-Air ~ % sql

SQLcl: Release 24.4 Production on Sun Feb 23 00:07:50 2025

Copyright (c) 1982, 2025, Oracle.  All rights reserved.

Username? (''?)

# In the next article you will learn how to connect to autonomous database using sqlcl, stay tuned.

Unveiling DBMS_SEARCH in Oracle 23 AI: The New Era of Database Search

In the world of database management, efficiently locating data is paramount. With the advent of Oracle 23 AI, Oracle has introduced a game-changing feature: `DBMS_SEARCH`. This new feature, designed to simplify and accelerate search operations within Oracle databases, is making waves in the industry. In this blog post, we will dive deep into what `DBMS_SEARCH` is, how it works, and its benefits through a real-world example.

Imagine a global e-commerce company, "ShopEasy," with millions of users, transactions, products, and reviews stored in its Oracle 23ai database. As the business grows, so does the need to quickly retrieve data that powers search functionality on their platform. Users may want to find products based on specific keywords, filter by categories, or even search through product reviews to see what others are saying. The traditional approach would involve writing complex SQL queries and potentially creating multiple indexes. However, with Oracle 23ai's `DBMS_SEARCH` package, things have become much more streamlined.

### What is `DBMS_SEARCH`?

`DBMS_SEARCH` is a new built-in package in Oracle 23 AI that allows you to perform powerful and flexible full-text searches directly within the database. It provides a simplified, high-performance method for searching across multiple tables and columns, offering a unified search interface similar to popular search engines like Elasticsearch but directly integrated within Oracle.

### How Does `DBMS_SEARCH` Work?

Under the hood, `DBMS_SEARCH` leverages AI-powered indexing and advanced algorithms to understand the context of data better. It automatically indexes the specified columns, understands the relationships between tables, and optimizes search queries to provide near-instantaneous results. The package comes with several built-in functions, such as `DBMS_SEARCH.CREATE_INDEX`, `DBMS_SEARCH.SEARCH`, and `DBMS_SEARCH.DROP_INDEX`, allowing developers to define search contexts, execute searches, and manage indexes effortlessly.

For example, to set up a search index across the `PRODUCTS` and `REVIEWS` tables, you can use:

```sql
BEGIN
DBMS_SEARCH.CREATE_INDEX(
index_name => 'product_review_index',
tables => 'PRODUCTS, REVIEWS',
columns => 'product_name, review_text'
);
END;
/
```

With this setup, the `DBMS_SEARCH.SEARCH` function can be used to search for "wireless headphones" across both tables, returning relevant product names and reviews in a single, optimized query:

```sql
SELECT * FROM TABLE(DBMS_SEARCH.SEARCH('product_review_index', 'wireless headphones'));
```

### Benefits of `DBMS_SEARCH`

The benefits of `DBMS_SEARCH` are manifold:

1. **Unified Search Across Multiple Tables**: Unlike traditional methods where each table would require separate indexing and querying, `DBMS_SEARCH` allows for a single, cohesive search across multiple tables and columns.

2. **AI-Powered Optimization**: By leveraging AI, Oracle 23ai intelligently optimizes search queries, reducing latency and boosting performance. This is especially useful for real-time applications like e-commerce, where quick search results are crucial.

3. **Simplified Development and Maintenance**: Developers can set up and manage search contexts with just a few lines of code. There's no need for external search engines or additional infrastructure.

4. **Enhanced User Experience**: For "ShopEasy," `DBMS_SEARCH` directly translates to a better user experience. Users can search for products, filter by reviews, or find popular items effortlessly, driving more engagement and sales.

### A Real-World Impact

For "ShopEasy," implementing `DBMS_SEARCH` led to a 50% reduction in search query latency and simplified their search infrastructure. The customers could instantly find products matching their queries, read reviews, and make purchases, boosting customer satisfaction and revenue.

### Conclusion

`DBMS_SEARCH` in Oracle 23 AI represents a significant leap forward in database search capabilities, bringing the power of AI-driven search directly into the hands of developers. Whether you’re running an e-commerce platform, a financial service, or a healthcare application, `DBMS_SEARCH` offers an efficient, high-performance, and unified way to handle search operations. With its ease of use, powerful performance, and integration with Oracle's robust database ecosystem, `DBMS_SEARCH` is set to become a must-have tool for modern database management.