Oracle REST Data Services (ORDS) : AutoREST of JSON-Relational Duality Views

This article gives an overview of the AutoREST functionality of Oracle REST Data Services (ORDS) against JSON-relational duality views in Oracle 23c.

◉ Create a Test Database User

We need a new database user for our testing.

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

drop user if exists testuser2 cascade;

create user testuser2 identified by testuser2

  default tablespace users quota unlimited on users;

  

grant connect, resource to testuser2;

Create and populate a copy of the EMP and DEPT tables.

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

drop table if exists emp purge;

drop table if exists dept purge;

create table dept (

  deptno number(2) constraint pk_dept primary key,

  dname varchar2(14),

  loc varchar2(13)

) ;

create table emp (

  empno number(4) constraint pk_emp primary key,

  ename varchar2(10),

  job varchar2(9),

  mgr number(4),

  hiredate date,

  sal number(7,2),

  comm number(7,2),

  deptno number(2) constraint fk_deptno references dept

);

create index emp_dept_fk_i on emp(deptno);

insert into dept values (10,'ACCOUNTING','NEW YORK');

insert into dept values (20,'RESEARCH','DALLAS');

insert into dept values (30,'SALES','CHICAGO');

insert into dept values (40,'OPERATIONS','BOSTON');

insert into emp values (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,null,20);

insert into emp values (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);

insert into emp values (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);

insert into emp values (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,null,20);

insert into emp values (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);

insert into emp values (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,null,30);

insert into emp values (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,null,10);

insert into emp values (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,null,20);

insert into emp values (7839,'KING','PRESIDENT',null,to_date('17-11-1981','dd-mm-yyyy'),5000,null,10);

insert into emp values (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);

insert into emp values (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,null,20);

insert into emp values (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,null,30);

insert into emp values (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,null,20);

insert into emp values (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,null,10);

commit;

Create a JSON-relational duality view against the base tables. You can get more information about JSON-relational duality views here. We've purposely excluded some of the optional columns to make things a little simpler.

drop view if exists department_dv;

create json relational duality view department_dv as

select json {'departmentNumber' : d.deptno,

             'departmentName'   : d.dname,

             'location'         : d.loc,

             'employees' :

               [ select json {'employeeNumber' : e.empno,

                              'employeeName'   : e.ename,

                              'job'            : e.job,

                              'salary'         : e.sal}

                 from   emp e with insert update delete

                 where  d.deptno = e.deptno ]}

from dept d with insert update delete;

Notice the view references the departments table, but includes a list of all employees in the department. So this maps to a real-world object, not just a single table.

◉ Enable ORDS and AutoREST

Enable REST web services for the test schema. We use any unique and legal URL mapping pattern for the schema, so we don't expose the schema name. In this case we use "hr" as the schema alias.

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

begin

  ords.enable_schema(

    p_enabled             => TRUE,

    p_schema              => 'TESTUSER2',

    p_url_mapping_type    => 'BASE_PATH',

    p_url_mapping_pattern => 'hr',

    p_auto_rest_auth      => FALSE

  );

    

  commit;

end;

/

Web services from the schema can now be referenced using the following base URL.

http://localhost:8080/ords/hr/

The final step is to enable AutoREST for the JSON-relational duality view. This is done the same way as AutoREST enabling any other view.

begin

  ords.enable_object (

    p_enabled      => TRUE, -- Default  { TRUE | FALSE }

    p_schema       => 'TESTUSER2',

    p_object       => 'DEPARTMENT_DV',

    p_object_type  => 'VIEW', -- Default  { TABLE | VIEW }

    p_object_alias => 'departments'

  );

    

  commit;

end;

/

Notice the object is called DEPARTMENT_DV, but we want the web service to refer to it as "departments", hence the object alias. To disable AutoREST repeat the call with the P_ENABLED parameter set to FALSE.

We are now ready to start.

◉ GET Web Services (READ)

By default browsers use the GET method for HTTP calls, so the following URLs can be called from a browser URL bar.

The following URLs return JSON documents containing metadata about the objects in the test schema the specified object structure respectively.

Available Objects : http://localhost:8080/ords/hr/metadata-catalog/

Object Description: http://localhost:8080/ords/hr/metadata-catalog/departments/

There are a variety of ways to query data from an AutoREST enabled table or view. The following URL returns all the data from the DEPARTMENT_DV view. Remember, the object alias was set to "departments".

http://localhost:8080/ords/hr/departments/

The data from an individual row is returned using the primary key value. A comma-separated list is used for concatenated keys.

http://localhost:8080/ords/hr/departments/10

It's possible to page through data using the offset and limit parameters. The following URL returns a page of 2 rows of data from the DEPARTMENT_DV view, starting at row 3.

http://localhost:8080/ords/hr/departments/?offset=2&limit=2

There are a variety of operators that can be used to filter the data returned from the object. Depending on you client, you may need to encode parts of the URI.

# departmentName = 'SALES'

Normal : http://localhost:8080/ords/hr/departments/?q={"items.departmentName":"SALES"}

Encoded: http://localhost:8080/ords/hr/departments/?q=%7B%22departmentName%22:%22SALES%22%7D

# departmentNumber >= 20

Normal : http://localhost:8080/ords/hr/departments/?q={"departmentNumber":{"$gte":30}}

Encoded: http://localhost:8080/ords/hr/departments/?q=%7B%22departmentNumber%22:%7B%22$gte%22:30%7D%7D

# departmentName = 'SALES' AND departmentNumber >= 30

Normal : http://localhost:8080/ords/hr/departments/?q={"departmentName":"SALES","departmentNumber":{"$gte":30}}

Encoded: http://localhost:8080/ords/hr/departments/?q=%7B%22departmentName%22:%22SALES%22,%22departmentNumber%22:%7B%22$gte%22:30%7D%7D

◉ POST Web Services (INSERT)

New records are created using the POST method. The URL, method, header and payload necessary to do this are displayed below.

URL        : http://localhost:8080/ords/hr/departments/

Method     : POST

Header     : Content-Type: application/json

Raw Payload:

{

  "departmentNumber" : 50,

  "departmentName" : "DBA",

  "location" : "BIRMINGHAM",

  "employees" : [

    {

      "employeeNumber" : 9999,

      "employeeName" : "HALL",

      "job" : "CLERK",

      "salary" : 500

    }

  ]

}

If the payload is placed in a file called "/tmp/insert-payload.json", the following "curl" command will add a department via the DEPARTMENT_DV view.

$ curl -i -X POST --data-binary @/tmp/insert-payload.json -H "Content-Type: application/json" http://localhost:8080/ords/hr/departments/

HTTP/1.1 201

Content-Location: http://localhost:8080/ords/hr/departments/50

ETag: "77052B06E84B60749E410D5C2BA797DF"

Location: http://localhost:8080/ords/hr/departments/50

Cache-Control: max-age=0

Expires: Wed, 12 Apr 2023 09:55:13 GMT

Content-Type: application/json

Transfer-Encoding: chunked

Date: Wed, 12 Apr 2023 09:55:13 GMT

{"departmentNumber":50,"departmentName":"DBA","location":"BIRMINGHAM","employees":[{"employeeNumber":9999,"employeeName":"HALL","job":"CLERK","salary":500}],"_metadata":{"etag":"77052B06E84B60749E410D5C2BA797DF","asof":"00000000002710B9"},"links":[{"rel":"self","href":"http://localhost:8080/ords/hr/departments/50"},{"rel":"describedby","href":"http://localhost:8080/ords/hr/metadata-catalog/departments/item"},{"rel":"collection","href":"http://localhost:8080/ords/hr/departments/"}]}$

In addition to the web service output, we can see rows have been created in the base tables.

select * from dept where deptno = 50;

    DEPTNO DNAME          LOC

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

        50 DBA            BIRMINGHAM

SQL>

select * from emp where empno = 9999;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      9999 HALL       CLERK                                 500                    50

SQL>

◉ PUT Web Services (UPDATE)

Records are updated, or inserted if they are missing, using the PUT method. The URL, method, header and payload necessary to do this are displayed below.

URL        : http://localhost:8080/ords/hr/departments/50

Method     : PUT

Header     : Content-Type: application/json

Raw Payload: 

{

  "departmentNumber" : 40,

  "departmentName" : "OPERATIONS",

  "location" : "BOSTON",

  "employees" : [

    {

      "employeeNumber" : 9998,

      "employeeName" : "WOOD",

      "job" : "CLERK",

      "salary" : 500

    }

  ]

}

Notice the row to be updated is determined by the URL, in a similar way to a GET call using the primary key. Excluding the PK columns, any columns not specified in the payload are set to null.

If the payload is placed in a file called "/tmp/update-payload.json", the following "curl" command will add a new employee to department 40 via the DEPARTMENT_DV view.

$ curl -i -X PUT --data-binary @/tmp/update-payload.json -H "Content-Type: application/json" http://localhost:8080/ords/hr/departments/40

HTTP/1.1 200

Content-Location: http://localhost:8080/ords/hr/departments/40

ETag: "AAC7DB6EB25FAB98572C2855225DE82B"

Cache-Control: max-age=0

Expires: Wed, 12 Apr 2023 10:06:51 GMT

Content-Type: application/json

Transfer-Encoding: chunked

Date: Wed, 12 Apr 2023 10:06:51 GMT

{"departmentNumber":40,"departmentName":"OPERATIONS","location":"BOSTON","employees":[{"employeeNumber":9998,"employeeName":"WOOD","job":"CLERK","salary":500}],"_metadata":{"etag":"AAC7DB6EB25FAB98572C2855225DE82B","asof":"00000000002713E6"},"links":[{"rel":"self","href":"http://localhost:8080/ords/hr/departments/40"},{"rel":"describedby","href":"http://localhost:8080/ords/hr/metadata-catalog/departments/item"},{"rel":"collection","href":"http://localhost:8080/ords/hr/departments/"}]}$

In addition to the web service output, we can see the row has been updated by querying the table.

select * from emp where deptno = 40;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      9998 WOOD       CLERK                                 500                    40

SQL>

◉ DELETE Web Services (DELETE)

Records are deleted using the DELETE method. The URL and method necessary to do this are displayed below.

URL        : http://localhost:8080/ords/hr/departments/50

Method     : DELETE

The following "curl" command will delete a row from the EMP table. The URL is an encoded version of the one shown above.

$ curl -i -X DELETE  http://localhost:8080/ords/hr/departments/50

HTTP/1.1 200

X-Frame-Options: SAMEORIGIN

X-Content-Type-Options: nosniff

X-XSS-Protection: 1; mode=block

Cache-Control: max-age=0

Expires: Wed, 12 Apr 2023 10:11:09 GMT

Content-Type: application/json

Transfer-Encoding: chunked

Date: Wed, 12 Apr 2023 10:11:09 GMT

{"rowsDeleted":1}

$

In addition to the web service output, we can see the row has been deleted by querying the table.

select * from dept where deptno = 50;

no rows selected

SQL>

select * from emp where deptno = 50;

no rows selected

SQL>

◉ Managing State

In all the previous operations we've ignored state, assuming the data is not changing. In reality it's possible the data has changed between our service calls. JSON-relational duality views give us a way to manage the state, providing us with an "etag" which is effectively a version we can use for optimistic locking. The following example shows this.

We delete department "50" to give us a clean starting point.

delete from emp where deptno = 50;

delete from dept where deptno = 50;

commit;

We create a new department using a REST call as we did previously.

URL        : http://localhost:8080/ords/hr/departments/

Method     : POST

Header     : Content-Type: application/json

Raw Payload:

{

  "departmentNumber" : 50,

  "departmentName" : "DBA",

  "location" : "BIRMINGHAM",

  "employees" : [

    {

      "employeeNumber" : 9999,

      "employeeName" : "HALL",

      "job" : "CLERK",

      "salary" : 500

    }

  ]

}

If the payload is placed in a file called "/tmp/insert-payload.json", the following "curl" command will add a department via the DEPARTMENT_DV view.

$ curl -i -X POST --data-binary @/tmp/insert-payload.json -H "Content-Type: application/json" http://localhost:8080/ords/hr/departments/

HTTP/1.1 201

Content-Location: http://localhost:8080/ords/hr/departments/50

ETag: "77052B06E84B60749E410D5C2BA797DF"

Location: http://localhost:8080/ords/hr/departments/50

Cache-Control: max-age=0

Expires: Wed, 12 Apr 2023 12:51:57 GMT

Content-Type: application/json

Transfer-Encoding: chunked

Date: Wed, 12 Apr 2023 12:51:57 GMT

{"departmentNumber":50,"departmentName":"DBA","location":"BIRMINGHAM","employees":[{"employeeNumber":9999,"employeeName":"HALL","job":"CLERK","salary":500}],"_metadata":{"etag":"77052B06E84B60749E410D5C2BA797DF","asof":"0000000000274448"},"links":[{"rel":"self","href":"http://localhost:8080/ords/hr/departments/50"},{"rel":"describedby","href":"http://localhost:8080/ords/hr/metadata-catalog/departments/item"},{"rel":"collection","href":"http://localhost:8080/ords/hr/departments/"}]}

$

Notice the resulting "etag" value of "77052B06E84B60749E410D5C2BA797DF".

We add another employee to department "50" using a conventional insert. This simulates the data changing between the last time we checked the document.

insert into emp values (9997,'WOOD','CLERK',null,null,1300,null,50);

commit;

Now we attempt to update the department, passing the original "etag" value in the "_metadata" tag.

URL        : http://localhost:8080/ords/hr/departments/50

Method     : POST

Header     : Content-Type: application/json

Raw Payload:

{

  "_metadata" : {"etag" : "77052B06E84B60749E410D5C2BA797DF"},

  "departmentNumber" : 50,

  "departmentName" : "DBA",

  "location" : "BIRMINGHAM",

  "employees" : [

    {

      "employeeNumber" : 9999,

      "employeeName" : "HALL",

      "job" : "SALESMAN",

      "salary" : 1000

    }

  ]

}

If the payload is placed in a file called "/tmp/update-payload.json", the following "curl" command will update the employee details in department 50 via the DEPARTMENT_DV view.

$ curl -i -X PUT --data-binary @/tmp/update-payload.json -H "Content-Type: application/json" http://localhost:8080/ords/hr/departments/50

HTTP/1.1 412

Cache-Control: max-age=0

Expires: Wed, 12 Apr 2023 12:58:40 GMT

Content-Type: application/problem+json

Content-Length: 204

Date: Wed, 12 Apr 2023 12:58:40 GMT

{

    "code": "PredconditionFailed",

    "message": "Predcondition Failed",

    "type": "tag:oracle.com,2020:error/PredconditionFailed",

    "instance": "tag:oracle.com,2020:ecid/qOqFfmt7AEbuGbIfns-vKg"

}

$

The data change has caused the "etag" value to change, so the update caused a "PredconditionFailed" error. In order to proceed, we would have to re-query the data to get the new "etag" value, then try again.

◉ Batch Load

In addition to basic DML and queries, it's possible to upload batches of data using AutoREST.

Make sure the additional departments and employees are removed.

delete from emp where deptno > 40;

delete from dept where deptno > 40;

commit;

The URL, method, header and payload necessary to do this are displayed below. Notice the payload is a JSON array of documents to load.

URL        : http://localhost:8080/ords/hr/departments/batchload"

Method     : POST

Header     : Content-Type : application/json

Raw Payload:

[

  {

    "departmentNumber" : 60,

    "departmentName" : "DEVELOPER",

    "location" : "LONDON",

    "employees" : [

      {

        "employeeNumber" : 9997,

        "employeeName" : "SMITH",

        "job" : "MANAGER",

        "salary" : 3000

      }

    ]

  },

  {

    "departmentNumber" : 70,

    "departmentName" : "PROJECTS",

    "location" : "LONDON",

    "employees" : [

      {

        "employeeNumber" : 9996,

        "employeeName" : "JONES",

        "job" : "MANAGER",

        "salary" : 3500

      }

    ]

  },

  {

    "departmentNumber" : 80,

    "departmentName" : "MAINTENANCE",

    "location" : "LONDON",

    "employees" : [

      {

        "employeeNumber" : 9995,

        "employeeName" : "DAVIS",

        "job" : "MAMAGER",

        "salary" : 2500

      }

    ]

  }

]

If the payload is placed in a file called "/tmp/data.json", the following "curl" command will perform a batch load into the EMP table.

$ curl -i -X POST --data-binary @/tmp/data.json -H "Content-Type: application/json" http://localhost:8080/ords/hr/departments/batchload

HTTP/1.1 200

X-Frame-Options: SAMEORIGIN

X-Content-Type-Options: nosniff

X-XSS-Protection: 1; mode=block

Cache-Control: max-age=0

Expires: Wed, 12 Apr 2023 14:58:58 GMT

Content-Type: text/plain

Transfer-Encoding: chunked

Date: Wed, 12 Apr 2023 14:58:58 GMT

#INFO Number of rows processed: 3

#INFO Number of rows in error: 0

#INFO Last row processed in final committed batch: 3

SUCCESS: Processed without errors

$

In addition to the web service output, we can see the rows have been loaded by querying the table.

select * from dept where deptno > 40;

  DEPTNO DNAME          LOC

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

        70 PROJECTS       LONDON

        80 MAINTENANCE    LONDON

        60 DEVELOPER      LONDON

SQL>

select * from emp where deptno > 40;

  EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      9996 JONES      MANAGER                              3500                    70

      9995 DAVIS      MAMAGER                              2500                    80

      9997 SMITH      MANAGER                              3000                    60

SQL>

The parameters that can be used to influence the batch load are documented here.

◉ Display Enabled Objects

The USER_ORDS_ENABLED_OBJECTS view displays enabled objects.

set linesize 200

column parsing_schema format a20

column parsing_object format a20

column object_alias format a20

column type format a20

column status format a10

select parsing_schema,

       parsing_object,

       object_alias,

       type,

       status

from   user_ords_enabled_objects

order by 1, 2;

◉ Thoughts

My biggest issue with AutoREST in the past was it was table/view centric. Very few real world units of work map directly to one table or view. As a result I often spurned AutoREST in favour of manually coding APIs in PL/SQL, and presenting them as REST web services.

With JSON-relational duality views we can easily map real world objects to multiple database tables. The integration between ORDS and JSON-relational duality views make AutoREST a lot more appealing.

Source: oracle-base.com

Continue reading

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