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.
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;
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.
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; /
New Iterators and iteration controls are introduced as briefly outlined below.
Illustrated examples can be found here.
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.
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
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.
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.