Although BOOLEAN support has already been available with PL/SQL prior to this release, the BOOLEAN data type is now supported by SQL as well. This expansion of support provides improved compatibility between PL/SQL and SQL.

PL/SQL stored functions with BOOLEAN parameter types are now invokable directly from SQL. While PL/SQL functions with BOOLEAN arguments were already callable from SQL (with arguments of BOOLEAN type binds), BOOLEAN expressions and BOOLEAN literals are now supported as well. Additionally, PL/SQL stored procedures and anonymous PL/SQL blocks with host binds that expect values of the BOOLEAN type are invokable from C via OCI and other interfaces such as dynamic SQL and DBMS_SQL. It is also possible to call a C trusted or safe callout with formal parameters of BOOLEAN type.

BOOLEAN defines in the INTO and BULK COLLECT INTO clauses of a SELECT statement inside a PL/SQL block are supported.

Implicit conversions between BOOLEAN and number and character types are supported. It is possible to assign number and character variables and expressions to a BOOLEAN variable. The function to_boolean has also been added to convert from number and character types to the BOOLEAN data type. The functions to_number, to_char, and to_nchar now have BOOLEAN overloads to convert BOOLEAN values to number or character types. You can use the CAST operator to cast an expression to the BOOLEAN type as well.

To enable the support of implicit conversion, the initialization parameter PLSQL_IMPLICIT_CONVERSION_BOOL must be set to TRUE. Explicit conversions such as CAST and to_char do not depend on the parameter, so will work regardless of whether PLSQL_IMPLICIT_CONVERSION_BOOL is set to TRUE or FALSE. For more information about using PLSQL_IMPLICIT_CONVERSION_BOOL, see Oracle Database Reference.

All DML operations from PL/SQL take BOOLEAN variables in both IN and OUT binds, including array IN binds and array OUT binds. DML triggers support BOOLEAN binds (both IN and OUT binds) and BOOLEAN column references in the WHEN clause.

Pipelined table functions and polymorphic table functions (PTF) support returning columns of BOOLEAN data type.

It is possible to invoke Java and JavaScript stored procedures with parameters of BOOLEAN type from PL/SQL using call specifications with parameters of BOOLEAN type. PL/SQL procedures and anonymous blocks are also invokable from Java using JDBC in the server or client-server environment. Variables of BOOLEAN type passed using OCI, dynamic SQL, or DBMS_SQL can be passed directly as BOOLEAN.

CREATE OR REPLACE FUNCTION useBool(p1 BOOLEAN) RETURN NUMBER AS
BEGIN
    IF p1 THEN RETURN 100;
    ELSE
        RETURN 200;
    END IF;
END;
/

SET SERVEROUTPUT ON;
DECLARE
    v1 NUMBER;
    v2 BOOLEAN := TRUE;
BEGIN
    SELECT useBool(v2) INTO v1 FROM dual; --boolean argument function called from SELECT
    DBMS_OUTPUT.PUT_LINE(v1);
END;
/

100

With Oracle Database Release 23ai, PL/SQL supports the VECTOR data type along with vector operations.

The VECTOR data type is introduced to support Artificial Intelligence workloads with Oracle AI Vector Search. Along with the new data type, vector operations including distance functions are provided to manage vectors in the database. In PL/SQL, the VECTOR data type is supported as its own distinct scalar family type and can be used in the same way as any other data type in PL/SQL.

For more information about vectors in PL/SQL, see VECTOR Data Type.

For information about Oracle AI Vector Search and general support for vectors in Oracle Database, see Oracle Database AI Vector Search User's Guide.

The clauses IF NOT EXISTS and IF EXISTS are supported by CREATE, ALTER, and DROP DDL statements. They are used to suppress potential errors otherwise raised by the existence or non-existence of a given object, allowing you to write idempotent DDL scripts.

The IF NOT EXISTS clause is supported by the CREATE DDL statement to prevent errors from being thrown if an object with the given name already exists. If the object does already exist, the command is ignored and the original object remains unchanged.

On the flip side, the IF EXISTS clause suppresses errors when used with ALTER and DROP DDL statements. In the case that no object by the given name exists, the command is ignored and no object is affected by ALTER or DROP.

The use or exclusion of the clause provides you more control depending on whether you need to know if an object exists before executing a DDL statement. With this flexibility, you can determine whether you would rather have the statement ignored or have an error raised in the event the object exists (or doesn't exist).

CREATE PROCEDURE IF NOT EXISTS hello AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello there');
END;
/

CREATE PROCEDURE IF NOT EXISTS hello AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Second hello');
END;
/

SELECT TEXT FROM USER_SOURCE WHERE NAME='HELLO';

TEXT
-------------------------------------------
procedure            hello
AS BEGIN
DBMS_OUTPUT.PUT_LINE('Hello there');
END;

The simple CASE statement is extended in PL/SQL to support the use of dangling predicates and choice lists, allowing for simplified and less redundant code.

Dangling predicates are ordinary expressions with their left operands missing that can be used as a selector_value either instead of or in combination with any number of literals or expressions. With dangling predicates, more complicated comparisons can be made without requiring a searched CASE statement.

Comma separated lists of choices are now supported in the WHEN clause(s) of a simple CASE statement. They can help streamline code by allowing for the consolidation of multiple selector_value options that correspond to the same result.

The JSON constructor can now accept a PL/SQL aggregate type and return a JSON object or array populated with the aggregate type data. Conversely, the built-in function json_value now supports PL/SQL aggregate types in the RETURNING clause, mapping from JSON to the specified aggregate type.

All PL/SQL record field and collection data element type constraints are honored by json_value, including character max length, integer range checks, and not null constraints.

SQL objects and PL/SQL record type instances, including implicit records created by the %ROWTYPE attribute, are allowed as valid input to the JSON constructor. Expanded support for user defined types as input streamlines data interchange between PL/SQL applications and languages that support JSON.

The SQL Transpiler automatically and wherever possible converts (transpiles) PL/SQL functions within SQL into SQL expressions, without user intervention.

The conversion operation is transparent to users and can improve performance by reducing overhead accrued from switching between the SQL and PL/SQL runtime.

For Oracle Database 23ai, Release Update 23.7, PL/SQL Language Reference documents these new features and enhancements.