UrbanPro
true

Learn PL/SQL from the Best Tutors

  • Affordable fees
  • 1-1 or Group class
  • Flexible Timings
  • Verified Tutors

Search in

New features of Oracle 23ai

Vinayak V Dabgar
22 Sep 0 0

2 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";

 

0 Dislike
Follow 1

Please Enter a comment

Submit

Other Lessons for You

Introduction to Performance tuning
What is Performance in general terms? Throughput per Input. For Eg: we keep saying, this bike has so and so much of mileage and all that. That is one of the parameters in measuring the performance of...

Oracle Architecture - Introduction
As a Oracle developer or a DBA, you should know the in & out of it’s Architecture. Let’s keep it simple first and explore later. Oracle is a software or a program, so like any other program,...

Introduction to Performance tuning
What is Performance in general terms? Throughput per Input. For Eg: we keep saying, this bike has so and so much of mileage and all that. That is one of the parameters in measuring the performance of...

Starter on exception handling
Just run these two blocks individually, observe the output and comment on what do you understand from it. SET SERVEROUTPUT ONDeclareCursor C is select * from emp;i c%rowtype;BeginFetch C into i;End; SET...

How to read and understand the Compilation errors?
Today, we will understand how to interpret the compile time erros and fix them. Don't confuse with runtime errors which is altogether a different subject called Exception Handling. Compilation erros are...

Looking for PL/SQL Classes?

Learn from Best Tutors on UrbanPro.

Are you a Tutor or Training Institute?

Join UrbanPro Today to find students near you
X

Looking for PL/SQL Classes?

The best tutors for PL/SQL Classes are on UrbanPro

  • Select the best Tutor
  • Book & Attend a Free Demo
  • Pay and start Learning

Learn PL/SQL with the Best Tutors

The best Tutors for PL/SQL Classes are on UrbanPro

This website uses cookies

We use cookies to improve user experience. Choose what cookies you allow us to use. You can read more about our Cookie Policy in our Privacy Policy

Accept All
Decline All

UrbanPro.com is India's largest network of most trusted tutors and institutes. Over 55 lakh students rely on UrbanPro.com, to fulfill their learning requirements across 1,000+ categories. Using UrbanPro.com, parents, and students can compare multiple Tutors and Institutes and choose the one that best suits their requirements. More than 7.5 lakh verified Tutors and Institutes are helping millions of students every day and growing their tutoring business on UrbanPro.com. Whether you are looking for a tutor to learn mathematics, a German language trainer to brush up your German language skills or an institute to upgrade your IT skills, we have got the best selection of Tutors and Training Institutes for you. Read more