Oracle Database 23c: The Ultimate Guide to Seamless Cloud Integration

In the ever-evolving landscape of technology, businesses are increasingly turning to cloud solutions to optimize their operations. This paradigm shift extends to the realm of database management, and Oracle has responded with its groundbreaking Oracle Database 23c. In this comprehensive guide, we will navigate the intricate features and advantages of Oracle Database 23c, equipping you with the knowledge required to make well-informed decisions for your organization's database needs.

Evolution of Oracle Database:

The Oracle Database has long been a stalwart in the world of data management, setting the benchmark for reliability and performance. The advent of Oracle Database 23c marks a significant milestone in its evolution, introducing an array of innovative features that cater to the demands of the modern cloud-centric environment.

Unparalleled Cloud Integration:

A standout feature of Oracle Database 23c is its seamless integration with leading cloud platforms such as Amazon Web Services (AWS), Microsoft Azure, and Google Cloud. This integration is not merely a technical achievement; it is a solution to the challenges faced by businesses in migrating their on-premises databases to the cloud. The result? Effortless and efficient data management, superior scalability, and a reduction in operational complexities.

Advanced Security Measures:

In an era where data breaches are a constant threat, Oracle Database 23c takes security to new heights. Employing advanced encryption techniques, multi-factor authentication, and real-time monitoring, this version ensures that your data is safeguarded both during transmission and while it resides in the cloud. This level of security is of paramount importance to industries dealing with sensitive customer information and proprietary data.

Autonomous Database Capabilities:

Oracle has pioneered autonomous database technology, and Oracle Database 23c continues this legacy. Leveraging the power of machine learning and automation, this database optimizes its performance, fine-tunes itself for optimal efficiency, and proactively resolves issues without requiring human intervention. This self-driving aspect liberates IT teams from routine tasks, allowing them to focus on strategic initiatives that drive the business forward.

Enhanced Performance:

Performance is a critical factor in any database, and Oracle Database 23c rises to the challenge. Its in-memory processing capabilities turbocharge query performance, delivering rapid insights for data-driven decision-making. Moreover, its support for high-performance computing ensures that even the most resource-intensive tasks are executed swiftly and effectively.

Streamlined Management:

The complexity of managing databases can be a drain on resources. Oracle Database 23c addresses this pain point by offering an intuitive user interface that simplifies administrative tasks. From provisioning to monitoring and troubleshooting, the user-friendly interface empowers users of varying technical backgrounds to manage databases with confidence and ease.

Scalability on Demand:

As businesses grow and expand, their database requirements evolve as well. Oracle Database 23c accommodates this growth with its dynamic scalability feature. This means you can seamlessly expand your database capabilities as your business flourishes, without the need for extensive reconfigurations. The database infrastructure aligns with your growth trajectory, ensuring a seamless transition.

Global Availability:

In an interconnected global business landscape, downtime is not an option. Oracle Database 23c recognizes this reality by offering global availability features. The database maintains high availability across different regions and availability zones, guaranteeing uninterrupted access to critical data, even in the face of regional outages.

Conclusion:

Oracle Database 23c stands as a testament to Oracle's commitment to innovation and excellence in the realm of database technology. With its seamless cloud integration, advanced security measures, autonomous capabilities, and enhanced performance, this version opens doors for businesses to thrive in the digital age. By harnessing the power of Oracle Database 23c alongside the agility of cloud platforms, organizations can unlock new levels of efficiency, scalability, and actionable insights.

For those seeking a database solution that seamlessly merges with the demands of the cloud era while delivering unmatched performance and security, Oracle Database 23c emerges as the definitive choice.

Continue reading

Oracle Graph Server REST API

Oracle Graph is a powerful tool designed to uncover hidden relationships within your data. By representing information in a graph structure, Oracle Graph enables organizations to gain valuable insights from interconnected data entities. The Oracle Graph offering includes the Graph Server REST API, a gateway to utilize the capabilities of Oracle Graph from any application with a simple REST call. This API allows developers to interact with their graphs, enabling them to create graphs, run queries, and derive actionable information from their data.

Oracle Graph REST API v1 employs cookie-based authentication and encodes queries within URLs. In Oracle Graph Server and Client release 23.3 we released Oracle Graph REST API v2, which adopts token-based authentication and allows queries to be transmitted within the JSON body. The Graph Server REST API provides a vital resource for developers to easily create and query graphs.

Background for the following examples:

◉ Oracle Graph includes the ability to run graph queries in Oracle Database, and also run graph queries and analytics in a specialized in-memory Graph Server (PGX).  

◉ To load data from the bank_graph example and create a graph.

Graph Server REST API v2

Let’s run through a simple example using version 2 of the Graph Server REST API with Postman. For all of the following API calls, ensure that the request has the following headers:

◉ Accept: application/json; charset=UTF-8

◉ Content-Type: application/json

1. Get Authentication Token

Create a POST request to https://<Graph_Server_IP>:7007/auth/token. Add a JSON body with the username, password and createSession parameters.

Set the createSession parameter to True if you want to create a PGX session to run queries in the in-memory Graph Server, or False if you are running queries in the database.

The response should be an access token, which we will use in the next API calls.

2. Get Graphs

Create a GET request to https:// <Graph_Server_IP>:7007/v2/graphs, with a query parameter for the driver you want to use (GRAPH_SERVER_PGX, PGQL_IN_DATABASE or SQL_IN_DATABASE). The resulting query string should look like https:// <Graph_Server_IP>:7007/v2/graphs?driver=pgql_in_database.

Add an Authorization header with the value 'Bearer <access_token>'.

The response should be a list of graphs for your authenticated user.

3. Run a Query

Create a POST request to https:// <Graph_Server_IP>:7007/v2/runQuery.

Add an Authorization header with the value 'Bearer <access_token>'.

Add a JSON body with the statements, driver, formatter, parameters and visualize parameters, following this example:

{

  "statements": [

    "SELECT v FROM MATCH (v) ON BANK_GRAPH LIMIT 1"

  ],

  "driver": "PGQL_IN_DATABASE",

  "formatter": "GVT",

  "parameters": {

    "dynamicSampling": 2,

    "parallel": 8,

    "start": 0,

    "size": 100

  },

  "visualize": true

}

The result should be a JSON object with the result of the query run.

4. Refresh Access Token

To refresh your access token before it expires, create a PUT request to https://<Graph_Server_IP>:7007/auth/token.

Add a JSON body with token and createSession parameters. The token should have the value for the current access token. Set the createSession parameter to True if you want to create a PGX session to run queries in the in-memory Graph Server, or False if you are running queries in the database.

The response should be the new access token.

For more information on v2 of the Graph Server REST API and a full list of functionalities, visit the official documentation.

Graph Server REST API v1

Let’s run through a simple example using version 1 of the Graph Server REST API with Postman. For all of the following API calls, ensure that the request has the following headers:

◉ Accept: application/json; charset=UTF-8

◉ Content-Type: application/json

1. Authenticate user

Create a POST request to https://<Graph_Server_IP>:7007/ui/v1/login/.

Add a JSON body with username, password, pgqlDriver and baseUrl parameters. The pgqlDriver parameter specifies if you want to connect to the database, using pgqlDriver, or to Graph Server, using pgxDriver. The baseUrl parameter should be the url of Graph Server if you are using the pgxDriver, or the JDBC url for your database if you are using the pgqlDriver.

The response should be the username of the user who has been authenticated. On successful login, the server session cookie is stored in a cookie file, cookie.txt.

2. List Graphs

Create a GET request to https://<Graph_Server_IP>:7007/ui/v1/graphs. The cookie should be automatically added as a header in Postman.

The response should be a list of graphs for your authenticated user.

3. Run Query

Create a Get request to https://<Graph_Server_IP>:7007/ui/v1/query, with pgql, graph, parallelism and size parameters. The resulting url should look like:

https://<Graph_Server_IP>:7007/ui/v1/query?pgql=<PGQL_query>&graph=<graph_name>&parallelism= <parallelism_value>&size=<size>

For example to query five edges from bank_graph, we can use the following: https://<Graph_Server_IP>:7007/ui/v1/query?pgql=SELECT%20e%0AMATCH%20()-%5Be%5D-%3E()%0ALIMIT%205&graph=BANK_GRAPH&parallelism=&size=100

The result should include the resulting vertices and edges queried in a JSON format.

4. Logout of Graph Server

Create a POST request to https://<Graph_Server_IP>:7007/ui/v1/logout/.

On successful logout, the server should return HTTP status code 200 and the session token from the cookie.txt file will no longer be valid.

Source: oracle.com

Continue reading

Unlocking the Power of Data: How Oracle Database Revolutionizes Business Insights

In the ever-evolving landscape of modern business, data has emerged as the lifeblood that fuels growth, innovation, and strategic decision-making. Oracle Database stands as a formidable champion in this realm, empowering enterprises to harness the true potential of their data and drive unprecedented business insights. As we delve into the intricate tapestry of data utilization, transformation, and analysis, we'll uncover the compelling ways in which Oracle Database outshines its counterparts, ushering businesses into a new era of informed decision-making.

The Foundation of Oracle Database

At the core of Oracle Database's supremacy lies its robust architecture, meticulously designed to handle vast volumes of data with unparalleled efficiency and reliability. Boasting a foundation built on years of industry expertise, Oracle Database offers an amalgamation of cutting-edge features that cater to the diverse needs of modern businesses. From its advanced security protocols to its seamless scalability, every facet of Oracle's architecture is optimized to ensure optimal performance and data integrity.

Unleashing the Power of Data

In today's data-driven landscape, the ability to extract actionable insights from raw information is a competitive advantage coveted by businesses across the globe. Oracle Database excels in this domain through its sophisticated data processing capabilities. Through a harmonious blend of SQL and NoSQL technologies, Oracle enables users to effortlessly manage structured and unstructured data. This versatility opens doors to a myriad of analytical possibilities, enabling businesses to uncover hidden patterns, predict trends, and make informed decisions.

Seamless Integration and Scalability

Oracle Database understands that modern businesses rely on a diverse ecosystem of tools and applications to function seamlessly. To address this need, Oracle offers robust integration capabilities that allow for the smooth flow of data across different systems. This interoperability not only streamlines operations but also lays the foundation for comprehensive data analysis. Additionally, Oracle's scalability ensures that as businesses grow, their data infrastructure can effortlessly expand to accommodate increasing demands without compromising performance.

Advanced Security Protocols

In an era where data breaches and cyber threats loom as significant concerns, Oracle Database takes the lead in fortifying data security. With features such as transparent data encryption, advanced access controls, and data masking, Oracle ensures that sensitive information remains safeguarded against unauthorized access. This level of security fosters trust among customers, partners, and stakeholders, allowing businesses to focus on innovation and growth without compromising on data protection.

Empowering Business Agility

Oracle Database isn't merely a static repository of data; it's a dynamic catalyst that propels business agility. Through its support for real-time analytics and rapid data processing, Oracle equips organizations to respond swiftly to changing market dynamics. Whether it's identifying emerging trends, adapting to customer preferences, or optimizing supply chain operations, Oracle Database provides the tools needed to stay ahead in today's hyper-competitive landscape.

Revolutionizing Decision-Making

In the grand tapestry of business, decisions are the threads that weave success. Oracle Database serves as the master weaver by empowering decision-makers with comprehensive and real-time insights. Through its intuitive dashboards, visualizations, and predictive analytics, Oracle transforms raw data into actionable narratives, guiding leaders towards choices that are grounded in data-driven certainty rather than intuition.

A Glimpse into the Future

As technology continues its rapid evolution, Oracle remains committed to pushing the boundaries of what's possible. With ongoing investments in AI, machine learning, and cloud technologies, Oracle Database is poised to redefine the very notion of business insights. From predictive modeling that anticipates industry shifts to prescriptive analytics that recommend optimal strategies, the future of Oracle Database is bound to be characterized by innovation that propels businesses forward.

Conclusion

Oracle Database stands as a testament to the transformative power of data when harnessed effectively. Its robust architecture, seamless integration, advanced security, and capacity for unlocking insights make it an unparalleled asset in the realm of business technology. In a landscape where competitive advantage hinges on the ability to transform data into actionable intelligence, Oracle Database emerges as the beacon guiding enterprises towards success.

Continue reading

Predicates for JSON_QUERY and JSON_VALUE in Oracle Database 23c

In Oracle database 23c the JSON_QUERY and JSON_VALUE functions can include multiple predicates in a single JSON path expression, and use the PASSING clause to support variables.

In previous versions some simple predicates were possible with the JSON_VALUE function, but not to the extent we see in Oracle 23c.

◉ Setup

The examples in this article use the following table.

drop table if exists t1 purge;

create table t1 (

  id         number,

  json_data  json,

  constraint t1_pk primary key (id)

);

We insert some test data.

insert into t1 (id, json_data)

values (1, json('[

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

                   {"fruit":"orange","quantity":12},

                   {"fruit":"banana","quantity":8},

                   {"fruit":"lime","quantity":15},

                   {"fruit":"lemon","quantity":11}

                 ]'));

commit;

Here is the whole of the collection displayed with pretty print.

select id,

       json_query(json_data, '$'

                  returning clob pretty) as json_data

from t1;

        ID JSON_DATA

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

         1 [

             {

               "fruit" : "apple",

               "quantity" : 10

             },

             {

               "fruit" : "orange",

               "quantity" : 12

             },

             {

               "fruit" : "banana",

               "quantity" : 8

             },

             {

               "fruit" : "lime",

               "quantity" : 15

             },

             {

               "fruit" : "lemon",

               "quantity" : 11

             }

           ]

SQL>

◉ JSON_QUERY with Predicates

We use a predicate to return data for array elements where "fruit" is set to "apple".

select id,

       json_query(json_data, '$[*]?(@.fruit == "apple")') as json_data

from t1;

        ID JSON_DATA

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

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

SQL>

We use a predicate to limit the rows returned to just those where the "fruit" element is "apple" or "orange". We are returning multiple elements, so we need to use the WITH WRAPPER option. In this example we are also using the PASSING clause to define variable values, but we could have hardcoded the values as before.

select id,

       json_query(json_data, '$[*]?(@.fruit in ($v1, $v2))'

       passing 'apple' as "v1", 'orange' as "v2"

       with wrapper) as json_data

from t1;

        ID JSON_DATA

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

         1 [{"fruit":"apple","quantity":10},{"fruit":"orange","quantity":12}]

SQL>

In this example we reduce the data further by only displaying data where the "quantity" is greater than 11.

select id,

       json_query(json_data, '$[*]?(@.fruit in ($v1, $v2) && @.quantity > $v3)'

       passing 'apple' as "v1", 'orange' as "v2", 11 as "v3"

       with wrapper) as json_data

from t1;

        ID JSON_DATA

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

         1 [{"fruit":"orange","quantity":12}]

SQL>

If we only wanted the "quantity" value, we could append ".quantity" to the end of the path. We know this will return a single value, so we could remove the WITH WRAPPER keywords to remove the square brackets.

select id,

       json_query(json_data, '$[*]?(@.fruit in ($v1, $v2) && @.quantity > $v3).quantity'

       passing 'apple' as "v1", 'orange' as "v2", 11 as "v3"

       with wrapper) as json_data

from t1;

        ID JSON_DATA

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

         1 [12]

SQL>

Alternatively we could move the quantity predicate across to the "quantity" element and achieve the same result. This demonstrates the use of multiple predicates in a single JSON path expression.

select id,

       json_query(json_data, '$[*]?(@.fruit in ($v1, $v2)).quantity?(@ > $v3)'

       passing 'apple' as "v1", 'orange' as "v2", 11 as "v3"

       with wrapper) as json_data

from t1;

        ID JSON_DATA

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

         1 [12]

SQL>

◉ JSON_VALUE with Predicates

We use a predicate to return a value from where the array where "fruit" is set to "apple".

column fruit format a30

select id,

       json_value(json_data, '$[*].fruit?(@ == "apple")') as fruit

from t1;

        ID FRUIT

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

         1 apple

SQL>

We repeat the previous example, but this time add the PASSING clause to define a variable value to use in the JSON path expression.

select id,

       json_value(json_data, '$[*].fruit?(@ == $v1)'

       passing 'apple' as "v1") as fruit

from t1;

        ID FRUIT

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

         1 apple

SQL>

In this example we could return data for "apple" or "orange", but only where the "quantity" is greater than 11.

select id,

       json_value(json_data, '$[*]?(@.fruit in ($v1, $v2) && @.quantity > $v3).fruit'

       passing 'apple' as "v1", 'orange' as "v2", 11 as "v3") as fruit

from t1;

        ID FRUIT

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

         1 orange

SQL>

If we only wanted the "quantity" value, we could append ".quantity" to the end of the path. This time we add a returning clause to convert the result into a number.

select id,

       json_value(json_data, '$[*]?(@.fruit in ($v1, $v2) && @.quantity > $v3).quantity'

       passing 'apple' as "v1", 'orange' as "v2", 11 as "v3"

       returning number) as quanity

from t1;

        ID    QUANITY

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

         1         12

SQL>

Alternatively we could move the quantity predicate across to the "quantity" element and achieve the same result. This demonstrates the use of multiple predicates in a single JSON path expression.

select id,

       json_value(json_data, '$[*]?(@.fruit in ($v1, $v2)).quantity?(@ > $v3)'

       passing 'apple' as "v1", 'orange' as "v2", 11 as "v3"

       returning number) as quanity

from t1;

        ID    QUANITY

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

         1         12

SQL>

Source: oracle-base.com

Continue reading

Read-Only PDB Users in Oracle Database 23c

Oracle database 23c allows us to make PDB users read-only, which makes a connected session act like the database is opened in read-only mode, preventing the session from performing write operations.

Read-Only Users

We create a new test user and make it read-only. We grant DB_DEVELOPER_ROLE to the user, which gives it lots of object creation privileges.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

drop user if exists testuser2 cascade;

create user testuser2 identified by testuser2 quota unlimited on users read only;

grant db_developer_role to testuser2;

We check the DBA_USERS view and we can see the user is read-only.

column username format a20

column read_only format a10

select username,

       read_only

from   dba_users

where  username = 'TESTUSER2';

USERNAME             READ_ONLY

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

TESTUSER2            YES

SQL>

We connect to the test user and try a DDL statement, which fails.

conn testuser2/testuser2@//localhost:1521/freepdb1

create table t1 (id number);

*

ERROR at line 1:

ORA-28194: Can perform read operations only

SQL>

We switch the test user to read-write.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

alter user testuser2 read write;

We connect to the test user and try some DDL and DML statements, which all work as expected.

conn testuser2/testuser2@//localhost:1521/freepdb1

SQL> create table t1 (id number);

Table created.

SQL> insert into t1 values (1), (2), (3);

3 rows created.

SQL> update t1 set id = id;

3 rows updated.

SQL> delete from t1 where id = 3;

1 row deleted.

SQL> commit;

Commit complete.

SQL>

We switch the test user to read-only again.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

alter user testuser2 read only;

We connect to the test user and try some DML actions, which all fail.

conn testuser2/testuser2@//localhost:1521/freepdb1

SQL> insert into t1 values (3);

                 *

ERROR at line 1:

ORA-28194: Can perform read operations only

SQL> update t1 set id = id;

            *

ERROR at line 1:

ORA-28194: Can perform read operations only

SQL> delete from t1 where id = 3;

                 *

ERROR at line 1:

ORA-28194: Can perform read operations only

SQL> select * from t1;

        ID

----------

         1

         2

SQL>

We can see that when the user is set to read-only we can't run DDL or DML statements, but we can still query the objects.

Execute PL/SQL

A read-only user can execute any PL/SQL so long as it doesn't perform DDL or DML.

We connect to a privileged user and create two procedures, one of which performs some DML.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

create or replace procedure testuser2.my_proc1 as

begin

  dbms_output.put_line('Hello');

end;

/

create or replace procedure testuser2.my_proc2 as

begin

  insert into t1 values (4);

  commit;

end;

/

We connect to the test user and try to execute the procedures. Notice that the second procedure, which contains DML, fails.

conn testuser2/testuser2@//localhost:1521/freepdb1

SQL> set serveroutput on

SQL> exec my_proc1;

Hello

PL/SQL procedure successfully completed.

SQL>

SQL> exec my_proc2;

*

ERROR at line 1:

ORA-28194: Can perform read operations only

ORA-06512: at "TESTUSER2.MY_PROC2", line 3

ORA-06512: at line 1

SQL>

The read-only user also stops us from performing actions like SELECT ... FOR UPDATE, as shown below.

declare

  l_id  number;

begin

  select id

  into   l_id

  from   t1

  for update;

end;

/

*

ERROR at line 1:

ORA-28194: Can perform read operations only

ORA-06512: at line 4

SQL>

Source: oracle-base.com

Continue reading

Table Values Constructor in Oracle Database 23c

The table values constructor allows us to define multiple rows using a single constructor for use in SQL statements.

Setup

The following table is required to run the examples in this article.

drop table if exists t1;

create table t1 (

  id number,

  code varchar2(6),

  description varchar(25),

  constraint t1_pk primary key (id)

);

INSERT

The table values constructor allows us to insert multiple rows into a table in a single step.

insert into t1

values (1, 'ONE', 'Description for ONE'),

       (2, 'TWO', 'Description for TWO'),

       (3, 'THREE', 'Description for THREE');

commit;

select * from t1;

        ID CODE   DESCRIPTION

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

         1 ONE    Description for ONE

         2 TWO    Description for TWO

         3 THREE  Description for THREE

SQL>

That's a single network round trip without having to combine all the insert statements into a PL/SQL block.

SELECT

The same type of table values constructor can be used in the FROM clause of a SELECT statement. Notice we have to alias the column names so they are presented correctly.

select *

from   (values

          (4, 'FOUR', 'Description for FOUR'),

          (5, 'FIVE', 'Description for FIVE'),

          (6, 'SIX', 'Description for SIX')

       ) a (id, code, description);

        ID CODE DESCRIPTION

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

         4 FOUR Description for FOUR

         5 FIVE Description for FIVE

         6 SIX  Description for SIX

SQL>

WITH Clause

The table values constructor can be used as part of a WITH clause.

with a (id, code, description) AS (

  values (7, 'SEVEN', 'Description for SEVEN'),

         (8, 'EIGHT', 'Description for EIGHT'),

         (9, 'NINE', 'Description for NINE')

)

select * from a;

        ID CODE  DESCRIPTION

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

         7 SEVEN Description for SEVEN

         8 EIGHT Description for EIGHT

         9 NINE  Description for NINE

SQL>

MERGE

The table values constructor can be used as the source data for a MERGE statement.

merge into t1 a

  using (values

          (4, 'FOUR', 'Description for FOUR'),

          (5, 'FIVE', 'Description for FIVE'),

          (6, 'SIX', 'Description for SIX')

        ) b (id, code, description)

  on (a.id = b.id)

  when matched then

    update set a.code        = b.code,

               a.description = b.description

  when not matched then

    insert (a.id, a.code, a.description)

    values (b.id, b.code, b.description);

3 rows merged.

SQL>

select * from t1;

        ID CODE   DESCRIPTION

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

         1 ONE    Description for ONE

         2 TWO    Description for TWO

         3 THREE  Description for THREE

         4 FOUR   Description for FOUR

         5 FIVE   Description for FIVE

         6 SIX    Description for SIX

6 rows selected.

SQL>

rollback;

Source: oracle-base.com

Continue reading

Using JSON documents and don’t know what you’re looking for? 23c Search Indexes to the rescue

Introduction

Oracle has powerful capabilities for handling JSON. It also has flexible capabilities for full-text searching, like keyword search, phrase search, or proximity search. We're going to see how these capabilities meet in the JSON search index to provide the powerful functionality of full text search in an optimized manner for all your JSON documents.

What are text indexes?

In its basic form, a text index allows you to create a word-based index on a textual field in the database. It is then possible to search the table for fields containing particular words or phrases.

Let's say we have a table emp which contains some employee details:

create table emp(name varchar2(40), salary number, qualifications varchar2(200));

insert into emp values ('John', 1500, 'PhD in physics, Msc Math');

commit;

In 23c I would create a text index on that table using:

create search index emp_qual on emp(qualifications);

In earlier versions I would do:

create index emp_qual on emp(qualifications) indextype is ctxsys.context;

After having created my text index, I can search it using a CONTAINS query, such as:

select * from emp where contains(qualifications, 'physics') > 0;

That’s the simplest example of how to use a text index. That search is looking for the word “physics” somewhere in the qualifications field. The query could be much more complex – we’ll see a few more advanced examples when we look at JSON_TEXTCONTAINS in a moment.

What’s special about a Search Index?

On the face of it, we might think that we could just do a substring search on the qualifications field to look for the word ‘physics’. But substring searches are quite limited

• They can’t use an index
• They are case-sensitive
• Punctuation and spacing will affect the searches
• We can’t do  oolean searches such as AND, OR or NOT within the substring search itself, we’d have to do multiple searches which would get very inefficient

Having a search index means that each word in our text has its own index entry, so we are able to rapidly find references to individual words or phrases, or boolean combinations of words, without having to scan the original text.

What are JSON indexes?

Now let's say our employee data was stored as JSON, and we need to search using salary ranges. For small numbers of rows, a full scan of the JSON will be plenty fast enough, especially if we're using the binary JSON datatype in 21c/23c.

create table empj(empdata json);

insert into empj values ('{ "name":"john", "salary":1500, "qualifications": "PhD in physics, Msc Math"}');

insert into empj values ('{ "name":"bobby", "salary":900,

"qualifications": "Msc Math", "hobbies": "physics" }');

commit;

select * from empj e where e.empdata.salary.number() > 1000;

But what if we have a very large number of rows in our JSON table? In that case, we'll want to create an index on the salary element of the JSON:

create index emp_salary on empj e(e.empdata.salary.number());

But … what if we don't know what fields we need to search? Or we do know, but we want to do word-based searches on those fields? Ultimately, JSON data is flexible and does not have a static, fits-all schema definition like our relational table before.

Both of these problems are solved with a JSON Search Index. A JSON search index indexes all the data in a JSON object, without having to pre-declare any data types or even to know the attributes in your documents. It’s all JSON. Not only does it index your JSON documents in the most efficient manner, it provides full text search capabilities with this index.

OK ... I need a JSON search index. How do I create one?

Creating a JSON search index is as easy as this:

create search index emp_search on empj(empdata) for json;

With this JSON search index you killed two birds with one stone: First, you have an index for a ‘normal’ JSON search like our salary example before, as seen in the explain plan:

explain plan for select * from empj e where e.empdata.salary.number() > 1000;

select * from table(dbms_xplan.display);

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

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |            |     1 |  4114 |     4   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| EMPJ       |     1 |  4114 |     4   (0)| 00:00:01 |

|*  2 |   DOMAIN INDEX              | EMP_SEARCH |       |       |     4   (0)| 00:00:01 |

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

The line with Id=2 shows that we are using a 'domain index' for the search - that's our JSON search index being used to speed up access to the2 salary field - even though we didn't specify that we wanted to specifically index that field.

Second, your JSON search index also allows for full-text search on all string fields within the JSON. We would do that using the JSON_TEXTCONTAINS operator, which takes the column name, a JSON path for where to search, and a full text search expression as arguments. For example:

select e.empdata.name from empj e

where json_textcontains(empdata, '$', 'physics');

NAME

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

"john"

"bobby"

The '$' in there represents the root, or base of the JSON document (it's a "JSON path expression" if you want to look up more detail) and means we should search for the word 'physics' anywhere in the JSON document. If we wanted to search a particular part of the document, say "qualifications", we could express that as a path instead:

select e.empdata.name from empj e

where json_textcontains(empdata, '$.qualifications', 'physics');

NAME

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

"john"

That is the power of JSON Search Indexes. Depending on your ‘search space’ – the whole document or a specific attribute – the JSON search index is speeding up your request.

We could have done that with a simple JSON equality operator and some wildcards, but it would not have been fast on a large collection. And we certainly couldn't have done more complex searches like:

select e.empdata.name, e.empdata.qualifications from empj e

where json_textcontains(empdata, '$.qualifications', 'physics AND msc math');

NAME      QUALIFICATIONS

_________ _____________________________

"john"    "PhD in physics, Msc Math"

Meaning "the qualifications field contains the single word "physics" and the contiguous phrase "msc math". Nor could we do a 'fuzzy' search such as:

select e.empdata.name, e.empdata.qualifications from empj e

where json_textcontains(empdata, '$.qualifications', 'fuzzy(phisiks'));

NAME      QUALIFICATIONS

_________ _____________________________

"john"    "PhD in physics, Msc Math"

Very useful if you're unsure of your spelling, or that of whoever created the JSON in the first place. The JSON search index comes to the rescue and you’ll find what you’re looking for!

We can even do relevance ranking in 23c with JSON_TEXTCONTAINS. Let's add a couple more JSON documents to our table:

insert into empj values ('{ "name":"bill", "salary":1000, "qualifications": "Math professor"}');

insert into empj values ('{ "name":"mike", "salary":2000, "qualifications": "Physics student"}');

commit;

We'll need to issue a COMMIT and wait two or three seconds for our index to get updated, then we can run a query with a SCORE. Note the extra final argument to JSON_TEXTCONTAINS - that's a number which associates the SCORE() function with this particular JSON_TEXTCONTAINS.

select score(1), e.empdata.name, e.empdata.qualifications from empj e

where json_textcontains(empdata, '$.qualifications', 'math ACCUM physics', 1)

order by score(1) desc;

The ACCUM operator guarantees that if both terms are found, the record will score higher than if only one term is found. There can be more than two terms, and the higher number of terms found will always score higher. So the query above gives us:

SCORE(1)    NAME       QUALIFICATIONS

___________ __________ _____________________________

         52             "john"      "PhD in physics, Msc Math"

          2              "bobby"    "Msc Math"

          2              "bill"         "Math professor"

          2              "mike"     "Physics student"

The absolute value of the score is not that important - it's not something we'd generally show to the user. Instead it is used, as here, to order the results according to their relevance. You find what you are looking for easily, with the help of JSON Search Indexes.

Source: oracle.com

Continue reading

Third Quarterly Update on Oracle Graph (2023)

The graph features of Oracle Database enables developers to store and navigate relationships between entities. Oracle Graph Server and Client enables developers, analysts, and data scientists to use graphs within Oracle Database, while Graph Studio in Oracle Autonomous Database removes barriers to entry by automating setup and management, automating graph creation, and by providing step-by-step examples for getting started.

The last quarterly update on Oracle Graph, announced the availability of Oracle Graph Server and Client 23.2. That release included changes to the graph visualization app, updates to PGQL, and integrations of Oracle Graph with other services. The graph visualization app was updated to support SQL property graphs, which are available through Oracle Database 23c Free – Developer Release.   SQL Property Graphs enable creation and query of graphs using new syntax in the SQL 2023 standard. It also included additional functionality through PGQL, and updates that closely align PGQL with the SQL standard. Lastly, there were enhancements to the integrations with PyPi, SQL Developer and OCI Data Science.

Oracle Graph Server and Client 23.3 is now available for download for use with databases in the Cloud (OCI Marketplace image is available) and for databases on-premises. This release includes a number of new features, including the simplified install and use of the graph visualization tool, updates to PGQL, and RDF feature enhancements. The graph visualization REST API was also updated to streamline authentication and run PGQL queries through the JSON body, rather than as an encoded URL. More information on the REST API will be available in a future post.

Simplified Installation and Use of the Graph Visualization Tool

Configuration is now simplified, and there is also a single login screen for visualizing graphs in the database and in the graph server.

Once authenticated, on Oracle Database 19c, you will see separate tabs for querying graphs in the Graph Server and PGQL Property Graphs in the database. On Oracle Database 23c Free – Developer Release, you will see separate tabs for the  Graph Server, PGQL Property Graphs in database and SQL Property Graphs in database. This simplifies the transition between running graph queries in the database and in the Graph Server.

PGQL Updates

The newest updates enhanced support for LATERAL subqueries. LATERAL subqueries allow for passing the output rows of one query into another by projecting any number of columns to be used by the outer query. This release includes the ability to use LATERAL subqueries when running PGQL queries in the database. Lateral subqueries can now be mixed with any number of MATCH clauses per FROM clause and is supported inside EXISTS / scalar subqueries.

This release also adds support for LATERAL subqueries in the graph visualization tool, so you can visualize the results of your LATERAL subqueries.

RDF Feature Updates

This release adds support for creating a data source with credentials using RDF Server and adds support for GeoJSON. Creating a data source with credentials will allow you to establish secure connections to your data, ensuring efficient access to these resources.

With the GeoJSON integration, you will be able to visualize and analyze location-based information from your RDF data.

Source: oracle.com

Continue reading

Integrating Helidon and WebLogic Microservices with Oracle MicroTx

Introduction

More and more businesses are adopting microservices. This may be for green field developments where everything can start as a microservice. Or it may be building microservices that need to interact with or leverage existing, typically monolithic applications. Few companies can afford to build completely net new microservice based applications to replace their existing applications. The best alternative is to create new features using microservices and capitalize on the existing application until it can be replaced, in whole or piecemeal. 

In this post, I’ll cover a use case where an existing money management application already exists built using Oracle WebLogic Server. While this example will use JAX-RS and JPA, JDBC as well as EJB or JMS based applications can also be integrated by providing a JAX-RS interface to those applications.

Here is a diagram of the sample application that will be covered in this post:

Understanding the Sample

The basic scenario is an existing WebLogic Server JAX-RS JPA based web application that manages account balances and can have funds deposited to or withdrawn from that balance. A new application is being created using microservices that needs to integrate with this existing WebLogic application. These new microservices are written in Java using Helidon. They provide deposit and withdrawal services.

The issue now is how can one ensure that operations such as transferring funds, to or from, the existing application and the new microservices occur atomically? Fundamentally this is a dual write problem where the write to the source account and the destination account must both succeed or both fail. This is where distributed transactions come into the picture. Normally in WebLogic applications, the internal WebLogic Java Transaction Service is used. It provides XA distributed transaction support across multiple resource managers as well as across WebLogic applications deployed in other WebLogic servers. It can also support transactions spread across other application servers when using SOAP based web services with WS-AtomicTransaction and associated protocols. However, managing transactions that span other technologies, such as Helidon or Node.js or when using REST based services requires another solution.

This is where the Oracle Transaction Manager for Microservices (MicroTx) comes into the picture. MicroTx provides an external transaction coordinator for REST based services. Using MicroTx, the existing WebLogic application with some minor configuration change can be integrated with the new Helidon based microservices while ensuring transactional integrity across the participating systems. 

With MicroTx, a microservice can start a transaction and call other microservices that should be included in the transaction. This sample application shows one Helidon microservice that is acting as a sort of bank teller. The teller microservice allows a user to request that funds be transferred from one account to another account. However, those accounts may be held in different systems using different frameworks or languages. In this case, one account is provided by the WebLogic application, and the other is a new microservice running in Helidon.

For a JPA based application like the WebLogic sample application for MicroTx, no code changes are required, just some additional configuration to include the MicroTx client library and define a single property. 

This diagram shows how filters are used to propagate transaction context from one microservice to another.

Once these configuration changes are made, the application can participate in distributed transactions that are managed by MicroTx.

For the new Helidon teller microservice, which we call the initiator as it’s the one initiating the transaction, all that is required is to include the MicroTx client library and add the @Transactional annotation:

@Transactional(Transactional.TxType.REQUIRED)

on the method handling the transfer function. This annotation tells MicroTx that this method must be part of a transaction and begins a new transaction if one is not already present. In addition, if the method returns successfully, then the transaction is automatically committed by MicroTx. If the method generates an exception, the transaction is automatically rolled-back.

When the teller microservice makes a REST call to withdraw money from one of the participants, the MicroTx client library filters add headers to the outbound REST request to indicate to the participant that it should participate in the transaction. The incoming filters in the participant then enlist the participant in the transaction by calling the MicroTx coordinator and establish a transaction context in the participant.

Once the teller microservice has decided the transaction should be committed, it returns success which causes the MicroTx library to ask for the transaction to be committed by calling the transaction coordinator. The transaction coordination service then calls back to each of the enlisted participants to ask them to prepare. If that succeeds, it then asks all the participants to commit. If one or more of the participants cannot prepare, or the initiator decides to abort the transaction, the transaction will be rolled-back. Otherwise, the transaction will be committed. The initiator can abort the transaction by throwing an exception.

This overall flow is shown in this diagram:

In the transaction flow depicted above, MicroTx manages the details of distributed transaction processing so that the application developer does not have to. The interactions in green are completely handled by the MicroTx library. From a developer point of view, MicroTx makes this easy. Use an annotation within Helidon, and configuration within WebLogic Server, and MicroTX will manage the distributed transactions for you.

Note that this is a bit different than the way XA transactions are normally handled. In the typical XA transaction manager implementation, the transaction manager has its own connections to the resource manager. Which means the transaction manager needs its own credentials as well as the appropriate client library for the resource manager. To allow the MicroTx transaction manager to support any resource manager, it proxies its requests to the participant, which already has a connection to the resource manager. This makes the MicroTx transaction coordinator largely resource manager agnostic.

Source: oracle.com

Continue reading

Introducing the Oracle Database Error Help Portal

In a continuous effort to increase user productivity, we are pleased to introduce the new Error Help Portal for Oracle Database, which will help users gain faster and improved insights into Oracle Database errors.

You can easily access the new portal via https://docs.oracle.com/error-help/db/ and the Oracle Database Documentation landing page:

Normalized URLs

The Error Help Portal utilizes a normalized URL scheme, meaning that each error message can be quickly accessed by typing the correct error message number into the URL itself, for example:

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

Users will also note that starting with Oracle Database 23c, tools will automatically generate these URLs for errors encountered, making the error message documentation one click away.

Improved overview and reader experience

Unlike the error message book in previous releases of the Oracle Database Documentation, the portal presents each error message on a single web page, providing a better reader/user experience. On each page, users can quickly identify the Oracle Database release that the error message is applicable, when the page was last updated, the error message itself, and its Cause and recommended Action for the user to take.

Previous Releases

Users may note that some error message text and the cause and action appear different in the Error Help Portal than those raised by the current version of Oracle Database. As part of Oracle’s continuous initiative to further enhance user productivity, Oracle also continues to improve the error messages raised by Oracle Database to be more meaningful, actionable, and clear to the end user.

To avoid potential confusion for the end user, the Error Help Portal still shows the error message text as it would appear for the supported Previous Releases:

Error message text parameters

Another improvement overall is that variable values inside error message texts, sometimes referred to as the error message parameters, are also documented, helping users to better understand what the values produced by error messages signify.

Users will notice that some error message texts shown in the portal contain words in italics indicating the use of such error message parameters and the bulleted list below the error message provide further explanation for each of these:

Additional information

For error messages that require additional information or context to the user, a new Additional Information section below the Cause and Action will be shown:

For the savvy user

Savvy users may have already spotted that the normalized URL can be used with the site search feature of popular browsers like Chrome, hence providing a mechanism where you can type in only a shortcut and the error message number in the browser URL field to find the documentation page:

Here is how you do that for Chrome:

1. Head to Settings --> Search engine --> Manage search engines and site search

2. Scroll down to Site search and click the Add button.

Add a new Search Engine with your favorite name, your shortcut (the screenshot above used "err") and paste the following URL into the URL field: "https://docs.oracle.com/error-help/db/%s/"

3. Click Save and close the Settings page.

From now on, when you type "err" (or the shortcut character(s) you have chosen) in the URL bar and click Tab, any error numbers that you type into the URL field will be added to the Error Help Portal URL and bring you straight to the Error Message page.

Feedback wanted!

The Oracle Database Documentation team is actively looking for feedback. If you found something missing, wrong, or something that could be improved, please look for the "Thumbs Up" icon at the bottom of the page, hover over it and click the "Thumbs Up" or "Thumbs Down" icon.

Either button will open a new window with a text box where you can provide further information to the Oracle Database Documentation team. Please take the extra time and fill out the box, as it will help us better understand how to improve the error message and Error Help Portal. We already thank you for your feedback!

The road ahead

Oracle is actively working on enhancing error messages and the Error Help Portal. This will remain a continuous task throughout database releases. Users may find that some error messages still need to reflect all the new Error Help Portal features. Please be patient and use the feedback mechanism to help us prioritize. We strive to provide the best possible experience, which takes time and effort.

Our goal is to expand the portal to other Oracle products too, which is why you see a product name already today inside the normalized URL.

We hope that the new Error Help Portal will aid users in quickly finding the reasons and solutions for the errors they encountered and that it will help them become even more productive.

Source: oracle.com

Continue reading