02 of the new features of Oracle 23ai (previously Oracle 23c) with respect to SQL are discussed.
1. Boolean datatype included in SQL. Previously, boolean was not part of SQL, but belonged to Oracle PL/SQL.
Demonstration:
In this case study, the table bool_demonstration is meant to store the employee number, employee name and also with a boolean column to mark a contractor employee.
CREATE TABLE bool_demonstration (
empid NUMBER,
empname VARCHAR2(100),
contractor boolean
);
INSERT INTO bool_demonstration VALUES (
'101',
'Mike',
TRUE
);
INSERT INTO bool_demonstration VALUES (
'102',
'Tom',
TRUE
);
INSERT INTO bool_demonstration VALUES (
'103',
'Joe',
0
);
INSERT INTO bool_demonstration VALUES (
'104',
'Steve',
'Y'
);
INSERT INTO bool_demonstration VALUES (
'105',
'Michel',
FALSE
);
COMMIT;
TO_BOOLEAN:
On how to convert character values/numeric values, that were used to represent boolean value datatype in SQL to boolean values:
Using TO_BOOLEAN to explicitly convert character value expressions or numeric value expressions to boolean values.
SELECT TO_BOOLEAN(0), TO_BOOLEAN('true'), TO_BOOLEAN('no');
DUAL table (Dual is not needed now, but DUAL is not deprecated, it still works).
Before Oracle 23ai, DUAL was needed:
Select 1+1 from dual;
select (2+4)/6 from dual;
Previous versions of Oracle, these select statements did not work. With Oracle 23ai, the
select statements work. (without dual). Although DUAL table still exists and is not deprecated, we dont need to use DUAL table.
a. Calculations (without Dummy dual table)
select 1+1;
select (2+4)/6 ;
b.Calling a PL/SQL function without dual:
Create a simple function to demonstrate using functions in SQL statement (with and without dual for Oracle 23ai).
CREATE OR REPLACE FUNCTION fn_cal_circle_area (
p_radius IN NUMBER
) RETURN NUMBER IS
v_area NUMBER;
c_pi CONSTANT NUMBER := 3.142;
BEGIN
v_area := c_pi * power(p_radius, 2);
RETURN ( v_area );
END;
Before Oracle 23c:
SELECT
fn_cal_circle_area(5) "CircleArea"
FROM
dual;
Now,
SELECT
fn_cal_circle_area(5) "CircleArea";
