What is new in oracle 20c for developers part-2 new plsql features by lakshman bulusu

This is a part of What is new in Oracle 20c for developers blog post series.

This blog post briefly describes some new pl/sql features in Oracle 20c from a developer perspective.

New PL/SQL Features

1. PL/SQL-only attributes in Non Persistable User Defined Types

PL/SQL-only data types such as BOOLEAN and PLS_INTEGER can be used when defining object types and their instances that do not be persisted in the DB. This also applies to VARRAYS, and CREATE TYPE …… AS TABLE types. This is very useful in applications that use the OOP mode.

As an example:

CREATE TYPE new_object AS OBJECT (is_new PLS_INTEGER) NOT PERSISTABLE;

CREATE TYPE new_tab_typ AS TABLE OF (BOOLEAN) NOT PERSISTABLE;

2. PL/SQL new JSON data type

Just like in SQL, JSON can be specified as a data type in PL/SQL as a parameter type for a PL/SQL sub-program, and in PL/SQL expressions anywhere SQL functions can be specified. JSON data can be used for function over-loading when used as a parameter to such function(s) and JSON data can be interchanged between SQL and PL/SQL.

2.1 New PL/SQL object types for JSON in-memory processing are introduced

The key point to be noted here is in-memory. The new PL/SQL JSON object types are JSON_ELEMENT_T, JSON_OBJECT_T, JSON_ARRAY_T, JSON_SCALAR_T, JSON_KEY_LIST. The methods parse(), load() , and respective constructors of these object types allow construction of a textual JSON type instance to an JSON object-type instance (one of JSON_ELEMENT_T, JSON_OBJECT_T, JSON_ARRAY_T, and JSON_SCALAR_T) and the function to_json() to convert an object type instance (serialize) to a textual JSON type instance. Also, treat() for casting from JSON_ELEMENT_T to a subtype instance, get(), set(), introspection methods (is_object, is_array, is_scalar, is_string, is_number, or is_boolean)and delete() and clone() are available.

The new PL/SQL object types as stated above are constructed as transient object types, are processed in memory, and serialized using to_ string() in the object type instance. For example, using the JSON_OBJECT_T type on JSON data emp creates an in-memory instance that is processed and finally serialized to a string value that is returned as the function value. After the function execution completes, the memory allocated is released automatically as part of garbage collection.

Example 1: New PL/SQL Object Types for JSON in-memory construction and serialization

CREATE OR REPLACE FUNCTION process_sal(emp VARCHAR2) RETURN VARCHAR2
  emp_object JSON_OBJECT_T;
  emp_id     NUMBER;
  emp_sal    NUMBER;
  emp_object_size NUMBER;
 BEGIN
   emp_object := JSON_OBJECT_T.parse(emp);
   emp_object_size := emp_object.get_size;
   FOR i IN 1..emp_object_size LOOP
     emp_id := emp_object.get_Number('emp_id');
     emp_sal := emp_object.get_Number('sal');
   END LOOP;
   IF emp_id = 101 THEN
     emp_sal := emp_sal + 1200;
   END IF;
   emp_object.put('emp_id',emp_id);
   emp_object.put('sal',emp_sal);
   RETURN emp_object.to_string;
END;
/

3. New PL/SQL LOOP iterators and iterator controls

New Iterators and iteration controls are introduced as briefly outlined below.

Iterands can now be explicitly declared and accessed in the enclosing loop or inner loop (in case of nested loops), and assigned inside loop body by declaring them as MUTABLE.
Stepped Range iteration control: This generates a sequence of stepped numeric values.
Single Expression iteration control: This evaluates a single expression.
Repeated Expression iteration control: This repeatedly evaluates a single expression.
Collection Iteration Controls, namely Values Of, Indices Of, and Pairs Of: These generate all the values from a collection in sequence, all the indices from a collection in sequence, and all the index and value pairs from a collection (on two operands) respectively.
Cursor iteration control: This generates all the records from a cursor, cursor variable, or dynamic SQL.
Multiple iteration controls can be pipelined together. Stopping and skipping controls can be defined to control loop body termination and the loop body to skip some values respectively. Also dynamic SQL can be used as input control to generate values for the iteration from it.

Illustrated examples can be found here.

4. Extension of iterator choice association into PL/SQL aggregate qualified expressions

Three types of iterator choice associations are allowed to be extended into PL/SQL, namely, basic iterator choice association, index iterator choice association, and sequence iterator choice The basic iterator choice association extends by enabling a full iterator as the index. The index iterator choice association extends to specify an index expression along with the value expression. The sequence iterator choice association enables a sequence of values to be appended to the end of a collection. Illustrated examples can be found here.

5. New Machine Learning algorithms via PL/SQL API

Starting with Oracle 20c, the Oracle Data Mining functionality is renamed to Oracle Machine Learning for SQL (OML4SQL).

a) Two new machine learning algorithms, namely XGBOOST and MSET-SPRT, can be specified using the PL/SQL DBMS_DATA_MINING package. The DBMS_DATA_MINING.ALGO_XGBOOST enables data preparation for training set, build the model in-DB, and score the model for classification and regression. The DBMS_DATA_MINING.ALGO_MSET_SPRT enables model build based on existing data in-DB, and score the model on new data in-DB for anomaly detection. The number of false positives or false negatives are minimized.

b) The package DBMS_DATA_MINING also enables extensibility with custom model in R for performing classification, regression, clustering, association, attribute importance, and feature extraction. The procedures CREATE_MODEL and DROP_MODEL can used to integrate R model with OML4SQL. The MODEL_VIEW function enables to return the R model specifics. The PREDICTION and CLUSTER_ID scoring sub-programs can be used with custom R models.

c) In case of Deep Neural Network (DNN) model building

The RELU activation function can be specified via the value NNET_ACTIVATIONS_RELU for the NNET_ACTIVATIONS setting. RELU takes care of the vanishing gradient problem while building complex DNNs.
A new setting DMSSET_NN_SOLVER allows to specify a new value ‘Adam’ for optimizer parameter while compiling DNNs. The value of ‘L-BFGS’ can also be set as value to this new setting. The NNET_ITERATIONS setting in 20c has a default value for the new ‘Adam’ value optimizer setting.
A single value that is applied to each hidden layer of a DNN can be specified for the settings NNET_NODES_PER_LAYER and NNET_ACTIVATIONS.

6. New PRAGMA SUPPRESSES_WARNING_6009

Pertaining to exception handling in a PL/SQL sub-proram, if any WHEN OTHERS exception does not have an explicit RAISE statement or a call to the RAISE_APPLICATION_ERROR procedure, the PL/SQL compiler issue the PLW-06009 warning. This new pragma suppresses this warning. This the warning 06009 can be set at the session level or in individual sub-program. The below examples illustrate the use of this.

 ALTER SESSION SET PLSQL_WARNINGS='ENABLE:(6009)';
CREATE OR REPLACE PROCEDURE prc_custom_logic
IS
  PRAGMA SUPPRESSES_WARNING_6009(prc_custom_logic)
BEGIN
  DBMS_OUTPUT.PUT_LINE('In procedure prc_custom_logic');
EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('In exception');
END;
/
CREATE OR REPLACE PROCEDURE prc_call_custom_logic
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('In prc call custom logic');
EXCEPTION WHEN OTHERS THEN
    prc_custom_logic;
END;
/

In the above example, the warning 6009 is not given when compiling prc_call_custom_logic.

7. PL/SQL built-in packages enhancements

a) Oracle 20c introduced block-chain tables that enable design and development of a centralized application that enables a data secure and tamper-proof implementation from users perspective. Block-chain tables are append-only tables and deletion is either prohibited or constrained as of a point in time or just in time. A hash value is generated using special sequencing and chaining mechanisms that is stored as part of the row metadata and can be used to validate sets-of-rows participating in a block chain. PL/SQL 20c adds two new packages DBMS_BLOCK CHAIN_TABLE and DBMS_USER_CERTS that serve as API for block-chain tables and data. The DBMS_USER_CERTS package enables adding and deleting of digital certificates which come handy when adding digital signatures to a row in a block chain table. These two packages combined allow for creation of high-security data vault simulation for applications implementing block chain technology.

b) The DBMS_JSON package can now accommodate extended JSON scalar data of types double, timestamp, and float.

c) PL/SQL Advanced Queuing supports the newly introduced parsed binary JSON data type data for enqueue and dequeue operations.

d) PL/SQL DBMS_RESULT_CACHE package adds three new procedures and one function to mark objects in result cache as blacklisted. The OBJECT_BLACKLIST_ADD procedure adds an object to the blacklist, the OBJECT_BLACKLIST_REMOVE procedure takes off-lists an object from such a list, and the OBJECT_BLACKLIST_CLEAR procedure clears the blacklist. The function OBJECT_BLACKLIST returns a list of blacklisted objects in the result cache. These features com in handy when segmenting objects in the result cwache such as for result set reuse to improve performance based particular application criteria.

e) New GET_BLOCK_MAP function in DBMS_PLSQL_CODE_COVERAGE that enables obtaining the mapping of basic blocks to the corresponding PL/SQL source sub-program vis-a-vis the namespace (procedure, function, package spec, package body, type spec and type body) being passed to it. It returns information about the sub-program name, basic block number, starting line number and column number of the basic block, and a numeric indicator stating whether such a mapping can be obtained or not. This built-in procedure along with the the new function aid in implementation of robust code quality checks and the automation of the same before deployment.

f) Desupport for large object related packaged procedure DBMS_LOB.LOADFROMFILE. Instead Oracle recommends DBMS_LOB.LOADCLOBFROMFILE and DBMS_LOB.LOADBLOBFROMFILE. Also, the DBMS_OBFUSCATION_TOOLKIT package is removed in Oracle 20c and Oracle recommends to use DBMS_CRYPTO which is enhanced with latest security encryption mechanisms.