Oracle Database 23c: A Comprehensive Guide

Introduction to Oracle Database 23c

Oracle Database 23c, also known as Oracle Database 23c, is the latest iteration of Oracle's powerful, enterprise-grade database management system. This version introduces a multitude of features and enhancements that aim to revolutionize data management, streamline processes, and enhance performance and scalability. As organizations increasingly rely on data to drive decision-making, Oracle Database 23c stands out as a critical tool for managing and leveraging large volumes of data efficiently.

Key Features of Oracle Database 23c

1. Advanced AI and Machine Learning Integration

Oracle Database 23c introduces advanced AI and machine learning (ML) capabilities directly within the database. This integration allows for more intelligent data processing and analytics. With these features, users can build, train, and deploy machine learning models using SQL, making it easier for data scientists and developers to create sophisticated applications without needing external tools.

2. Enhanced Multitenancy

The concept of multitenancy in Oracle Database 23c has been significantly improved. This feature allows multiple pluggable databases (PDBs) to run within a single container database (CDB). The new enhancements provide better resource allocation, isolation, and management of these PDBs, enabling more efficient use of hardware and reducing operational costs.

3. Autonomous Database Enhancements

Oracle's Autonomous Database capabilities have been further refined in 23c. These enhancements include improved self-driving, self-securing, and self-repairing functionalities. The database can automatically handle routine tasks such as tuning, backups, and security updates, freeing up valuable time for database administrators (DBAs) and ensuring optimal performance and security.

4. Blockchain Table Enhancements

Blockchain technology within Oracle Database 23c has seen notable improvements. Blockchain tables provide immutable and tamper-evident storage of data, ensuring data integrity and security. These enhancements include better performance, scalability, and support for more complex transactions, making it easier to implement blockchain solutions within the database.

5. In-Memory Database Advancements

Oracle Database 23c continues to push the boundaries of in-memory database technology. The advancements in this area include faster data processing, improved query performance, and more efficient use of memory resources. These improvements enable real-time analytics and faster decision-making processes.

Performance and Scalability Improvements

1. Optimized Query Performance

Oracle Database 23c introduces numerous optimizations to improve query performance. These include advanced indexing techniques, improved execution plans, and better use of hardware resources. These enhancements ensure that even the most complex queries are executed quickly and efficiently, providing faster access to critical data.

2. Improved Storage Management

Storage management in Oracle Database 23c has been enhanced to provide more efficient data storage and retrieval. This includes better compression algorithms, automated storage tiering, and improved support for large datasets. These features help reduce storage costs and ensure that data is always available when needed.

3. Scalable Architecture

The scalable architecture of Oracle Database 23c allows it to handle increasing workloads without compromising performance. This is achieved through better parallel processing, improved load balancing, and support for distributed databases. As a result, organizations can scale their database infrastructure to meet growing data demands seamlessly.

Security Enhancements

1. Advanced Encryption Techniques

Security is a top priority in Oracle Database 23c, with the introduction of advanced encryption techniques. These techniques ensure that data is protected both at rest and in transit, preventing unauthorized access and data breaches. The database also supports transparent data encryption, making it easier to implement and manage encryption policies.

2. Enhanced User Authentication

User authentication mechanisms have been strengthened in Oracle Database 23c. This includes multi-factor authentication (MFA), single sign-on (SSO) integration, and improved password policies. These features help ensure that only authorized users can access sensitive data and systems.

3. Comprehensive Auditing and Compliance

Oracle Database 23c offers comprehensive auditing and compliance features. This includes detailed logging of database activities, automated compliance checks, and support for regulatory requirements such as GDPR and CCPA. These features help organizations monitor and manage database security more effectively.

Simplified Database Management

1. Unified Management Console

The unified management console in Oracle Database 23c provides a centralized interface for managing all aspects of the database. This includes configuration, monitoring, and troubleshooting tools. The console simplifies database administration tasks, making it easier for DBAs to manage and optimize their database environments.

2. Automated Maintenance

Automated maintenance features in Oracle Database 23c help reduce the workload on DBAs. This includes automated backups, patching, and updates. These features ensure that the database is always up-to-date and running smoothly, without the need for manual intervention.

3. Integrated Development Environment

Oracle Database 23c includes an integrated development environment (IDE) that supports a wide range of programming languages and tools. This includes support for SQL, PL/SQL, Java, and more. The IDE provides a rich set of features for developing, testing, and deploying database applications, making it easier for developers to build robust and scalable solutions.

Conclusion

Oracle Database 23c represents a significant leap forward in database technology. With its advanced AI and ML integration, enhanced multitenancy, autonomous capabilities, and robust security features, it provides a powerful platform for managing and leveraging data. The performance and scalability improvements, combined with simplified management and enhanced development tools, make it an ideal choice for organizations looking to optimize their data infrastructure.

For those seeking to harness the full potential of their data, Oracle Database 23c offers unparalleled capabilities and a clear path to innovation and efficiency.

Continue reading

Unlocking the Power of Oracle Databases: A Comprehensive Guide to 19c, 21c, and 23c

In today's digital landscape, data management lies at the heart of every successful organization. Enterprises worldwide rely on robust database management systems to store, retrieve, and manipulate vast amounts of data efficiently. Among the plethora of options available, Oracle Databases stand out as a cornerstone in the realm of relational databases.

Introduction to Oracle Databases

Oracle Corporation, a global leader in database technology, has been at the forefront of innovation for decades. With each iteration, Oracle databases evolve to meet the ever-changing demands of modern businesses. The latest versions, Oracle 19c, 21c, and the upcoming 23c, offer a myriad of features and enhancements, empowering organizations to unlock the full potential of their data infrastructure.

Oracle 19c: The Foundation of Stability and Performance

Oracle Database 19c serves as the bedrock of reliability and performance for enterprises worldwide. Built upon a foundation of stability, this version introduces numerous advancements aimed at optimizing database operations. From enhanced security features to improved automation capabilities, Oracle 19c sets the standard for efficient data management in the digital age.

Key Features of Oracle 19c

• Automatic Indexing: Streamline database performance with intelligent index management.
• Real Application Testing: Ensure seamless application upgrades and migrations with comprehensive testing tools.
• Database In-Memory: Accelerate query performance and analytics with in-memory processing capabilities.
• Multitenant Architecture: Maximize resource utilization and cost-efficiency through consolidated database deployment.

Embracing Innovation with Oracle 21c

As technology continues to evolve, so do the demands placed on database systems. Oracle Database 21c emerges as a beacon of innovation, delivering cutting-edge features designed to address the challenges of the digital era. From native blockchain tables to persistent memory support, Oracle 21c empowers organizations to stay ahead of the curve.

Revolutionary Features of Oracle 21c

• Native JSON Data Type: Simplify development and integration of JSON-based applications.
• Blockchain Tables: Ensure data immutability and integrity with blockchain-powered tables.
• Persistent Memory Support: Enhance performance and scalability with direct access to persistent memory devices.
• AutoML for In-Database Machine Learning: Democratize machine learning capabilities with automated model generation and deployment.

Looking Ahead: Oracle 23c and Beyond

As businesses embrace digital transformation, the need for agile and scalable database solutions becomes more apparent than ever. Oracle Database 23c, poised for release in the near future, promises to redefine the boundaries of data management once again. While specific details remain under wraps, industry analysts anticipate groundbreaking features that will further cement Oracle's position as a leader in the database landscape.

Anticipated Innovations in Oracle 23c

• Autonomous Operations: Leverage AI and machine learning to automate routine database tasks.
• Enhanced Security: Bolster data protection with advanced encryption and access control mechanisms.
• Serverless Architecture: Embrace cloud-native principles with a serverless database deployment model.
• Hybrid Cloud Integration: Seamlessly extend on-premises databases to the cloud for enhanced flexibility and scalability.

Conclusion

In conclusion, Oracle Databases represent the pinnacle of innovation and reliability in the world of data management. From the stalwart stability of Oracle 19c to the cutting-edge features of Oracle 21c and the promise of Oracle 23c, Oracle continues to empower organizations to harness the full potential of their data assets. By staying abreast of the latest advancements and embracing the power of Oracle technology, businesses can position themselves for success in an increasingly data-driven world.

Continue reading

Unveiling the Power of Oracle Database 23c: Revolutionizing Data Management

In the ever-evolving landscape of data management, Oracle has yet again set a new standard with the introduction of Oracle Database 23c. This groundbreaking release brings forth a plethora of features and enhancements designed to streamline operations, enhance security, and elevate performance to unprecedented levels. In this comprehensive guide, we delve into the intricate details of Oracle Database 23c, exploring its key features, benefits, and the transformative impact it can have on your organization's data infrastructure.

Enhanced Security Measures

Security is paramount in today's digital age, and Oracle Database 23c delivers on this front with an array of enhanced security measures. With features such as Data Safe and Transparent Data Encryption, organizations can fortify their data against unauthorized access and cyber threats. Additionally, Oracle Label Security provides granular control over data access, allowing organizations to enforce fine-grained security policies based on user roles and data classifications.

Advanced Machine Learning Capabilities

Harnessing the power of machine learning, Oracle Database 23c empowers organizations to unlock valuable insights from their data like never before. Through features such as AutoML, organizations can automate the process of building and deploying machine learning models, enabling data-driven decision-making at scale. Furthermore, Adaptive Query Optimization leverages machine learning algorithms to optimize query performance, ensuring efficient utilization of resources and maximizing throughput.

Seamless Scalability and Performance

Scalability and performance are cornerstones of any robust data management solution, and Oracle Database 23c excels in both areas. With support for multitenant architecture and in-memory processing, organizations can effortlessly scale their databases to accommodate growing workloads while maintaining optimal performance levels. Moreover, Automatic Indexing leverages machine learning algorithms to automatically create and manage indexes, further enhancing query performance and reducing administrative overhead.

Cloud-Native Innovations

As organizations increasingly embrace cloud-native technologies, Oracle remains at the forefront with Oracle Database 23c's cloud-native innovations. With support for Oracle Autonomous Database and Oracle Cloud Infrastructure, organizations can seamlessly migrate their on-premises databases to the cloud, unlocking greater agility, scalability, and cost-efficiency. Additionally, Oracle Exadata Cloud@Customer brings the power of Exadata to the cloud, allowing organizations to leverage the industry's leading database platform without the need for upfront infrastructure investments.

Enhanced Data Integration and Management

Efficient data integration and management are essential for driving actionable insights and maximizing the value of data assets. Oracle Database 23c offers a comprehensive suite of tools and features for data integration, data warehousing, and real-time analytics, empowering organizations to extract, transform, and load data from disparate sources with ease. With Oracle Data Integrator and Oracle GoldenGate, organizations can streamline data movement and replication, ensuring data consistency and integrity across the entire ecosystem.

Future-Proof Your Data Infrastructure with Oracle Database 23c

In conclusion, Oracle Database 23c represents a paradigm shift in the realm of data management, combining unparalleled security, advanced machine learning capabilities, seamless scalability, and cloud-native innovations to deliver a comprehensive solution for modern organizations. Whether you're looking to enhance security, boost performance, or embrace cloud-native technologies, Oracle Database 23c has you covered. Embrace the future of data management with Oracle Database 23c and stay ahead of the curve in today's data-driven world.

Continue reading

Oracle Database 23c: New feature - Direct Joins for UPDATE and DELETE Statements

Oracle Database 23c introduces a host of powerful features aimed at enhancing database performance and query optimization. Among these innovations, Direct Join stands out as an efficient mechanism to streamline update and delete operations involving multiple related tables.

23c now allows you to use direct joins to other tables in UPDATE and DELETE statements in the FROM clause, in this article we will use SQL code examples to HR schema, so you can practice and learn it.

The main benefit is to make the coding of these Direct Joins simpler, using less code and more readable for SQL developers.

Scenario 1: Updating Salaries based on Department and City

Consider the scenario where we need to update the salaries of employees working in the Marketing department in the city of Toronto by increasing their salaries by 10%.

Prior to Oracle Database 23c, we might have used a query similar to this:

Once we identify the records that need to be updated, we proceed to design our UPDATE statement to modify only those records. Therefore, in Oracle versions prior to 23c, the filter used in the UPDATE statement is quite similar to the filter used in the SELECT statement.

Prior to Oracle Database 23c, the common approach to formulate the UPDATE statement involved employing an inner query within the WHERE clause.

However, in 23c, we can harness the power of Direct Join to optimize this update operation:

By utilizing Direct Join, we simplify the query, eliminate subqueries, and improve overall performance.

Scenario 2: Delete employee Neena's job history where she worked as AC_ACCOUNT

Suppose we need to delete employee Neena's job history where she worked as AC_ACCOUNT.

Prior to Oracle Database 23c, we might have used a query similar to this:

Once we identify the records that need to be deleted, we proceed to design our DELETE statement to remove only those records. Therefore, in prior to 23c, the filter used in the DELETE statement is quite similar to the filter used in the SELECT statement.

Prior to Oracle Database 23c, the common approach to formulating the DELETE statement involved employing a subquery or inner query within the WHERE clause.

However, 23c, we can harness the power of Direct Join to optimize this DELETE operation:

Source: oracle.com

Continue reading

Oracle Globally Distributed Database supports RAFT Replication in Oracle Database 23c

Distributed databases are gaining increasing popularity and significance due to their capacity to manage large and intricate datasets, scalability, flexibility, and compatibility with contemporary application designs and architectures. As organizations embrace cloud-based solutions and microservices-based architectures, there is an anticipated surge in the demand for distributed databases. Businesses are on a continual quest for innovative database solutions that facilitate horizontal scalability, ensure data durability, and maintain high availability while adopting more adaptable strategies.

Scaling databases is imperative for handling transactional volume and ensuring optimal performance. Additionally, in an increasingly global business landscape, geographic scaling is crucial to tailor services to customers located in different parts of the world.

Resilience is a vital consideration when scaling databases, as businesses strive to guarantee survival in the event of the failure of an entire Availability Zone/Availability Domain (AZ/AD) or cloud provider.

The escalating demand for distributed databases, coupled with the necessity for horizontal and geographic scaling, is propelling businesses toward adopting next-generation methodologies such as Oracle Distributed Database. This ensures efficient performance and resilience, meeting the expectations of modern consumers. Oracle Distributed Database functions as a data distribution system, employing advanced techniques to partition data across multiple servers or shards, thereby delivering exceptional performance, availability, and scalability.

Oracle Database 23c introduces a noteworthy feature in the form of Raft replication, a consensus-based replication protocol that facilitates automatic configuration of replication across all shards. Raft replication seamlessly integrates with applications, providing transparency in its operation. In case of shard host failures or dynamic changes in the distributed database's composition, Raft replication automatically reconfigures replication settings. The system takes a declarative approach to configure the replication factor, ensuring a specified number of replicas are consistently available.

Swift failover is a key attribute of Raft replication, enabling all nodes to remain active even in the event of a node failure. Notably, this feature incorporates an automatic sub-second failover mechanism, reinforcing both data integrity and operational continuity. Such capabilities make this feature well-suited for organizations seeking a highly available and scalable database system.

When Raft replication is enabled, a distributed database contains multiple replication units. A replication unit (RU) is a set of chunks that have the same replication topology. Each RU has multiple replicas placed on different shards. The Raft consensus protocol is used to maintain consistency between the replicas in case of failures, network partitioning, message loss, or delay.

Replication Unit

When Raft replication is enabled, a distributed database contains multiple replication units. A replication unit (RU) is a set of chunks that have the same replication topology. Each RU has three replicas placed on different shards. The Raft consensus protocol is used to maintain consistency between the replicas in case of failures, network partitioning, message loss, or delay.

Each shard contains replicas from multiple RUs. Some of these replicas are leaders and some are followers. Raft replication tries to maintain a balanced distribution of leaders and followers across shards. By default each shard is a leader for two RUs and is a follower for four other RUs. This makes all shards active and provides optimal utilization of hardware resources.

Raft Group

Each replication unit contains exactly one chunk set and has a leader and a set of followers, and these members form a raft group. The leader and its followers for a replication unit contain replicas of the same chunk set in different shards as shown below. A shard can be the leader for some replication units and a follower for other replication units.

All DMLs for a particular subset of data are executed in the leader first, and then are replicated to its followers.

Replication Factor

The replication factor (RF) determines the number of participants in a RAFT group. This number includes the leader and its followers.

The RU needs a majority of replicas available for write.

• RF = 3: tolerates one replica failure
• RF = 5: tolerates two replica failures

In Oracle Globally Distributed Database , the replication factor is specified for the entire database, that is all replication units in the database have the same RF. The number of followers is limited to two, thus the replication factor is three.

Raft Log

Each RU is associated with a set of Raft logs and OS processes that maintain the logs and replicate changes from the leader to followers. This allows multiple RUs to operate independently and in parallel within a single shard and across multiple shards. It also makes it possible to scale the replication up and down by changing the number of RUs.

Changes to data made by a DML are recorded in the Raft log. A commit record is also recorded at the end of each user transaction. Raft logs are maintained independently from redo logs and contain logical changes to rows. Logical replication reduces failover time because followers are open to incoming transactions and can quickly become the leader.

The Raft protocol guarantees that followers receive log records in the same order they are generated by the leader. A user transaction is committed on the leader as soon as half of the followers acknowledge the receipt of the commit record and writes it to the Raft log.

Transactions

On a busy system, multiple commits are acknowledged at the same time. The synchronous propagation of transaction commit records provides zero data loss. The application of DML change records to followers, however, is done asynchronously to minimize the impact on transaction latency.

Leader Election Process

Per Raft protocol, if followers do not receive data or heartbeat from the leader for a specified period of time, then a new leader election process begins.

The default heartbeat interval is 150 milliseconds, with randomized election timeouts (up to 150 milliseconds) to prevent multiple shards from triggering elections at the same time, leading to split votes.

Node Failure

Node failure and recovery are handled in an automated way with minimal impact on the application.

The failover time is sub-3 seconds with less than 10 millisecond network latencies between Availability Zones. This includes failure detection, shard failover, change of leadership, application reconnecting to new leader, and continuing business transactions as before.

The impact of the failure on the application can further be abstracted by configuring retries in JDBC driver and end customer experience will be that a particular request took longer rather than getting an error.

The following is an illustration of a distributed database with all three shards in a healthy state. Applications requests are able to reach all three shards, and replication between the leaders and followers is ongoing between the shards.

Leader Node Failure

When the leader for a replication unit becomes unavailable, followers will initiate a new leader election process using the Raft protocol.

As long as a majority of the nodes (quorum) are still healthy, the Raft protocol will ensure that a new leader is elected from the available nodes.

When one of the followers succeeds in becoming the new leader, proactive notifications are sent from the shard to the client driver of leadership change. The client driver starts routing the request to the new leader shard. Routing clients (such as UCP) are notified using ONS notifications to update their shard and chunk mapping, ensuring that they route traffic to the leader.

During this failover and reconnection period, the application could be configured to wait and retry with the retry interval and retry counts settings at the JDBC driver configuration. These are very similar to the present RAC instance failover configuration.

Upon connecting to new leader, the application will continue to function as before.

The following diagram shows that the first shard failed, and that a new leader for the replication unit whose leader was once on that first shard has been replaced by a new leader in the second shard.

Oracle Globally Distributed Database provides commands and options in the GDSCTL CLI to enable and manage Raft replication in a system-managed sharded database.

Source: oracle.com

Continue reading

SQL Firewall now built into Oracle Database 23c

SQL injection attacks and compromised accounts are the two most common techniques that adversaries use to gain full access to sensitive data stored in databases. While user and developer education has helped, success remains limited. In certain cases, fixing application code might be unfeasible, especially for packaged or legacy applications. Web application firewalls (WAFs) claim to mitigate SQL injection attacks, but attacker can bypass these pattern-matching approaches. Allowlist-based and network-based SQL firewalls offer stronger mitigation, but not for local and encrypted traffic. Moreover, they don’t have a full run-time context required for fully analyzing SQL queries.

Considering the high value of the data stored in the database, we need an SQL firewall that comprehensively scans all SQL traffic irrespective of where it comes from, without exceptions. Enforcement hinges on the firewall’s ability to understand the SQL with its full context. This context is what the database builds while running any SQL statement, including steps to resolve synonyms, object names, or dynamically generated names, and current user context. For any security to be practical, the firewall must ideally run at wire speed and facilitate easy management and monitoring of not just one database but a fleet of databases.

Oracle Database 23c’s SQL Firewall integration

It was precisely this challenge that the Oracle database security team undertook. We're thrilled to announce the integration of a powerful SQL Firewall directly into Oracle Database 23c to effectively address both SQL injection attacks and compromised account issues. What’s equally exciting is that we offer an easy solution that can have minimal impact on performance. 

The new Oracle SQL Firewall offers the following features and benefits:

• Oracle SQL Firewall inspects all incoming database connections and SQL statements, including those from PL/SQL (Oracle’s procedural extension to SQL), whether local or over the network, encrypted or clear text. It only allows explicitly authorized SQL. For all other SQL, it logs the offending statements and raises violations. This statement could have been a SQL injection attack or a new SQL statement that the authorized user hasn’t run before.
• Customers can decide whether they want to block unauthorized SQL or only log it, giving them the flexibility on how to handle attacks.
• Oracle SQL Firewall evaluates the complete SQL and the processing context. By running inside the Oracle database server, the firewall easily handles encoding of the SQL statement, synonyms, dynamically generated object names, and any SQL statements dynamically generated in PL/SQL units.
• Oracle SQL Firewall relies on the allowlisting of the authorized SQL statements and associated trusted database connection paths while blocking the rest. You train the SQL Firewall by simply capturing authorized SQL statements for an application account. Subsequently, the firewall detects and prevents unauthorized SQL and potential SQL injection attacks.
• Oracle SQL Firewall can also block connections not coming from trusted IP addresses, operating system usernames, or program names. This function is useful when you want to put some protection in place immediately, while you create the allowlist for your applications. This feature ensures that any direct access to your databases is coming exclusively from trusted endpoints.

By building Oracle SQL Firewall inside the database and streamlining its implementation, the performance overhead of Oracle SQL Firewall is negligible, making it suitable for all production workloads. Because Oracle SQL Firewall is inside the database, you don’t need to deploy or manage any external components, greatly simplifying deployment.

Figure 1 shows how Oracle SQL Firewall operates inline within the Oracle Database 23c kernel, evaluating every incoming SQL statement, regardless of origin. Oracle SQL Firewall always generates a violation log when it detects a violation of its rules. However, you can configure SQL Firewall to either allow or block violating SQL statements.

Figure 1: Oracle SQL Firewall built into the Oracle Database kernel

Managing Oracle SQL Firewall

Oracle SQL Firewall policies work at a database account level, whether of an application account or a direct database user, such as a reporting user or a database administrator. This flexibility allows you to gradually build up the protection level of the database, starting from either the database administrators or the application accounts.

You can manage Oracle SQL Firewall in multiple ways. We recommend PL/SQL procedures in the SYS.DBMS_SQL_FIREWALL package if you have a standalone implementation and are proficient in managing through commands.

If you’re looking for UI-based management or to manage multiple Oracle SQL firewalls centrally, Oracle Data Safe is your answer. If you’re already an Oracle Data Safe customer using it for user and security assessment, activity auditing, sensitive data discovery, and data masking, you can now use the same Data Safe cloud service for managing Oracle SQL Firewall and preserve your investment.

The Data Safe unified console has been extended to manage SQL Firewall. Administrators can use the console to collect SQL activities of application accounts, monitor the collection progress, create Oracle SQL Firewall policies with allowlist rules (allowed contexts, allowed SQL statements) from the captured SQL activities, and enable SQL Firewall policies. When a firewall policy is enabled, Data Safe automatically collects the firewall violation logs from the database and stores them in Data Safe. Those logs are then available for online analysis and reporting across your database fleet, as shown in Figure 2.

You can use the Data Safe REST APIs, software developer kits (SDKs), CLI, and Terraform for further automation and integration. You can also utilize the larger Oracle Cloud Infrastructure (OCI) ecosystem for integrating Oracle SQL Firewall violations with its alerts and notifications.

Figure 2: SQL Firewall dashboard in Oracle Data Safe

Complementing industry-leading database security features within the Oracle database for authentication, access control, encryption, and auditing, Oracle SQL Firewall inspects all incoming SQL statements and allows only authorized SQL statements. Oracle SQL Firewall solution provides unmatched security and administrative convenience through Data Safe.

Source: oracle.com

Continue reading

Schema-level privilege grants with Database 23c

Many modern applications separate the data-owning schema from the application service or run-time account used to access that data. This schema provides for a separation of duty and the least-privilege model and can help lower the risk if the accessing account is compromised. But how do you manage this list? As application schemas change over time, how do you keep that list current?

Previously, developers had the following options:

• Grant individual privileges on each table and view in the application schema
• Grant ANY privileges: Select any table, update any table, and so on.

The first choice may be inconvenient because you need to identify every single table or view and then grant every run-time or service user permission individually. You could develop a script, but that’s an extra step. This option is also a suboptimal way to deal with application schema changes, such as adding new tables or views, because you must now remember to make corresponding privilege grants. Also, visualizing and verifying such detailed lists can be daunting.

The second choice of granting ANY privileges, while convenient, is suboptimal from a security angle because you grant that user the ability to select from every table in the database! If this user account is compromised, your entire database can be compromised.

Oracle Database 23c to the rescue

To address this, Oracle Database 23c introduces a new schema-level grant. If you GRANT SELECT ANY TABLE ON SCHEMA HR TO BOB, that user can see all the tables and views in the HR schema - and only in the HR schema. If a new table is added to the schema, they instantly have access to that new table. No extra management is needed, and you continue to support a least-privilege security model with appropriate separation of duties.

Users can grant schema-level privileges on their own schema without having any special privileges. To grant schema-level privileges on someone else’s schema, you need either the GRANT ANY SCHEMA or GRANT ANY PRIVILEGE system privilege.

To see which schema privileges have been granted, use the DBA_SCHEMA_PRIVS view. You can also use ROLE_SCHEMA_PRIVS, USER_SCHEMA_PRIVS, and SESSION_SCHEMA_PRIVS views.

Source: oracle.com

Continue reading

Lock-Free Reservations to Prevent Blocking Sessions in Oracle Database 23c

In Oracle 23c we can use Lock-Free Reservations to reduce the incidents of blocking sessions for frequently updated numeric column values.

Relational databases have a heavy focus on ACID compliance. This stops logical data corruption, which is really important for most of our data. We would rather have sessions block each other, than risk having incorrect data in our tables.

Despite this, there are some scenarios where blocking sessions are problematic and unnecessary. The main example in this article uses a column that is frequently incremented to maintain sales total counters.

◉ The Problem : Blocking Sessions

We create a table to hold product information and seed it with some products. We include an ITEMS_SOLD column, which provides a running total of the sales as a summary, so we don't have to aggregate the raw data.

-- Clean up before doing a new run.

drop trigger if exists sales_trg;

drop table if exists sales purge;

alter table if exists products modify (items_sold not reservable);

drop table if exists products purge;

create table products (

  product     varchar2(10) primary key,

  items_sold  number

);

insert into products (product, items_sold)

values ('banana', 0),

       ('apple', 0),

       ('lemon', 0),

       ('lime', 0);

commit;

We create a table to hold sales information for our products. Each sale is a new insert, so we don't have any problems with updates getting blocked because of heavy transaction rates.

create table sales (

  id        number generated always as identity primary key,

  product   varchar2(10),

  quantity  number,

  constraint sales_products_fk foreign key (product) references products (product)

);

We would expect our application to update the PRODUCTS table for every new sale to keep the ITEMS_SOLD column up to date. In this case we will implement that using a trigger.

create or replace trigger sales_trg

after insert on sales

for each row

begin

  update products p

  set    p.items_sold = p.items_sold + :new.quantity

  where  p.product = :new.product;

end;

/

With each insert into the SALES table, our PRODUCTS table is updated. We check our current totals.

select * from products;

PRODUCT    ITEMS_SOLD

----------         ----------

banana              0

apple                 0

lemon               0

lime                   0

SQL>

We create some new sales. The trigger fires during the inserts, and we see the impact immediately in the ITEMS_SOLD column values.

insert into sales (product, quantity) values ('banana', 10);

insert into sales (product, quantity) values ('apple', 5);

select * from products;

PRODUCT    ITEMS_SOLD

----------      ----------

banana             10

apple                 5

lemon               0

lime                   0

SQL>

We commit the new sales.

commit;

The problem with this setup is inserts from different sessions can block each other. Open two new connections to the database. In session 1 we issue the following statement.

insert into sales (product, quantity) values ('banana', 1);

In session 2 we issue the following statement.

insert into sales (product, quantity) values ('banana', 5);

Notice the insert in session 2 is blocked until session 1 issues a commit or rollback. This is because both sessions are attempting to update the same row in the PRODUCTS table via the trigger.

In session 1 we issue a commit, and we see the insert in session 2 completes. Issue a commit in session 2 also.

-- Session 1

commit;

-- Session 2

commit;

select * from products;

PRODUCT    ITEMS_SOLD

----------       ----------

banana             16

apple                 5

lemon               0

lime                   0

SQL>

◉ The Solution : Lock-Free Reservations

Lock-free reservations stop the sessions from blocking each other. Transactions against a reservable column are held in a reservation journal and only processed on commit of the transaction.

Let's clean up the SALES and PRODUCTS tables.

truncate table sales;

update products

set    items_sold = 0;

commit;

We set the ITEMS_SOLD column to RESERVABLE.

alter table if exists products modify (items_sold reservable);

We check the USER_OBJECTS view and we see a reservation journal table has been created.

select object_name

from   user_objects

where  object_type = 'TABLE';

OBJECT_NAME

--------------------------------------------------------------------------------

PRODUCTS

SALES

SYS_RESERVJRNL_87694

SQL>

We describe this reservation journal table and we see it includes the primary key column (PRODUCT), the operation on the ITEMS_SOLD column (ITEMS_SOLD_OP) and the reserved value for the operation on the ITEMS_SOLD column (ITEMS_SOLD_RESERVED).

desc sys_reservjrnl_87694

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 ORA_SAGA_ID$                                       RAW(16)

 ORA_TXN_ID$                                         RAW(8)

 ORA_STATUS$                                         CHAR(12)

 ORA_STMT_TYPE$                                 CHAR(16)

 PRODUCT                                                 NOT NULL VARCHAR2(10)

 ITEMS_SOLD_OP                                     CHAR(7)

 ITEMS_SOLD_RESERVED                      NUMBER

SQL>

We create some new sales. The trigger fires during the inserts, but we don't see the impact immediately in the ITEMS_SOLD column values.

insert into sales (product, quantity) values ('banana', 10);

insert into sales (product, quantity) values ('apple', 5);

select * from products;

PRODUCT    ITEMS_SOLD

---------- ----------

banana              0

apple                 0

lemon               0

lime                  0

SQL>

We check the reservation journal table and we can see the two reservations for the updates to the PRODUCTS table.

select ora_stmt_type$,

       product,

       items_sold_op,

       items_sold_reserved       

from sys_reservjrnl_87694;

ORA_STMT_TYPE$   PRODUCT    ITEMS_S ITEMS_SOLD_RESERVED

----------------  ---------- ------- -------------------

UPDATE           apple        +                         5

UPDATE           banana     +                        10

SQL>

We issue a commit, and we see the changes to the PRODUCTS table, and the journal table is empty.

commit;

select * from products;

PRODUCT    TOTAL_QUANTITY

---------- --------------

banana                 10

apple                     5

lemon                   0

lime                       0

SQL>

select ora_stmt_type$,

       product,

       items_sold_op,

       items_sold_reserved       

from sys_reservjrnl_87694;

no rows selected

SQL>

Open two connections to the database. In session 1 we issue the following statement.

insert into sales (product, quantity) values ('banana', 1);

In session 2 we issue the following statement.

insert into sales (product, quantity) values ('banana', 5);

The insert in session 2 is no longer blocked by the first session. Both still performed their updates, but the trigger changes to the ITEMS_SOLD column in the PRODUCTS table were reserved.

We don't see any changes to the ITEMS_SOLD column until the sessions commit their changes.

select * from products;

PRODUCT    TOTAL_QUANTITY

---------- --------------

banana                 10

apple                     5

lemon                   0

lime                       0

SQL>

-- Session 2

commit;

select * from products;

PRODUCT    TOTAL_QUANTITY

---------- --------------

banana                 15

apple                     5

lemon                   0

lime                      0

SQL>

-- Session 1

commit;

select * from products;

PRODUCT    TOTAL_QUANTITY

---------- --------------

banana                 16

apple                     5

lemon                   0

lime                       0

SQL>

◉ Basic Usage

There are a lot of guidelines and restrictions associated with reservable columns, listed here, but we'll run through some basic usage below.

The table must have a primary key.

drop table if exists t1 purge;

create table t1 (

  code varchar2(10),

  res_col  number reservable

);

*

ERROR at line 1:

ORA-55728: Reservable column property can only be specified for a column on a

table that has a primary key.

SQL>

We repeat the table creation, but this time give the table a primary key and populate it with a row of data.

drop table if exists t1 purge;

create table t1 (

  code varchar2(10) primary key,

  res_col  number reservable

);

insert into t1 values ('one', 0);

commit;

We can't update the column to set a specific value. We must use a + or - operation.

update t1

set    res_col = 5

where  code = 'one';

       *

ERROR at line 1:

ORA-55746: Reservable column update statement only supports + or - operations

on a reservable column.

SQL>

As expected, we can increment or decrement the value using a + or - operation.

update t1

set    res_col = res_col + 5

where  code = 'one';

update t1

set    res_col = res_col - 5

where  code = 'one';

commit;

We can't perform any other mathematical operations on it.

update t1

set    res_col = res_col * 5

where  code = 'one';

       *

ERROR at line 1:

ORA-55746: Reservable column update statement only supports + or - operations

on a reservable column.

SQL>

We have to reference the primary key when doing an update. If we omit the primary key, we get an error.

update t1

set    res_col = res_col - 5;

       *

ERROR at line 1:

ORA-55732: Reservable column update should specify all the primary key columns

in the WHERE clause.

SQL>

We can't delete a row from a table with a reservable column if there are any outstanding reservations. We update the reservable column them attempt a delete, which fails. If we commit or rollback the update, the delete operation works fine.

update t1

set    res_col = res_col + 5

where  code = 'one';

delete from t1 where code = 'one';

*

ERROR at line 1:

ORA-55754: Resource busy error is detected for the reservable column update

statement. A delete or a DDL operation is conflicting with this update

statement.

SQL>

commit;

delete from t1 where code = 'one';

1 row deleted.

SQL>

We can't drop the table if it has a reservable column.

drop table if exists t1 purge;

                     *

ERROR at line 1:

ORA-55764: Cannot DROP or MOVE tables with reservable columns. First run "ALTER

TABLE MODIFY ( NOT RESERVABLE)" and then

DROP or MOVE the table.

SQL>

Instead we have to change the column to non reservable first, then drop the table.

alter table if exists t1 modify (res_col not reservable);

drop table if exists t1 purge;

◉ Considerations

Some things to consider about this functionality.

• There are a lot of guidelines and restrictions associated with reservable columns, listed here.
• This functionality is focussed on preventing blocking row locks on hot columns in a table.
• There is more work happening to complete the update processing. For a typical update there is now the maintenance of reservation journal and the final update.
• If there are multiple updates to the same row by our session in a single transaction, this will result in multiple journal entries for that row. When the final update is applied to the table, the multiple journal entries will be consolidated and applied to the destination table using a single update.
• Rollback and rollback to save points are supported as normal.

Source: oracle-base.com

Continue reading

Some new PL/SQL features in Oracle Database 23c

PL/SQL stands for Procedural Language for SQL. PL/SQL is Oracle’s procedural extension for SQL and is is loosely based on Ada (a variant of Pascal developed for the US Deptartment of Defense).  PL/SQL has been available in Oracle Database since version 6 (first released in 1992) while stored PL/SQL procedures/functions/packages/triggers became available with version 7.

Let us have a look at what is new with Oracle Database 23c. There are 5 main areas:

1. The SQL Transpiler

A new init.ora parameters in Oracle Database 23c called SQL_TRANSPILER enables or disables the SQL transpiler (think of a combination of translate and compile). When this parameter is set to ON, the SQL transpiler feature is enabled and PL/SQL functions are automatically transpiled into SQL expressions whenever possible (does not work for all functions), without user intervention. The conversion operation is transparent to users and can improve performance by reducing overhead accrued from switching between the SQL and PL/SQL runtime.

You can change the value with either “alter system” or “alter session” and in RAC different instances can have different values. The default value is OFF and the parameter can be modified on PDB level too.

SQL> alter system set SQL_TRANSPILER=on scope=both;

System altered.

2. The BOOLEAN data type

Another new init.ora parameter called PLSQL_IMPLICIT_CONVERSION_BOOL (false is default) allows/disallows implicit conversions in PL/SQL. PLSQL_IMPLICIT_CONVERSION_BOOL allows or disallows implicit conversions in PL/SQL. Here are the data types that are converted to boolean and vice versa:

Numeric data type to BOOLEAN:

• Non-zero numeric values are implicitly converted to the BOOLEAN value TRUE
• The numeric value of 0 is implicitly converted to the BOOLEAN value FALSE

Character data type to BOOLEAN:

• Character values such as ‘true’, ‘t’, ‘yes’, ‘y’, and ‘on’ (case-insensitive) are implicitly converted to the BOOLEAN value TRUE
• Character values such as ‘false’, ‘f’, ‘no’, ‘n’, and ‘off’ (case-insensitive) are implicitly converted to the BOOLEAN value FALSE

BOOLEAN to numeric data type

• The BOOLEAN value TRUE is implicitly converted to the numeric value 1
• The BOOLEAN value FALSE is implicitly converted to the numeric value 0

BOOLEAN to character data type

The BOOLEAN value TRUE is implicitly converted to the character value ‘TRUE’

The BOOLEAN value FALSE is implicitly converted to the character value ‘FALSE’

When this parameter is set to FALSE, PL/SQL does not support such implicit conversions. Let us see how this works in practice, here is a small table called DBAs and let us add few Oracle ACEs (and few more non-ACEs) into it (in honor of Lionel Messi winning the Golden Ball/Ballon d’Or last night for the 6th time, 30th of October 2023):

Note that regardless of the way we inserted the boolean value for each one the result is either TRUE or FALSE when PLSQL_IMPLICIT_CONVERSION_BOOL is set to TRUE:

3. IF [NOT] EXISTS Syntax Support

In 23c, the clauses IF NOT EXISTS and IF EXISTS are supported by CREATE, ALTER, and DROP DDL statements. They are used to suppress potential errors otherwise raised by the existence or non-existence of a given object, allowing you to write idempotent DDL scripts.

IF NOT EXISTS cannot be used in combination with OR REPLACE in commands using the CREATE DDL statement.

4. Extended CASE Controls

The simple CASE statement is extended in PL/SQL to support the use of dangling predicates and choice lists, allowing for simplified and less redundant code.

Dangling predicates are ordinary expressions with their left operands missing (for example > 275) that can be used as a selector_value either instead of or in combination with any number of literals or expressions. With dangling predicates, more complicated comparisons can be made without requiring a searched CASE statement.

Here is an example of how dangling predicates work:

The CASE statement is extended in PL/SQL to be consistent with the updated definitions of CASE expressions and CASE statements in the SQL:2003 Standard [ISO03a, ISO03b]. Dangling predicates allow tests other than equality to be performed in simple CASE operations. Multiple choices in WHEN clauses allow CASE operations to be written with less duplicated code.

Currently, the dangling predicates IS JSON and IS OF are not supported.

5. JSON Constructor and JSON_VALUE Support of PL/SQL Aggregate Types

The JSON constructor can now accept a PL/SQL aggregate type and return a JSON object or array populated with the aggregate type data. Conversely, the built-in function json_value now supports PL/SQL aggregate types in the RETURNING clause, mapping from JSON to the specified aggregate type.

All PL/SQL record field and collection data element type constraints are honored by json_value, including character max length, integer range checks, and not null constraints.

SQL objects and PL/SQL record type instances, including implicit records created by the %ROWTYPE attribute, are allowed as valid input to the JSON constructor. There is expanded support for user defined types as input streamlines data interchange between PL/SQL applications and languages that support JSON.

Here is a simple example of using the JSON_SERIALIZE function in order to “transform” and “pretty” print a PL/SQL aggregate (we used to call it unofficially a pseudo table in Oracle 7) into JSON:

Here is how the output from the PL/SQL block above looks like:

Source: juliandontcheff.wordpress.com

Continue reading

JSON Schema in Oracle Database 23c

In Oracle database 23c a JSON Schema can validate the structure and contents of JSON documents in your database.

What is a JSON Schema?

JSON is extremely flexible, but sometimes we want to validate the structure and contents of our JSON. A JSON Schema is a declarative language that allows us to annotate and validate JSON documents. You can get a full explanation of JSON Schema here.

In the examples below we will use the following JSON schema. It will validate the JSON is made up of a JSON object, with two mandatory items, with their minimum and maximum sizes defined.

{

  "type"       : "object",

  "properties" : {"fruit"    : {"type"      : "string",

                                "minLength" : 1,

                                "maxLength" : 10},

                  "quantity" : {"type"      : "number",

                                "minimum"   : 0,

                                "maximum"   : 100}},

  "required"   : ["fruit", "quantity"]

}

Oracle first introduced the ability to display a JSON schema when they introduced the JSON Data Guide in Oracle 12.2, and later made it easier when they enhanced the JSON_DATAGUIDE function in Oracle 18c. In both cases this the ability to display a JSON Schema, not enforce one.

VALIDATE Keyword During Table Creation

We use the VALIDATE clause along with the JSON schema when defining a JSON column in our table.

drop table if exists t1 purge;

create table t1 (

  id         number,

  json_data  json validate '{

  "type"       : "object",

  "properties" : {"fruit"    : {"type"      : "string",

                                "minLength" : 1,

                                "maxLength" : 10},

                  "quantity" : {"type"      : "number",

                                "minimum"   : 0,

                                "maximum"   : 100}},

  "required"   : ["fruit", "quantity"]

}',

  constraint t1_pk primary key (id)

);

We can see the JSON schema is associated with the column by using the USER_JSON_SCHEMA_COLUMNS view.

set long 1000000

column table_name format a10

column column_name format a11

column constraint_name format a15

column json_schema format a40

select table_name,

       column_name,

       constraint_name,

       json_schema

from   user_json_schema_columns;

TABLE_NAME COLUMN_NAME CONSTRAINT_NAME JSON_SCHEMA

---------- ----------- --------------- ----------------------------------------

T1         JSON_DATA   SYS_C0012374    {"type":"object","properties":{"fruit":{

                                       "type":"string","minLength":1,"maxLength

                                       ":10},"quantity":{"type":"number","minim

                                       um":0,"maximum":100}},"required":["fruit

                                       ","quantity"]}

SQL>

We create some data to test the JSON schema.

-- Valid

insert into t1 (id, json_data) values (1, json('{"fruit":"apple","quantity":10}'));

1 row created.

SQL>

-- Valid : Extra weight element.

insert into t1 (id, json_data) values (7, json('{"fruit":"apple","quantity":10,"weight":11}'));

1 row created.

SQL>

-- Missing quantity

insert into t1 (id, json_data) values (2, json('{"fruit":"apple"}'));

            *

ERROR at line 1:

ORA-40875: JSON schema validation error

SQL>

-- Missing fruit

insert into t1 (id, json_data) values (3, json('{"quantity":10}'));

            *

ERROR at line 1:

ORA-40875: JSON schema validation error

SQL>

-- Fruit name too long.

insert into t1 (id, json_data) values (4, json('{"fruit":"abcdefghijk","quantity":10}'));

            *

ERROR at line 1:

ORA-40875: JSON schema validation error

SQL>

-- Fruit name too short.

insert into t1 (id, json_data) values (5, json('{"fruit":"","quantity":10}'));

            *

ERROR at line 1:

ORA-40875: JSON schema validation error

SQL>

-- Quantity too big.

insert into t1 (id, json_data) values (6, json('{"fruit":"apple","quantity":101}'));

            *

ERROR at line 1:

ORA-40875: JSON schema validation error

SQL>

-- Quantity too small.

insert into t1 (id, json_data) values (7, json('{"fruit":"apple","quantity":-1}'));

            *

ERROR at line 1:

ORA-40875: JSON schema validation error

SQL>

Notice that one of the valid examples included the ability to include additional properties that were not in the JSON Schema. This is because JSON is extensible by nature, so JSON Schema only validate minimum requirements by default. We can prevent additional properties being included in the data by setting "additionalProperties" to false, as shown below.

drop table if exists t1 purge;

create table t1 (

  id         number,

  json_data  json validate '{

  "type"       : "object",

  "properties" : {"fruit"    : {"type"      : "string",

                                "minLength" : 1,

                                "maxLength" : 10},

                  "quantity" : {"type"      : "number",

                                "minimum"   : 0,

                                "maximum"   : 100}},

  "required"   : ["fruit", "quantity"],

  "additionalProperties"  : false

}',

  constraint t1_pk primary key (id)

);

insert into t1 (id, json_data) values (7, json('{"fruit":"apple","quantity":10,"weight":11}'));

            *

ERROR at line 1:

ORA-40875: JSON schema validation error

SQL>

VALIDATE Keyword With IS JSON Condition

We can use VALIDATE as part of an IS JSON condition. In the following example we recreate the table, this time using the IS JSON condition as part of a check contraint.

drop table if exists t1 purge;

create table t1 (

  id         number,

  json_data  json,

  constraint t1_pk primary key (id),

  constraint json_data_chk check (json_data is json validate '{

  "type"       : "object",

  "properties" : {"fruit"    : {"type"      : "string",

                                "minLength" : 1,

                                "maxLength" : 10},

                  "quantity" : {"type"      : "number",

                                "minimum"   : 0,

                                "maximum"   : 100}},

  "required"   : ["fruit", "quantity"]

}')

);

We can also use the VALIDATE keyword with an IS JSON condition in a query. We recreate the table without using the VALIDATE keyword, and populate it with a variety of JSON documents.

drop table if exists t1 purge;

create table t1 (

  id         number,

  json_data  json,

  constraint t1_pk primary key (id)

);

insert into t1 (id, json_data) values (1, json('{"fruit":"apple"}'));

insert into t1 (id, json_data) values (2, json('{"quantity":10}'));

insert into t1 (id, json_data) values (3, json('{"fruit":"apple","quantity":10}'));

We query the table using the IS JSON VALIDATE condition, so we only return data that matches the JSON schema.

select *

from   t1

where  json_data is json validate '{

  "type"       : "object",

  "properties" : {"fruit"    : {"type"      : "string",

                                "minLength" : 1,

                                "maxLength" : 10},

                  "quantity" : {"type"      : "number",

                                "minimum"   : 0,

                                "maximum"   : 100}},

  "required"   : ["fruit", "quantity"]

}';

        ID JSON_DATA

---------- --------------------------------------------------------------------------------

         3 {"fruit":"apple","quantity":10}

SQL>

VALIDATE Keyword With Domains

We can create a domain that uses the VALIDATE keyword to check a JSON schema. This allows us to create a reusable JSON schema.

drop domain if exists json_schema_domain;

create domain json_schema_domain as json

  constraint json_data_chk check (json_schema_domain is json validate '{

  "type"       : "object",

  "properties" : {"fruit"    : {"type"      : "string",

                                "minLength" : 1,

                                "maxLength" : 10},

                  "quantity" : {"type"      : "number",

                                "minimum"   : 0,

                                "maximum"   : 100}},

  "required"   : ["fruit", "quantity"]

}');

We can use this domain during table creation.

drop table if exists t1 purge;

create table t1 (

  id         number,

  json_data  domain json_schema_domain

);

There is a simplified form of this type of domain. Notice the check constraint has been removed.

drop table if exists t1 purge;

drop domain if exists json_schema_domain;

create domain json_schema_domain as json validate '{

  "type"       : "object",

  "properties" : {"fruit"    : {"type"      : "string",

                                "minLength" : 1,

                                "maxLength" : 10},

                  "quantity" : {"type"      : "number",

                                "minimum"   : 0,

                                "maximum"   : 100}},

  "required"   : ["fruit", "quantity"]

}';

We can use this domain during table creation.

create table t1 (

  id         number,

  json_data  domain json_schema_domain

);

Extended Data Types

JSON has limited data type support. Native binary JSON data (OSON format) adds support for scalar types not present in JSON. There is a full list of the extended types here.

For example, JSON doesn't have datetime data types, so it represents all datetime data as strings. How do we validate that data with a JSON Schema? To demonstrate this we will recreate the test table, altering the JSON Schema to add "expiryDate" as a date extended data type.

drop table if exists t1 purge;

create table t1 (

  id         number,

  json_data  json validate '{

  "type"       : "object",

  "properties" : {"fruit"    : {"type"      : "string",

                                "minLength" : 1,

                                "maxLength" : 10},

                  "quantity" : {"type"      : "number",

                                "minimum"   : 0,

                                "maximum"   : 100},

                  "expiryDate" : {"extendedType": "date"}},

  "required"   : ["fruit", "quantity", "expiryDate"]

}',

  constraint t1_pk primary key (id)

);

We try to insert some data.

insert into t1 (id, json_data) values (1, json('

{

  "fruit" : "apple",

  "quantity" : 10,

  "expiryDate" : "2023-06-30T09:30:26+0000"

}'));

            *

ERROR at line 1:

ORA-40875: JSON schema validation error

SQL>

Although this represents a valid date, it is a string, so it violates the JSON schema. We can alter the JSON to explicity identify it as a DATE datatype using "$oracleDate", and add the EXTENDED keyword to the end of the JSON constructor call.

insert into t1 (id, json_data) values (1, json('

{

  "fruit" : "apple",

  "quantity" : 10,

  "expiryDate" : {"$oracleDate" : "2023-06-30T09:30:26+0000"}

}' extended));

1 row created.

SQL>

DBMS_JSON_SCHEMA.IS_SCHEMA_VALID

The IS_SCHEMA_VALID function in the DBMS_JSON_SCHEMA package can check the validity of a JSON schema definition. In the following example we call it with a valid JSON schema, then an invalid one.

select dbms_json_schema.is_schema_valid('{

  "type"       : "object",

  "properties" : {"fruit"    : {"type"      : "string",

                                "minLength" : 1,

                                "maxLength" : 10},

                  "quantity" : {"type"      : "number",

                                "minimum"   : 0,

                                "maximum"   : 100}},

  "required"   : ["fruit", "quantity"]

}') as is_valid;

  IS_VALID

----------

         1

SQL>

select dbms_json_schema.is_schema_valid('banana') as is_valid;

*

ERROR at line 1:

ORA-40441: JSON syntax error

SQL>

Source: oracle-base.com

Continue reading