IF [NOT] EXISTS DDL Clause in Oracle Database 23c

Prevent errors during object creation and removal using the IF [NOT] EXISTS DDL clause in Oracle Database 23c.

◉ Setup

The following objects are required by the examples below.

-- Test user.

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

create user testuser1 identified by testuser1;

grant connect, resource to testuser1;

-- Test objects.

conn testuser1/testuser1@//localhost:1521/freepdb1

create table t1 (id number);

create sequence t1_seq;

create view t1_v as

select * from t1;

create procedure p1 as

begin

  null;

end;

/

◉ The Problem

When creating database change scripts for applications we may not know if an object already exists, which can lead to errors. For example, the following CREATE commands would fail because the objects are already present.

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

Connected.

SQL> create user testuser1 identified by testuser1;

create user testuser1 identified by testuser1

            *

ERROR at line 1:

ORA-01920: user name 'TESTUSER1' conflicts with another user or role name

SQL>

SQL> conn testuser1/testuser1@//localhost:1521/freepdb1

Connected.

SQL> create table t1 (id number);

create table t1 (id number)

             *

ERROR at line 1:

ORA-00955: name is already used by an existing object

SQL>

SQL> create sequence t1_seq;

create sequence t1_seq

                *

ERROR at line 1:

ORA-00955: name is already used by an existing object

SQL>

In a similar way, attempting to drop an object that doesn't exist will result in an error.

SQL> drop table t3 purge;

drop table t3 purge

           *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>

◉ The Solution : IF [NOT] EXISTS

Oracle 23c allows us to use the IF [NOT] EXISTS DDL clause to mask errors when we attempt to create objects that already exist, or drop objects that don't exist.

Here is an example with user creation. The TESTUSER1 user already exists, so it is not created, but the command doesn't return an error. When we attempt to drop the TESTUSER3 user, which doesn't exist, it also doesn't give an error.

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

Connected.

SQL> create user if not exists testuser1 identified by testuser1;

User created.

SQL>

SQL> drop user if exists testuser3 cascade;

User dropped.

SQL>

We see similar behaviour with object creation.

SQL> conn testuser1/testuser1@//localhost:1521/freepdb1

Connected.

SQL> create table if not exists t1 (id number);

Table created.

SQL>

SQL> create sequence if not exists t1_seq;

Sequence created.

SQL>

SQL> create view if not exists t1_v as

select * from t1;

View created.

SQL>

SQL> create procedure if not exists p1 as

begin

  null;

end;

/

Procedure created.

SQL>

As expected, we can attempt to drop objects which do not exist without errors.

SQL> drop table if exists t3;

Table dropped.

SQL> drop sequence if exists t3_seq;

Sequence dropped.

SQL> drop view if exists t3_v;

View dropped.

SQL> drop procedure if exists p3;

Procedure dropped.

SQL>

We can include a presence check in an ALTER statement, but in the case of adding column, we can't guard against a column already being present.

SQL> alter table if exists t1 add (id number);

alter table if exists t1 add (id number)

                              *

ERROR at line 1:

ORA-01430: column being added already exists in table

SQL>

◉ CREATE OR REPLACE

We can't mix CREATE OR REPLACE and the IF NOT EXISTS clause.

SQL> create or replace view if not exists t1_v as

select * from t1;

create or replace view if not exists t1_v as

                                     *

ERROR at line 1:

ORA-11541: REPLACE and IF NOT EXISTS cannot coexist in the same DDL statement

SQL>

SQL> create or replace procedure if not exists p1 as

begin

  null;

end;

/

create or replace procedure if not exists p1 as

                                          *

ERROR at line 1:

ORA-11541: REPLACE and IF NOT EXISTS cannot coexist in the same DDL statement

SQL>

◉ Considerations

Here are some things to consider when using this functionality.

- If we are using the output from a script as an audit of the actions processed, this can be misleading. We will get reports of object creations that didn't actually happen.

- During object creation the IF NOT EXISTS clause is a simple presence check based on the object name. It doesn't compare the structure of the object and report if there is a difference.

- There are limitations to the IF NOT EXISTS clause. As demonstrated above, we can't prevent an error if we try to add a column to a table that is already present.

- The output of some older versions of tools is different to what was presented above in 23c SQL*Plus. For example older versions of SQL Developer, SQLcl and SQL Developer Web all return "Table IF created." rather than "Table {table-name} created" when the presence check is added. Upgrade to the latest tools where possible.

Source: oracle-base.com    

Continue reading

Property Graphs in SQL Developer release 23.1

The Property Graph feature of Oracle Database offers powerful graph support to explore and discover complex relationships in data.

Oracle Database 23c Free - Developer Release is the first release of the next-generation Oracle Database, allowing developers a head-start on building applications with innovative 23c features that simplify the development of modern data-driven apps. The entire feature set of Oracle Database 23c is planned to be generally available within the next 12 months.

Oracle Database 23c Free – Developer Release includes support for using SQL to create and query property graphs, implementing the property graph specification in the SQL:2023 standard. You can now work with property graphs in SQL using any SQL worksheet in SQL Developer.

SQL Developer 23.1 includes the ability to list SQL property graphs in your database, just like you list tables and views:

Listing SQL property graphs and creating a property graph using SQL

You can use the sample data and SQL on github to create a graph and run queries in SQL Developer when connected to Oracle Database 23c Free – Developer Release. Just load the data, run the sample CREATE PROPERTY GRAPH statement, and start running graph queries in SQL!

Using PGQL with Oracle SQL Developer

If you are a developer using the Property Graph Query Language (PGQL), SQL Developer 22.2 introduced a PGQL worksheet to run PGQL queries on Property Graph Views (PGQL property graphs). This worksheet been enhanced in SQL Developer 23.1. 

PGQL can be used to create and query graphs in Oracle Database 12.2 and later.

Open a PGQL worksheet to run PGQL queries

Listing PGQL property graphs

Source: oracle.com

Continue reading

Database links in Autonomous Database Shared are the past - Cloud links are the future

As many of you know, database links are an established mechanism to allow other remote databases to access specific tables or views in your database.

Database links have been around for decades and always require a two-way interaction in one of two ways: The remote (accessing) side contacts you, the data owner, to ask for access and to get the specifics of how to connect to your system. Alternatively, as the data owner, you must proactively contact the remote side and share the access details for the remote side to set up the database link. With Oracle Autonomous Database Shared, this is a thing of the past.

With Cloud Links, new functionality in Autonomous Database Shared, the data owner registers a table or view for remote access for a selected audience defined by the data owner. The data is then instantaneously accessible by everybody who got remote access granted at registration time. No further actions are required to set up a Cloud Link, and whoever is supposed to see and access your data will be able to discover and work with the data made available to them. 

Sounds almost too cool to be true, doesn't it? Let's step through it how it works.

Cloud Links at work

Let's assume I have central sales information in my autonomous database that other autonomous databases need to access remotely now and then. "Trusted" autonomous databases in the same compartment than my system should be able to access all my detail sales data, whereas other satellite databases within my tenancy should only be able to see the aggregated sales information per sales channel. 

The objects I want to give remote access to look as follows:

You see that there is a base table SALES_ALL and a view SALES_VIEW_AGG defined on top of it that removes the customer information and aggregates the sales information for the other dimensions. The base table will be accessible within my trusted compartment, whereas the aggregated sales information without any customer information should be accessible for everybody in my tenancy.

After the Administrator of my Autonomous Database has given me the privilege to register a table (or view) for remote access with the scope needed for the task at hand, I simply register my table SALES for compartmental access and view SALES_VIEW_AGG for my tenancy (for brevity reasons, pls. consult the documentation for the privilege details):

What is it about this registration? 

Cloud Links introduce a new concept of regional namespace and name for any data that is made remotely accessible. Think of it as something similar to the database today, where one of the most famous Oracle tables ever has the name "EMP" and lives in the namespace "SCOTT". There can only be one SCOTT.EMP in your database. With Cloud Links, it's the same concept, just on a regional level and without being tied to a single database. And since it's not linked to a single database but needs some boundaries of visibility, there's a new concept of scope. The scope defines who can access your table or view through a cloud link remotely. The scope can be a region, tenancy, compartment, individual databases, or a combination of those. 

That was it. My view CLOUDLINK.SALES_VIEW_AGG will be remotely accessible within my tenancy as REGIONAL_SALES.SALES_AGG, and table CLOUDLINK.SALES_ALL as TRUSTED_COMPARTMENT.SALES without exposing its origin.

After a brief period of central metadata synchronization, my trusted databases in my compartment can access all my sales data, whereas all databases in my tenancy can access my high-level aggregated information (it normally takes 5 to 10 minutes). Any future database in my tenancy or the same compartment as my database will be able to access the same data, safely and filtered as required for their work based on the registration policies. 

I can verify what objects I registered for remote access for the different scopes in the data dictionary:

The scope of my two registered objects are, as expected, on the tenancy level for REGIONAL_SALES.SALES_AGG and on the compartment level inside my tenancy for TRUSTED_COMPARTMENT.SALES

On the remote (receiving) end, every autonomous database can see what remote objects they have access to by querying the data dictionary:

select * from dba_cloud_link_access;

Let's see what my trusted autonomous databases (in the same compartment) and others in my tenancy will see.

If I connect to a trusted autonomous database, I will see the following output on the left: I can see both remote data sets. In contrast, when I connect to an arbitrary autonomous database within the same tenancy as my autonomous database that registered the objects, the output will look different, as shown on the right: I can only see the data set shared on the tenant level.

Besides the trusted autonomous database and other autonomous databases in my tenancy, no one else will be able to discover or see the table and view that I registered in this example.

Now it's probably only sometimes known to you what data is made remotely available to you, so you can discover what was made available to you or even find particular data of interest by yourself. If you know the data (namespace, name) you can describe it explicitly, or, the more interesting case, you can see what's out there using free text search.

Voilà, we found the dataset that was shared with everybody without necessarily knowing about its existence.

How to work with registered data?

We registered some objects for remote access and verified that we can see these objects within the scope they were defined, but how do I access them now? I do not have any username/password or other means of authentication and authorization that I shared with a remote database that wants to access my data.

The authentication is done at the registration time of an object. In our example, the trusted autonomous database got access to my sales data by being a trusted database within the same compartment. The same is true for the autonomous database in my tenancy for the aggregated sales data. You only need your Administrator to give you the read privilege on cloud links for authorization (again, please consult the documentation for details here), and you're ready to read the remote data.

After having gotten the proper privilege, any remote object that is made accessible for your autonomous database and your user can be queried with standard "cloud link syntax", namely:

select .. from <namespace>.<name>@cloud$link;

You access remote data without any location knowledge:

That was not too hard to set up. If I can do it, you can do it for sure as well.

Continue reading

DML RETURNING Clause Enhancements in Oracle Database 23c

This article demonstrates the enhancements to the DML RETURNING clause introduced in Oracle database 23c.

◉ Setup

The following tables are 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 into t1 values (1, 'ONE', 'Description for ONE');

insert into t1 values (2, 'TWO', 'Description for TWO');

commit;

drop table if exists t2;

create table t2 (

  id          number,

  code        varchar2(6),

  description varchar(25),

  constraint t2_pk primary key (id)

);

insert into t2 values (3, 'THREE', 'Description for THREE');

insert into t2 values (4, 'FOUR', 'Description for FOUR');

commit;

◉ Single Row Update

We can now return old and new values of columns from rows that are touched by an UPDATE statement.

( RETURN | RETURNING) ( OLD | NEW )  expr [ , ( OLD | NEW ) expr ]+ 

INTO  [data_item] ...

In this example we define a variables to hold the old and new values of the CODE and DESCRIPTION columns. We update the T1 table, appending "2" on to the end of the CODE and DESCRIPTION column values for the second row. The RETURNING clause defines the values we want to return, using the OLD and NEW keywords to indicate which value. The INTO clause lists the variables to hold those values.

set serveroutput on

declare

  l_old_code        t1.code%type;

  l_old_description t1.description%type;

  l_new_code        t1.code%type;

  l_new_description t1.description%type;

begin

  update t1

  set    code        = code || '2',

         description = description || '2'

  where  id          = 2

  returning old code , old description, new code, new description

  into l_old_code, l_old_description, l_new_code, l_new_description;

  dbms_output.put_line('l_old_code        = ' || l_old_code);

  dbms_output.put_line('l_old_description = ' || l_old_description);

  dbms_output.put_line('l_new_code        = ' || l_new_code);

  dbms_output.put_line('l_new_description = ' || l_new_description);

  rollback;

END;

/

l_old_code        = TWO

l_old_description = Description for TWO

l_new_code        = TWO2

l_new_description = Description for TWO2

PL/SQL procedure successfully completed.

SQL>

◉ Multiple Row Update

We can do something similar for updates that touch multiple rows, but this time we need to use collections to hold the returned values. In this example we define a collection for each of the returned values. We update the table in a similar way, but this time we change INTO to a BULK COLLECT INTO and list the collections to hold the returned values. We loop through the collections to display the returned values.

set serveroutput on

declare

  type l_old_code_t is table of t1.code%type;

  type l_old_description_t is table of t1.description%type;

  type l_new_code_t is table of t1.code%type;

  type l_new_description_t is table of t1.description%type;

  l_old_code        l_old_code_t;

  l_old_description l_old_description_t;

  l_new_code        l_new_code_t;

  l_new_description l_new_description_t;

begin

  update t1

  set    code        = code || '1',

         description = description || '1'

  returning old code, old description, new code, new description

  bulk collect into l_old_code, l_old_description, l_new_code, l_new_description;

  for i in 1 .. l_old_code.count loop

    dbms_output.put_line('row               = ' || i);

    dbms_output.put_line('l_old.code        = ' || l_old_code(i));

    dbms_output.put_line('l_old.description = ' || l_old_description(i));

    dbms_output.put_line('l_new.code        = ' || l_new_code(i));

    dbms_output.put_line('l_new.description = ' || l_new_description(i));

  end loop;

  rollback;

end;

/

row               = 1

l_old.code        = ONE

l_old.description = Description for ONE

l_new.code        = ONE1

l_new.description = Description for ONE1

row               = 2

l_old.code        = TWO

l_old.description = Description for TWO

l_new.code        = TWO1

l_new.description = Description for TWO1

PL/SQL procedure successfully completed.

SQL>

◉ Insert and Delete

The NEW and OLD keywords can be used on INSERT and DELETE also, but they don't bring anything new to the table. For inserts we have no old values, and for deletes we have no new values.

set serveroutput on

declare

  l_old_code        t1.code%type;

  l_old_description t1.description%type;

  l_new_code        t1.code%type;

  l_new_description t1.description%type;

begin

  insert into t1 (id, code, description)

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

  returning old code , old description, new code, new description

  into l_old_code, l_old_description, l_new_code, l_new_description;

  dbms_output.put_line('l_old_code        = ' || l_old_code);

  dbms_output.put_line('l_old_description = ' || l_old_description);

  dbms_output.put_line('l_new_code        = ' || l_new_code);

  dbms_output.put_line('l_new_description = ' || l_new_description);

  rollback;

END;

/

l_old_code        =

l_old_description =

l_new_code        = FIVE

l_new_description = Description for FIVE

PL/SQL procedure successfully completed.

SQL>

set serveroutput on

declare

  l_old_code        t1.code%type;

  l_old_description t1.description%type;

  l_new_code        t1.code%type;

  l_new_description t1.description%type;

begin

  delete from t1

  where  id = 2

  returning old code , old description, new code, new description

  into l_old_code, l_old_description, l_new_code, l_new_description;

  dbms_output.put_line('l_old_code        = ' || l_old_code);

  dbms_output.put_line('l_old_description = ' || l_old_description);

  dbms_output.put_line('l_new_code        = ' || l_new_code);

  dbms_output.put_line('l_new_description = ' || l_new_description);

  rollback;

END;

/

l_old_code        = TWO

l_old_description = Description for TWO

l_new_code        =

l_new_description =

PL/SQL procedure successfully completed.

SQL>

Source: oracle-base.com

Continue reading

DB_DEVELOPER_ROLE Role in Oracle Database 23c

Oracle database 23c introduced a new DB_DEVELOPER_ROLE role, to provide the basic roles and privileges Oracle believe are necessary for a database developer.

◉ DB_DEVELOPER_ROLE Role Basics

Oracle database 23c introduced a new DB_DEVELOPER_ROLE role, to provide the basic roles and privileges Oracle believe are necessary for a database developer. Prior to this release, people would often grant the CONNECT and RESOURCE roles as a starting point for most developers, but the DB_DEVELOPER_ROLE role is more than the sum of these two roles, as we will se below.

The DB_DEVELOPER_ROLE role is granted and revoked in the normal way.

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

grant db_developer_role to testuser1;

revoke db_developer_role from testuser1;

◉ Comparing CONNECT, RESOURCE and DB_DEVELOPER_ROLE

The following three sections display the system, role and object privileges associated with each role.

   ◉ CONNECT Role

Here are the system, role and object privileges associated with the CONNECT role.

variable v_role VARCHAR2(30)

exec :v_role := 'CONNECT';

-- System Privileges

select sp.privilege

from   dba_sys_privs sp

where  sp.grantee = :v_role

order by 1;

PRIVILEGE

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

CREATE SESSION

SET CONTAINER

SQL>

-- Role Privileges

select rp.granted_role

from   dba_role_privs rp

where  rp.grantee = :v_role

order by 1;

no rows selected

SQL>

-- Object Privileges

column privilege format a30

column table_name format a30

select tp.privilege, tp.table_name 

from   dba_tab_privs tp

where  tp.grantee = :v_role

order by 1, 2;

no rows selected

SQL>

   ◉ RESOURCE Role

Here are the system, role and object privileges associated with the RESOURCE role.

variable v_role VARCHAR2(30)

exec :v_role := 'RESOURCE';

-- System Privileges

select sp.privilege

from   dba_sys_privs sp

where  sp.grantee = :v_role

order by 1;

PRIVILEGE

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

CREATE ANALYTIC VIEW

CREATE ATTRIBUTE DIMENSION

CREATE CLUSTER

CREATE HIERARCHY

CREATE INDEXTYPE

CREATE MATERIALIZED VIEW

CREATE OPERATOR

CREATE PROCEDURE

CREATE PROPERTY GRAPH

CREATE SEQUENCE

CREATE SYNONYM

CREATE TABLE

CREATE TRIGGER

CREATE TYPE

CREATE VIEW

15 rows selected.

SQL>

-- Role Privileges

select rp.granted_role

from   dba_role_privs rp

where  rp.grantee = :v_role

order by 1;

GRANTED_ROLE

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

SODA_APP

SQL>

-- Object Privileges

column table_name format a30

select tp.privilege, tp.table_name 

from   dba_tab_privs tp

where  tp.grantee = :v_role

order by 1, 2;

no rows selected

SQL>

   ◉ DB_DEVELOPER_ROLE Role

Here are the system, role and object privileges associated with the DB_DEVELOPER_ROLE role.

variable v_role VARCHAR2(30)

exec :v_role := 'DB_DEVELOPER_ROLE';

-- System Privileges

select sp.privilege

from   dba_sys_privs sp

where  sp.grantee = :v_role

order by 1;

PRIVILEGE

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

CREATE ANALYTIC VIEW

CREATE ATTRIBUTE DIMENSION

CREATE CUBE

CREATE CUBE BUILD PROCESS

CREATE CUBE DIMENSION

CREATE DIMENSION

CREATE DOMAIN

CREATE HIERARCHY

CREATE JOB

CREATE MATERIALIZED VIEW

CREATE MINING MODEL

CREATE MLE

CREATE PROCEDURE

CREATE SEQUENCE

CREATE SESSION

CREATE SYNONYM

CREATE TABLE

CREATE TRIGGER

CREATE TYPE

CREATE VIEW

DEBUG CONNECT SESSION

EXECUTE DYNAMIC MLE

FORCE TRANSACTION

ON COMMIT REFRESH

24 rows selected.

SQL>

-- Role Privileges

select rp.granted_role

from   dba_role_privs rp

where  rp.grantee = :v_role

order by 1;

GRANTED_ROLE

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

CTXAPP

SODA_APP

SQL>

-- Object Privileges

column privilege format a30

column table_name format a30

select tp.privilege, tp.table_name 

from   dba_tab_privs tp

where  tp.grantee = :v_role

order by 1, 2;

PRIVILEGE                      TABLE_NAME

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

EXECUTE                        JAVASCRIPT

READ                               V_$PARAMETER

READ                               V_$STATNAME

SELECT                            DBA_PENDING_TRANSACTIONS

SQL>

◉ Considerations

For many years we have been discouraged from using the predefined roles, and encouraged to use the "least privileges" approach when granting privileges to users. From a security perspective, the less privileges a user has, the smaller the attack surface.

We can see from the above comparison, the DB_DEVELOPER_ROLE role has more privileges than the CONNECT and the RESOURCE roles combined. For some people or companies this might be considered too much privilege. It's up to you or your company to decide if this role is suitable or not.

Continue reading

CASE Statement and CASE Expression Enhancements in Oracle Database 23c

In Oracle database 23c the simple CASE statement and expression are more flexible, allowing dangling predicates and multiple choices in a single WHEN clause.

This brings the PL/SQL simple CASE statement and expression in line with the SQL:2003 Standard [ISO03a, ISO03b] standard.

Setup

The examples in this article use the following table.

drop table if exists t1 purge;

create table t1 (

  pct number

);

insert into t1 (pct)

values (-1), (0), (10), (40), (70), (80), (90), (100), (101);

commit;

The Problem

In previous releases simple CASE statements and expressions were only capable of performing equality checks. If we needed comparisons other than equality checks we would have to use a searched case statement or expression.

In the following example we use a searched CASE statement to evaluate a threshold. We are using a searched CASE statement because most of the comparisons are not simple equality checks.

set serveroutput on

declare

  l_threshold varchar2(20);

begin

  for cur_rec in (select pct from t1) loop

    case 

      when cur_rec.pct  = 40 then l_threshold := 'Optimal';

      when cur_rec.pct <= 70 then l_threshold := 'Safe';

      when cur_rec.pct <= 80 then l_threshold := 'Check';

      when cur_rec.pct <= 90 then l_threshold := 'Warning';

      when cur_rec.pct  > 90 then l_threshold := 'Critical';

    end case;

    dbms_output.put_line(cur_rec.pct || ' : ' || l_threshold);

  end loop;

end;

/

-1 : Safe

0 : Safe

10 : Safe

40 : Optimal

70 : Safe

80 : Check

90 : Warning

100 : Critical

101 : Critical

PL/SQL procedure successfully completed.

SQL>

This example uses a searched CASE expression to do the same thing.

set serveroutput on

declare

  l_threshold varchar2(20);

begin

  for cur_rec in (select pct from t1) loop

    l_threshold := case 

                     when cur_rec.pct  = 40 then 'Optimal'

                     when cur_rec.pct <= 70 then 'Safe'

                     when cur_rec.pct <= 80 then 'Check'

                     when cur_rec.pct <= 90 then 'Warning'

                     when cur_rec.pct  > 90 then 'Critical'

                   end;

    dbms_output.put_line(cur_rec.pct || ' : ' || l_threshold);

  end loop;

end;

/

-1 : Safe

0 : Safe

10 : Safe

40 : Optimal

70 : Safe

80 : Check

90 : Warning

100 : Critical

101 : Critical

PL/SQL procedure successfully completed.

SQL>

Dangling Predicates

In Oracle 23c we can do the same thing using a simple CASE statement or expression using dangling predicates. A dangling predicate is an expression with its left operand missing.

This example uses a simple CASE statement with dangling predicates to achieve the same result.

set serveroutput on

declare

  l_threshold varchar2(20);

begin

  for cur_rec in (select pct from t1) loop

    case cur_rec.pct

      when 40    then l_threshold := 'Optimal';

      when <= 70 then l_threshold := 'Safe';

      when <= 80 then l_threshold := 'Check';

      when <= 90 then l_threshold := 'Warning';

      when  > 90 then l_threshold := 'Critical';

    end case;

    dbms_output.put_line(cur_rec.pct || ' : ' || l_threshold);

  end loop;

end;

/

-1 : Safe

0 : Safe

10 : Safe

40 : Optimal

70 : Safe

80 : Check

90 : Warning

100 : Critical

101 : Critical

PL/SQL procedure successfully completed.

SQL>

Here is the simple CASE expression equivalent.

set serveroutput on

declare

  l_threshold varchar2(20);

begin

  for cur_rec in (select pct from t1) loop

    l_threshold := case cur_rec.pct

                     when 40    then 'Optimal'

                     when <= 70 then 'Safe'

                     when <= 80 then 'Check'

                     when <= 90 then 'Warning'

                     when  > 90 then 'Critical'

                   end;

    dbms_output.put_line(cur_rec.pct || ' : ' || l_threshold);

  end loop;

end;

/

-1 : Safe

0 : Safe

10 : Safe

40 : Optimal

70 : Safe

80 : Check

90 : Warning

100 : Critical

101 : Critical

PL/SQL procedure successfully completed.

SQL>

Multiple Choices in a Single WHEN Clause

A single WHEN clause can include multiple equality checks or dangling predicates as a comma-separated list.

In the following example we use a simple CASE statement to show an error if a value is below 0, exactly 0.5 or greater than 100. We also add values 41 and 42 to the optimal threshold.

set serveroutput on

declare

  l_threshold varchar2(20);

begin

  for cur_rec in (select pct from t1) loop

    case cur_rec.pct

      when < 0, 0.5, > 100 then l_threshold := 'Error';

      when 40, 41, 42      then l_threshold := 'Optimal';

      when <= 70           then l_threshold := 'Safe';

      when <= 80           then l_threshold := 'Check';

      when <= 90           then l_threshold := 'Warning';

      when  > 90           then l_threshold := 'Critical';

    end case;

    dbms_output.put_line(cur_rec.pct || ' : ' || l_threshold);

  end loop;

end;

/

-1 : Error

0 : Safe

10 : Safe

40 : Optimal

70 : Safe

80 : Check

90 : Warning

100 : Critical

101 : Error

PL/SQL procedure successfully completed.

SQL>

This is the simple CASE expression equivalent of the previous example.

set serveroutput on

declare

  l_threshold varchar2(20);

begin

  for cur_rec in (select pct from t1) loop

    l_threshold := case cur_rec.pct

                     when < 0, 0.5, > 100 then 'Error'

                     when 40, 41, 42      then 'Optimal'

                     when <= 70           then 'Safe'

                     when <= 80           then 'Check'

                     when <= 90           then 'Warning'

                     when  > 90           then 'Critical'

                   end;

    dbms_output.put_line(cur_rec.pct || ' : ' || l_threshold);

  end loop;

end;

/

-1 : Error

0 : Safe

10 : Safe

40 : Optimal

70 : Safe

80 : Check

90 : Warning

100 : Critical

101 : Error

PL/SQL procedure successfully completed.

SQL>

SQL CASE Expressions

This functionality is not supported directly by SQL CASE expressions.

select pct,

       case pct

         when 0.5, < 0, > 100 then 'Error'

         when 40, 41, 42      then 'Optimal'

         when <= 70           then 'Safe'

         when <= 80           then 'Check'

         when <= 90           then 'Warning'

         when  > 90           then 'Critical'

       end as status

from   t1;

when 0.5, < 0, > 100 then 'Error'

                 *

ERROR at line 3:

ORA-02000: missing THEN keyword

SQL>

We can achieve a similar result by defining a function in the WITH clause to perform the CASE expression.

with

  function get_status(p_pct in number) return varchar2 is

  begin

    return case p_pct

             when < 0, 0.5, > 100 then 'Error'

             when 40, 41, 42      then 'Optimal'

             when <= 70           then 'Safe'

             when <= 80           then 'Check'

             when <= 90           then 'Warning'

             when  > 90           then 'Critical'

           end;

  end;

select pct, get_status(pct) as status

from   t1

/

       PCT STATUS

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

        -1 Error

         0 Safe

        10 Safe

        40 Optimal

        70 Safe

        80 Check

        90 Warning

       100 Critical

       101 Error

9 rows selected.

SQL>

Continue reading

Annotations in Oracle Database 23c

This article describes the use of annotations to document our database objects in Oracle database 23c.

What's the Point?

We can think of annotations as an extension of database comments. With comments we were able to add free text to objects such as tables and columns, allowing us to describe their purpose and usage. Annotations take this a step further, allowing us to associate name-value pairs to most database objects, which can be used to describe or classify them. The names and values are free text, so we can pick anything that is meaningful to us.

You may see annotations described as "application usage annotations". This simply emphasizes that annotations are mean to help the documentation of your application.

Using Annotations

If an object supports annotations, the annotations clause typically follows this format.

ANNOTATIONS ( {ADD|DROP} annotation_name {'annotation_value'} {,} )

Here is a simple example of adding annotations to a table. Notice we don't use the optional ADD keyword while adding these annotations.

create table fruit (

  id           number        annotations (SurrogateKey, UI_Display 'Fruit ID', Classification 'Fruit Info'),

  name         varchar2(50)  annotations (UI_Display 'Fruit Name', Classification 'Fruit Info'), 

  description  varchar2(50)  annotations (UI_Display 'Description', Classification 'Fruit Info')

)

annotations (UI_Display 'Fruit Table', Classification 'Fruit Info');

Let's look at each in turn.

◉ SurrogateKey: This annotation tells us the column is a surrogate key. Notice this annotation has no value, as the name is sufficient to provide meaning.

◉ UI_Display: This annotation gives the preferred display name in any UI.

◉ Classificaton: This annotation allows us to classify the type of information present. In this case we classify it as "Fruit Info".

Remember, all these annotations are just free text we have made up. They have no meaning to the database or any tool other than the meaning we associate with them. If we need spaces in the key names we must double-quote them. This is true of reserved words also.

We can modify the annotations by adding or dropping annotations. In these examples we add a new annotation to the table without using the optional ADD keyword, we drop the annotation using the DROP keyword, and we add it again, this time using the optional ADD keyword.

alter table fruit annotations (Visibility 'Everyone');

alter table fruit annotations (drop Visibility);

alter table fruit annotations (add Visibility 'Everyone');

We can do a similar thing with the column level annotations.

alter table fruit modify (id annotations (Visibility 'Hidden'));

alter table fruit modify (id annotations (drop Visibility));

alter table fruit modify (id annotations (add Visibility 'Hidden'));

Views

There are two main views associated with annotations. They are the USER_ANNOTATIONS and USER_ANNOTATIONS_USAGE views, but you will probably only use the USER_ANNOTATIONS_USAGE view.

set linesize 150

column object_name format a12

column object_type format a12

column column_name format a12

column domain_name format a12

column domain_owner format a12

column annotation_name format a14

column annotation_value format a20

select object_name,

       object_type,

       column_name,

       domain_name,

       domain_owner,

       annotation_name,

       annotation_value

from   user_annotations_usage

order by annotation_name, annotation_value;

OBJECT _NAME	OBJECT _TYPE	COLUMN _NAME	DOMAIN _NAME	DOMAIN _OWNER	ANNOTATION_NAM	ANNOTATION _VALUE
FRUIT	TABLE	ID			CLASSIFICATION
FRUIT	TABLE	DESCRIPTION			CLASSIFICATION
FRUIT	TABLE	NAME			CLASSIFICATION
FRUIT	TABLE				CLASSIFICATION
FRUIT	TABLE	ID			SURROGATEKEY
FRUIT	TABLE	DESCRIPTION			UI_DISPLAY
FRUIT	TABLE	ID			UI_DISPLAY
FRUIT	TABLE	NAME			UI_DISPLAY
FRUIT	TABLE				UI_DISPLAY
FRUIT	TABLE				VISIBILITY
FRUIT	TABLE	ID			VISIBILITY

11 rows selected.

SQL>

Continue reading

Operational Property Graphs in Oracle Database 23c Free - Developer Release

Oracle Database 23c Free - Developer Release is the first release of the next-generation Oracle Database, allowing developers a head-start on building applications with innovative 23c features that simplify the development of modern data-driven apps. The entire feature set of Oracle Database 23c is planned to be generally available within the next 12 months.

 

Your data is connected. Traversing implicit or explict connections in your data usually requires complex recursive queries or lengthy joins. In Oracle Database 23c Free - Developer Release, the GRAPH_TABLE function and MATCH clause of the new SQL:2023 standard enable you to write simple SQL queries to follow connections in data. You can model your data as a graph and run graph queries in SQL to quickly see relationships in your data that are difficult to identify otherwise. This has a variety of applications, from making product recommendations to detecting financial fraud to identifying dependencies in IT workflows. Graphs are a powerful tool to extract value from your data based on the connections embedded in data.

Graphs created from relational tables are like a ‘view’ on top of these tables. Data will not be moved into a specialized schema or database. Data stays in place in the source database tables, making graphs in Oracle Database particularly well-suited for applications working with operational property graphs. All inserts, updates, and deletes to the source tables are instantly reflected in the graph. 

Graphs have been supported in Oracle Database since release 12c, with graph queries enabled by the Property Graph Query Language (PGQL). In Oracle Database 23c Free - Developer Release, this functionality is available in SQL. The property graph data model consists of vertices connected to other vertices by edges, each of which can have associated key-value pairs (properties). Typically, vertices represent entities in a dataset (such as a ‘customer,’ ‘product,’ or ‘account_id’), and edges represent the relationships between these entitles. Queries are based on graph pattern matching, which allows you to specify patterns that are matched against vertices and edges in a graph model of data.

 

PGQL will continue to be supported in 23c, and your PGQL code will continue to work as before. However, if you're new to graphs or developing new functionality, this new SQL support is an exciting opportunity to explore graphs using the language and tools you already know. Building a graph using SQL is a simple process. Let's look at an example:

CREATE PROPERTY GRAPH bank_graph

    VERTEX TABLES (

        bank_accounts as ACCOUNTS

        PROPERTIES(ID, BALANCE)

    )

    EDGE TABLES (

        bank_transfers

        SOURCE KEY      (from_acc) REFERENCES ACCOUNTS(ID)

        DESTINATION KEY (to_acc)   REFERENCES ACCOUNTS(ID)

        PROPERTIES (amount)

    )

Here we create a property graph called bank_graph with a vertex table bank_accounts with two properties, id and balance. Our graph also has an edge table, bank_transfers, with the property amount representing the transfer of money between them.  

If we want to query the property graph that we just created, it's simple. But let's review a few basic syntax specifications we'll need to know first.

Symbol	Name	Example
()	Vertex	(v1) and (v2) are bank accounts
[]	Edge	[e1] represents a cash transfer between them
{}	<Path length	{1,3}
->	Directed edge

SELECT distinct(account_id)

FROM GRAPH_TABLE(bank_graph

   MATCH (v1)-[is bank_transfers]->{3,5}(v1)

    COLUMNS (v1.id as account_id)  

);

This query returns all accounts that are cycles (start and stop with the same account) that are 3 to 5 hops long.

Oracle Database is the graph database for the enterprise. It enables high-performance, scalable graph queries with the security, high availability, and simpler manageability required by enterprise applications.

Source: oracle.com

Continue reading

Key benefits of JSON Relational Duality: Experience it today using "Oracle Database 23c Free--Developer Release"

Oracle Database 23c JSON Relational Duality revolutionizes appdev by converging the benefits of the relational and document worlds within a single database. The new feature that enables this convergence is called JSON Relational Duality View (Will be simply referred below as Duality View).

With Duality View, while data gets stored in relational tables in a normalized way, it can be exposed to apps as JSON documents. Storing the data in relational tables delivers all the benefits of the relational model, whereas by exposing the same data as JSON documents, apps get schema flexibility and other benefits of the document model.

Let's look at some of the key benefits of using JSON Relational Duality:

1. Get the best of both worlds!

JSON Relational Duality helps to unify the benefits of both document and relational worlds. Developers now get the flexibility and data access benefits of the JSON document model, plus the storage efficiency and power of the relational model.

2. Don’t be forced into making compromises when building apps:

Using documents is not efficient for all use cases. Currently, if an organization stores data in a document database, they are forced to adapt various use cases even if using documents is not the most efficient way.

How JSON Relational Duality makes it flexible and efficient: Experience extreme flexibility in building apps using Duality view. Developers can access the same data relationally or as hierarchical documents based on their use case and are not forced into making compromises because of the limitations of the underlying database. Build document-centric apps on relational data or create SQL apps on documents.

Developers can manipulate documents realized by Duality Views using their favorite drivers, frameworks, tools, and development methods. They can operate on the same data as relational using standard SQL or as documents using standard document APIs:

◉ Analytics, reporting, and machine learning apps can access or modify data relationally (as rows and columns) using languages such as SQL, PL/SQL, C, Java, and JavaScript.

◉ Document-centric apps can use document APIs, such as REST, Simple Oracle Document Access (SODA), and Mongo API, to access the same data.

3. Don’t get limited to one JSON hierarchy as in document databases:

Document database limits flexibility. When using a document database, developers are forced to use one hierarchy for various use cases. For example, using Orders hierarchy to build customer profiles or accessing customer data will be inefficient. Using one JSON hierarchy for all use cases makes appdev complex and impacts performance.

How JSON Relational Duality makes it easy: Developers can use the power of Duality view in defining multiple JSON Views across overlapping groups of tables. This flexible data modeling makes building multiple apps against the same data easy and efficient.

4. Eliminate data duplication when working with documents:

Document databases have an inherent problem that they keep their documents independent. They do not have a good mechanism to declare relationships between documents, leading to data duplication and inconsistencies. Duplicated data leads to higher maintenance costs, decision-making errors, and lost business opportunities.

JSON Relational Duality eliminates data duplication: Duality Views are fully ACID (atomicity, consistency, isolation, durability) transactions across multiple documents and tables. It eliminates data duplication, whereas consistency is maintained automatically.

5. Eliminate round-trips and object-relational impedance mismatch:

Currently, app developers have to map between app-tier objects (used by programming languages) and relational tables. Developers assemble and modify data from several tables to implement a business use case. This is well-known in the industry as an object-relational impedance mismatch problem. The custom mapping between app-tier objects and relational tables makes app development more complex. In addition, since an operation on an app-tier object may lead to multiple access or update to participating tables, it also slows down performance.

Duality View eliminates the need for mapping objects to relational tables: It provides fully updateable JSON views over data. Apps can simply read a document, make necessary changes, and write the document back without worrying about underlying data structure, mapping, consistency, or performance tuning. Experience simplicity by retrieving and storing all the data needed for an app in a single database operation.

6. Build apps that support high concurrency access and updates:

Traditional locks don’t work well for modern apps. A new lock-free concurrency control provided with Duality View helps support high concurrency updates, which also works efficiently for interactive applications since the data is not locked during human thinking time. Duality View also provides document-level serializability that helps to streamline concurrent updates to underlying tables. It automatically ensures consistency between document updates and direct updates to underlying tables — document APIs and SQL applications can update the same data concurrently.

Experience the benefit yourself: Try JSON Relational Duality using “Oracle Database 23c Free--Developer Release”

Oracle announced "Oracle Database 23c Free--Developer Release", allowing developers to start building apps using 23c features like JSON Relational Duality. We are making it easy for you to experience the power and flexibility of Duality View in building apps. We are providing well-documented and easy-to-learn tutorials on GitHub. You can download the tutorials and use them with “Oracle Database 23c Free--Developer Release”. In the tutorials, developers can use SQL, REST and Oracle Database API for MongoDB to try features, capabilities and examples related to Duality Views. We will also be providing LiveLabs to play with Duality Views in a ready-to-run environment - stay tuned.

Source: oracle.com

Continue reading

What Careers Require 1Z0-149 Certification?

If you are an Oracle Database Application Development certified professional, you have already gained a competitive edge over others. The next step is to pursue advanced certifications to enhance your value in the job market. One such certification is the Oracle Database Program with PL/SQL 1Z0-149 certification, which validates your skills in programming with PL/SQL in Oracle Database 19c.

The demand for Oracle Database professionals is increasing daily due to the widespread use of Oracle Database in various industries. The Oracle Database Program with PL/SQL certification is an advanced certification that focuses on developing skills in programming with PL/SQL in Oracle Database 19c. With this certification, you can demonstrate your expertise in Oracle Database Application Development, which employers highly value.

Benefits of 1Z0-149 Certification

The Oracle Database Program with PL/SQL certification offers several benefits to professionals, such as:

• Increased value in the job market
• Enhanced credibility and recognition
• Improved job performance and productivity
• Higher salary and career growth opportunities
• Access to Oracle's expert community

⇉ Also Read: What Is Oracle 1Z0-149 Certification and Why Should You Care? ⇇

Top Career Opportunities for 1Z0-149 Certified Professionals

• Database Developer: A database developer is responsible for designing, developing, and maintaining databases for organizations. With 1Z0-149 certification, you can demonstrate your expertise in programming with PL/SQL, which is essential for database development.
• Database Administrator: A database administrator ensures security, availability, and performance. With 1Z0-149 certification, you can demonstrate your expertise in PL/SQL programming, which is necessary for database administration.
• Database Analyst: A database analyst analyzes and interprets data to provide insights and recommendations to organizations. With 1Z0-149 certification, you can demonstrate your expertise in PL/SQL programming, which is necessary for database analysis.
• PL/SQL Developer: A PL/SQL developer is responsible for developing and maintaining PL/SQL code for applications. With 1Z0-149 certification, you can demonstrate your expertise in PL/SQL programming, which is essential for PL/SQL development.
• Business Intelligence Analyst: A business intelligence analyst analyzes data to provide insights to help organizations make informed decisions. With 1Z0-149 certification, you can demonstrate your PL/SQL programming expertise necessary for business intelligence analysis.
• Data Warehouse Analyst: A data warehouse analyst is responsible for designing, developing, and maintaining data warehouses for organizations. With 1Z0-149 certification, you can demonstrate your PL/SQL programming expertise, which is essential for data warehouse development.
• IT Project Manager: An IT project manager manages projects from initiation to completion. With 1Z0-149 certification, you can demonstrate your expertise in PL/SQL programming, which can be valuable in managing IT projects that involve Oracle Database.
• Cloud Administrator: A cloud administrator manages cloud-based databases and applications. With 1Z0-149 certification, you can demonstrate your PL/SQL programming expertise, essential for managing Oracle Database in the cloud.
• Database Security Analyst: A database security analyst is responsible for ensuring the security of databases and the data they contain. With 1Z0-149 certification, you can demonstrate your expertise in PL/SQL programming, which can be valuable in implementing database security measures.
• Database Architect: A database architect is responsible for designing and maintaining databases that meet an organization's needs. With 1Z0-149 certification, you can demonstrate your expertise in PL/SQL programming, which is essential for database architecture.

• Database Administrator Manager: A database administrator manager manages a team of administrators who maintain and optimize databases for an organization. With 1Z0-149 certification, you can demonstrate your expertise in PL/SQL programming, which can be valuable in managing a team of Oracle Database administrators.
• Data Analyst: A data analyst collects, analyzes, and interprets data to provide insights to help organizations make informed decisions. With 1Z0-149 certification, you can demonstrate your expertise in PL/SQL programming, which is necessary for data analysis in Oracle Database.
• Database Performance Analyst: A database performance analyst is responsible for identifying and resolving database performance issues. With 1Z0-149 certification, you can demonstrate your expertise in PL/SQL programming, which can be valuable in optimizing the performance of Oracle Database.
• Database Developer Manager: A database developer manager manages a team of developers who create and maintain databases for an organization. With 1Z0-149 certification, you can demonstrate your expertise in PL/SQL programming, which can be valuable in managing a team of Oracle Database developers.

These are just a few career opportunities available to 1Z0-149 certified professionals. With this certification, you can gain the skills and knowledge necessary to pursue a rewarding Oracle Database development and administration career.

Conclusion

The Oracle Database Program with PL/SQL certification is a valuable credential that can open up numerous professional career opportunities. The certification validates your skills in programming with PL/SQL in Oracle Database 19c, which employers highly value.

Continue reading