Demonstration of Oracle SQL compatible functions and syntax
BigAnimal lets you run Oracle SQL queries in the cloud using EDB Postgres Advanced Server. This demonstration shows two Oracle SQL-syntax queries running unmodified on a BigAnimal test cluster, populated with the Chinook sample database.
Watch the video, or load up psql and follow along.
Connecting to the demo cluster with psql
You can use any recent version of psql to connect to EDB Postgres Advanced Server. The version that ships with Advanced Server has a few nice SQL*Plus compatibility features (with more availability in EDB*Plus). The queries and commands used here work the same in either version of psql. For convenience, these examples use the version of psql available in the EDB Postgres Advanced Server container image used by Cloud Native PostgreSQL and internally by BigAnimal. You can follow along by installing Docker and running:
Note
If you prefer a graphical tool to execute Oracle-syntax-compatible queries or run Oracle PL/SQL-compatible code, we recommend pgAdmin.
The connection string for this demo's EDB Postgres Advanced Server cluster looks like this:
In case you're unfamiliar with PostgreSQL connection URIs:
demo
is the user role you're connecting as. This is a user set up with select privileges on the database.password
is the password for this user.Passwords in connection strings.
This example illustrates a complete connection URL, including the password. This is fine for a demonstration and might also be acceptable for application configuration if access to the configuration is limited. Avoid this practice for admin, superuser, or other roles used interactively. psql prompts for a password if none is supplied.
p-vmpc7c40fm.pg.biganimal.io
is the host name for the EDB Postgres Advanced Server cluster on BigAnimal that you're connecting to.5432
is the usual PostgreSQL port number.chinook
is the name of the database.sslmode=require
ensures that you establish a secure connection.
With that in hand, launch psql:
Here's the schema:
An employee table is defined as follows:
This table has a reportsto
field. That means this is a hierarchical reporting structure, with some employees reporting to other employees who might in turn report to still other employees.
Demo #1: Exposing an organization hierarchy with CONNECT BY
Construct a hierarchical query to expose this chain of command.
Modern SQL can use a recursive CTE for this, as those are widely supported. But Oracle has, for decades, supported an alternative mechanism for querying hierarchy in the form of CONNECT BY
, as shown in the following:
Here, the CONNECT BY
and the LISTAGG
functions are used in a subquery to generate the chain of command for each employee: who they report to, who that person reports to, and so on.
The LISTAGG()
function was introduced in Oracle 11g Release 2. Very few database systems support it. PostgreSQL does support string_agg()
. In the previous example, that could be used as a drop-in replacement:
But the semantics of the two functions are different for even slightly less-trivial uses, specifically when using the grouping construct, as shown in the next demonstration.
Demo #2: Group concatenation with LISTAGG
As shown in the first demonstration, this database has album and track tables containing metadata on digital recordings. You can use some analytic functions, including LISTAGG
, to put together a report on average track storage requirements for albums with "baby" in the title.
Trying to eplace LISTAGG
with string_agg
in this example fails. The expression syntax for string_agg
is different.
This isn't difficult to correct, but it requires restructuring the query to replace the grouping construct. Such work can quickly accumulate errors. Fortunately, EDB Postgres Advanced Server supports LISTAGG
in addition to string_agg
,
so this query doesn't need to change when migrating from Oracle.
Compatibility preserves the value of your existing work
In both of the examples shown here, you probably would not use the functions and syntax demonstrated for new work. There are better, more familiar or at least more widely available equivalents provided natively by PostgreSQL and many other databases. But by supporting them, EDB Advanced Server lets you reuse existing logic with minimal modification, allowing you to focus your time and expertise on solving new problems.
Try it on your own cluster: export and import
If you want to try these examples on your own BigAnimal cluster, follow these instructions to import the example database.
Connect to your EDB Postgres Advanced Server cluster as edb_admin using psql. You can get the command for this from your cluster's Overview tab. It looks like this, where
<YOUR CLUSTER HOSTNAME>
is specific to your cluster:You're prompted for the password you specified when creating your cluster.
Create a new database and connect to it. You're prompted again for your cluster password:
Create a limited-privilege user for connecting to this database:
See Details on managing access in BigAnimal databases for more information.
Quit psql (
\q
).To export and import, you need compatible versions of pg_dump and pg_restore. If you aren't already running EDB Postgres Advanced Server, you can use the container image used by Big Animal to ensure compatibility.
Export the Chinook sample database from EDB's cluster using pg_dump:
Get the host name of your cluster from the Connect tab on the BigAnimal portal. Use it in place of
<YOUR CLUSTER HOSTNAME>
to invoke pg_restore:Note
You might see an error about pg_stat_statements. You can safely ignore this error. pg_stat_statements is a very useful extension and is installed by default on BigAnimal clusters. However, since you're connecting as the admin user and not a superuser, you can't modify it. The rest of the schema and data is restored, however.
Finally, connect to this database:
Now you can try some queries on your own cluster.
Next steps
Read more on Oracle compatibility features in the EDB Advanced Server documentation.
Learn about migrating existing databases to BigAnimal.