PL/SQL Classes Fees

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

Best Practices for Removing Duplicates in PL/SQL Queries

Introduction: As a seasoned PL/SQL tutor registered on UrbanPro.com, I understand the importance of efficiently handling data duplicates in PL/SQL queries. Removing duplicates is a common requirement in database management, and there are several strategies to achieve this in PL/SQL.

Methods for Removing Duplicates:

1. Using DISTINCT Keyword:

   • The simplest way to remove duplicates is by using the DISTINCT keyword in your SELECT statement.
   • Example:
     sql

• • SELECT DISTINCT column1, column2 FROM your_table;
  • This method works well when you want distinct combinations of values in specific columns.

• Aggregate Functions:

  • Utilize aggregate functions like MIN or MAX along with the GROUP BY clause.
  • Example:
    sql
• • SELECT column1, MAX(column2) FROM your_table GROUP BY column1;
  • This approach is suitable when you need to retain specific columns and aggregate others.

• ROWID or ROWNUM:

  • Leverage the ROWID or ROWNUM pseudo-columns to uniquely identify rows.
  • Example:
    sql