The 23c Database Free – Developer Release is aimed at allowing developer gain early access to lots of new features, and to allow them to develop or update their applications in time for when the full 23c Database is released. This is a new Developer First approach, as opposed to the Cloud First approach we have previously experienced. All of the features in 23c Database Free – Developer Release will be in the full bells and whistles 23c Database.
In this post I’m going to have a look at a few of the more straightforward new features, hence the usage of “Quickies” in title of the post. Just remember there are lots more features which require a more detailed example, which will be in a separate post. These Quickies include
- DUAL – no more DUAL
- DB_DEVELOPER_ROLE
- BOOLEAN
- Domains
- Annotations
Of course there are lots more, but these are enough to get started with and for this post. More posts will following. Also check out the New Features Guide.
DUAL – no more DUAL
There’s been lots of talk and debating about the DUAL table for many years, maybe a decade or more. Other Database vendors and people have mocked Oracle for having the DUAL table, and for forcing people to SELECT from DUAL to get some value or other. Does it really matter if a DUAL table is needed or not? I’m sure the debates will continue. With 23c Database the need to use DUAL for those type of queries is Gone. Some love it, while some just don’t care. But for those migrating to use an Oracle Database, from another Database where DUAL is not used, then this is just another thing you don’t have to worry about. Also useful in the multi Database environments.
Before we had to
select sysdate from dual;Now we have
select sysdate
Just think of all the scenarios you’ve encountered when you needed to select from DUAL. Now you don’t have to. Probably most of those scenarios have been in PL/SQL. Yes the new feature works there too,
DECLARE
vDate varchar2(20);
BEGIN
select to_char(sysdate, 'Day')
into vDate;
-- Print the Day
dbms_output.put_line('Today is '||vDate);
END;
/
Today is ThursdayJust remember DUAL is not going away, and will be needed by some, no code changes. From now on, you don’t have to use it. The choice is yours.
DB_DEVELOPER_ROLE
We have a new developer role containing most or all of the privileges a develop will need to work with data in the Database. Typically the DBA would create a Role which would then be granted to Developers. The DBA would need to work out what privileges the developers needed. The new DB_DEVELOPER_ROLE is designed to make the DBA job a little easier, or does it? Although having such a role can be useful, it doesn’t really remove the need for the DBA to curate what limited set of privileges the developers need. The DBA will probably have to keep doing what they have been doing up to now and keep control of what the developers can and cannot do. Despite this, the new role can be useful in some situations and have help to get developers run and running quickly before the DBA comes in to start restricting them.
create user brendan identified by Password1;
grant DB_DEVELOPER_ROLE to brendan;
connect brendan/Password1
select * from session_privs order by 1;
BOOLEAN
WARNING: An important warning is needed with using this new feature. It will only work in SQL*Plus that comes with the VM or Docker image. To use it you’ll need to connect/open a terminal window to the Docker or VM and run SQL*Plus. The Boolean data type is rendered correction in versions of SQL*Plus and SQL Command Line in earlier version than 23. It looks like these updates will be publically available for download later in 2023. Until then, you have limited choice. I’ll show below an example of what BOOLEAN looks like in the “new” version (only available on Docker and VM) and old versions of these tools.
What does BOOLEAN really mean? That question has been debated for many, many years now and even now there is still disagreement across many in the IT industry as to what it means and what values are allowed. For example, can a BOOLEAN have a null value, and if it can what does it mean. After many years of debating in the subset Oracle community, Oracle has finally added a BOOLEAN data type, although it has long existed in PL/SQL.
The BOOLEAN data type can have one of three values, TRUE, FALSE and null. TRUE/FALSE are not the only allowed values when inserting or updating data. The table below (from the docs) lists all the allowed values, and you can use these in both upper and lower case.
This means you can use any of the values listed above when inserting or updating data and they will be transformed in the corresponding TRUE or FALSE.
Note: While BOOLEAN is name of the new data type, there is also a shorthand version of it called BOOL. You can also set a default value to being TRUE or FALSE e.g. …, c1 BOOLEAN DEFAULT TRUE).
create table t_bol (
c1 NUMBER,
c2 BOOLEAN,
c3 BOOL);insert into t_bol (c1, c2, c3) values (1, 'true', 'false');
insert into t_bol (c1, c2, c3) values (2, 'TRUE', 'FALSE');
insert into t_bol (c1, c2, c3) values (3, 'yes', 'no');
insert into t_bol (c1, c2, c3) values (4, 'YES', 'NO');
insert into t_bol (c1, c2, c3) values (5, 'on', 'off');
insert into t_bol (c1, c2, c3) values (6, 'ON', 'OFF');
insert into t_bol (c1, c2, c3) values (7, 1, 0);
insert into t_bol (c1, c2, c3) values (8, '1', '0');
insert into t_bol (c1, c2, c3) values (9, 't', 'f');
insert into t_bol (c1, c2, c3) values (10, 'T', 'F');
insert into t_bol (c1, c2, c3) values (11, 'y', 'n');
insert into t_bol (c1, c2, c3) values (12, 'Y', 'N');
insert into t_bol (c1, c2, c3) values (13, null, 'FALSE');
insert into t_bol (c1, c2, c3) values (14, 'TRUE', null);
The image on the above left is from using SQL*Plus on the Docker image, while the image on the right is from using SQL*Plus on my Laptop (latest Mac release of 19.8). I get the same right image from using SQL Command Line 23.1
When querying the data you can use any of the values listed in the above table to filter data. Here are some examples.
-- testing TRUE Boolean value. These are all equivalent
select * from t_bol where c2 = 1;
select * from t_bol where c2 = TRUE;
select * from t_bol where c2 = 'yes';
-- testing FALSE boolean value
select * from t_bol where c3 = 0;
select * from t_bol where c3 = False;
select * from t_bol where c3 = 'n';Domains
The idea and use of Domains has long existed with Databases, but mostly from the design perspective. Domains were then implemented by the design tools which mapped these to columns in tables. Apart from that there was limited ability to manage the domains an over time changes to how these were implemented changed over time, with slight column renaming, or changes to data types etc. With the introduction of DOMAINS we can not over come this limitation and ensure the enforcement of the domain specification and avoid any slight changes that might occur.
With Domains in 23c, we have a few options from simple domain expressions, datetime domain expressions, interval domain expressions and compound domain expressions.
An example of a single column domain the typical example is with email addresses.
WARNING: The table descriptions given below showing the domains will only appear in version 23+ of SQL*Plus and SQLcl. These are only available on the Docker and VM images. The updated client tools should be available later in 2023. If you are using current or older versions of these tools, the Domain information will not show when you describe a table.
create DOMAIN domain_email as varchar2(80)
constraint dom_email_con check (regexp_like(domain_email, '^(\S+)\@(\S+).(\S+)$'))We can now create a table using this domain.
create table t_domain (
id number,
home_email domain domain_email,
work_email domain domain_email,
student_email domain domain_email);SQL> desc t_domain
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
HOME_EMAIL VARCHAR2(80) BRENDAN.DOMAIN_EMAIL
WORK_EMAIL VARCHAR2(80) BRENDAN.DOMAIN_EMAIL
STUDENT_EMAIL VARCHAR2(80) BRENDAN.DOMAIN_EMAIL
insert into t_domain values (1, 'brendan@brendan.com', null, null);
1 row inserted.
SQL> select * from t_domain;
ID HOME_EMAIL WORK_EMAIL STUDENT_EMAIL
_____ ______________________ _____________ ________________
1 brendan@brendan.com
SQL> insert into t_domain values (2, 'brendan', 'brendan@work.com', 'brendan@student.com')
Error starting at line : 1 in command -
insert into t_domain values (2, 'brendan', 'brendan@work.com', 'brendan@student.com')
Error report -
ORA-11534: check constraint (BRENDAN.SYS_C008228) due to domain constraint BRENDAN.DOM_EMAIL_CON of domain
BRENDAN.DOMAIN_EMAIL violated
SQL> insert into t_domain values (2, 'brendan@home.com', 'brendan@work.com', 'brendan@student.com')
1 row inserted.
SQL> select * from t_domain;
ID HOME_EMAIL WORK_EMAIL STUDENT_EMAIL
_____ ______________________ ___________________ ______________________
1 brendan@brendan.com
2 brendan@home.com brendan@work.com brendan@student.com
A typical multi column domain would be for address details, for example:
create domain domain_address as (
address1 as varchar2(50),
address2 as varchar2(50),
address3 as varchar2(50),
postcode as varchar2(8),
country as varchar2(30) )
constraint address_chk check (
address1 is not null
and address3 is not null
and postcode is not null
and country is not null );Creating a table incorporating domain like this is different to the previous example. For a multi-column scenario, we need to define the columns and then attached the domain to it. Yes this seems odd given the previous example, but we just have to work with it.
create table t_domain2 (
id number,
address1 varchar2(50),
address2 varchar2(50),
address3 varchar2(50),
postcode varchar2(8),
country varchar2(30),
phone varchar2(50),
domain domain_address(address1, address2, address3, postcode, country));
SQL> desc t_domain2;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
ADDRESS1 VARCHAR2(50) BRENDAN.DOMAIN_ADDRESS
ADDRESS2 VARCHAR2(50) BRENDAN.DOMAIN_ADDRESS
ADDRESS3 VARCHAR2(50) BRENDAN.DOMAIN_ADDRESS
POSTCODE VARCHAR2(8) BRENDAN.DOMAIN_ADDRESS
COUNTRY VARCHAR2(30) BRENDAN.DOMAIN_ADDRESS
PHONE VARCHAR2(50)Annotations
Annotations are a new, and additional, way to add meta-data to database objects. Previously, we could add comments to tables and comments, and this feature still exists. When using comments you are restricted to a limited set of database objects, but with Annotations these can added to a wider range of database objects. While Comments consisted of free text, Annotation allow you to add extra meta-data in the for of key-value pairs. Annotations can be used to provide additional descriptions, semantic model references, additional instructions for applications, etc. There isn’t any restrictions on these, and Annotations can be added or removed at any point.
The following example will use the same table we used earlier for BOOLEAN.
drop table if exists t_bol;
create table t_bol (
c1 NUMBER annotations(SurKey 'Unique value'),
c2 BOOLEAN annotations(BOOLEAN_VALUE 'TRUE'),
c3 BOOL annotations(BOOLEAN_VALUE 'FALSE'));The data dictionary information for the Annotations can be found in USER_ANNOTATIONS_USAGE.
select * from USER_ANNOTATIONS_USAGE; select * from user_annotations_usage; OBJECT_NAME OBJECT_TYPE COLUMN_NAME ... ANNOTATION_NAME ANNOTATION_VALUE ____________ ____________ ____________ ... __________________ ___________________ T_BOL TABLE C1 SURKEY Unique value T_BOL TABLE C2 BOOLEAN_VALUE TRUE T_BOL TABLE C3 BOOLEAN_VALUE FALSE
Ora-lytics 
You must be logged in to post a comment.