Some new PL/SQL features in Oracle Database 23c

PL/SQL stands for Procedural Language for SQL. PL/SQL is Oracle’s procedural extension for SQL and is is loosely based on Ada (a variant of Pascal developed for the US Deptartment of Defense).  PL/SQL has been available in Oracle Database since version 6 (first released in 1992) while stored PL/SQL procedures/functions/packages/triggers became available with version 7. Read more here on the Origins of PL/SQL.

Let us have a look at what is new with Oracle Database 23c. There are 5 main areas:

1. The SQL Transpiler

A new init.ora parameters in Oracle Database 23c called SQL_TRANSPILER enables or disables the SQL transpiler (think of a combination of translate and compile). When this parameter is set to ON, the SQL transpiler feature is enabled and PL/SQL functions are automatically transpiled into SQL expressions whenever possible (does not work for all functions), 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.

You can change the value with either “alter system” or “alter session” and in RAC different instances can have different values. The default value is OFF and the parameter can be modified on PDB level too.

SQL> alter system set SQL_TRANSPILER=on scope=both;

System altered.

2. The BOOLEAN data type

Another new init.ora parameter called PLSQL_IMPLICIT_CONVERSION_BOOL (false is default) allows/disallows implicit conversions in PL/SQL. PLSQL_IMPLICIT_CONVERSION_BOOL allows or disallows implicit conversions in PL/SQL. Here are the data types that are converted to boolean and vice versa:

Numeric data type to BOOLEAN:

• Non-zero numeric values are implicitly converted to the BOOLEAN value TRUE

• The numeric value of 0 is implicitly converted to the BOOLEAN value FALSE

Character data type to BOOLEAN:

• Character values such as ‘true’, ‘t’, ‘yes’, ‘y’, and ‘on’ (case-insensitive) are implicitly converted to the BOOLEAN value TRUE

• Character values such as ‘false’, ‘f’, ‘no’, ‘n’, and ‘off’ (case-insensitive) are implicitly converted to the BOOLEAN value FALSE

BOOLEAN to numeric data type

• The BOOLEAN value TRUE is implicitly converted to the numeric value 1

• The BOOLEAN value FALSE is implicitly converted to the numeric value 0

BOOLEAN to character data type

• The BOOLEAN value TRUE is implicitly converted to the character value ‘TRUE’

• The BOOLEAN value FALSE is implicitly converted to the character value ‘FALSE’

When this parameter is set to FALSE, PL/SQL does not support such implicit conversions. Let us see how this works in practice, here is a small table called DBAs and let us add few Oracle ACEs (and few more non-ACEs) into it (in honor of Lionel Messi winning the Golden Ball/Ballon d’Or last night for the 6th time, 30th of October 2023):

Note that regardless of the way we inserted the boolean value for each one the result is either TRUE or FALSE when PLSQL_IMPLICIT_CONVERSION_BOOL is set to TRUE:

3. IF [NOT] EXISTS Syntax Support

In 23c, 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.

IF NOT EXISTS cannot be used in combination with OR REPLACE in commands using the CREATE DDL statement.

4. Extended CASE Controls

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 (for example > 275) 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.

Here is an example of how dangling predicates work:

The CASE statement is extended in PL/SQL to be consistent with the updated definitions of CASE expressions and CASE statements in the SQL:2003 Standard [ISO03a, ISO03b]. Dangling predicates allow tests other than equality to be performed in simple CASE operations. Multiple choices in WHEN clauses allow CASE operations to be written with less duplicated code.

Currently, the dangling predicates IS JSON and IS OF are not supported.

Here is a good blog post (with examples) by Eduardo Claro.

5. JSON Constructor and JSON_VALUE Support of PL/SQL Aggregate Types

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. There is expanded support for user defined types as input streamlines data interchange between PL/SQL applications and languages that support JSON.

Here is a simple example of using the JSON_SERIALIZE function in order to “transform” and “pretty” print a PL/SQL aggregate (we used to call it unofficially a pseudo table in Oracle 7) into JSON:

Here is how the output from the PL/SQL block above looks like:

Additionally, here is a good video explaining the new 23c feature with 4 examples:

Note finally that even from Oracle 12.1, the compilation parameter PLSQL_DEBUG is deprecated. To compile PL/SQL units for debugging, specify PLSQL_OPTIMIZE_LEVEL=1. In 23c, no features in PL/SQL Language Reference have been desupported!

The 3 new PL/SQL packages in Oracle Database 23c

Oracle Database 23c comes with 3 new (supplied) PL/SQL packages which are:

1. DBMS_HCHECK
2. DBMS_SEARCH
3. DBMS_SQL_FIREWALL

Here is a short description of what they can be used for:

1. DBMS_HCHECK is a read-only and lightweight PL/SQL package that helps you identify database dictionary inconsistencies that are manifested in unexpected entries in the RDBMS dictionary tables or invalid references between dictionary tables.

Database dictionary inconsistencies can cause process failures and, in some cases, instance crash: such inconsistencies may be exposed to internal ORA-600 errors.

DBMS_HCHECK assists you in identifying such inconsistencies and in some cases provides guided remediation to resolve the problem and avoid such database failures.

The execution reports the result as: CRITICAL: Requires an immediate fix; FAIL: Requires resolution on priority; WARN: Good to resolve; PASS: No issues.

There are 66 procedures in the package, the 2 most important being CRITICAL and FULL. Here is how to run them:

In all cases, any output reporting “problems” must be triaged by Oracle Support to confirm if any action is required.

2. The DBMS_SEARCH package can be used for indexing of multiple schema objects in a single index.

A ubiquitous search index is a JSON search index, with predefined set of preferences required for searching documents with contents in different columns, tables, and views.

You can add a set of tables and views as data sources into this index and all the columns in the specified sources are indexed, and available for a full-text and range-based search. In the example below I am using 2 tables called: DBA_ACE ad CONTRIBUTIONS:

This procedure combines the contents from all columns of the DBA_ACE and CONTRIBUTIONS tables into a single index table named DBAS.

View the virtual indexed doc using the following command: select DBMS_SEARCH.GET_DOCUMENT(‘DBAS’,METADATA ) from DBAS;

Here is an example on how to search multiple objects using ubiquitous search index.

3. DBMS_SQL_FIREWALL can be used to prevent SQL injection attacks.

SQL Firewall implements database firewall features such as allow-listing, deny-listing, and object- and command-based access control inside the Oracle Database kernel.

To implement SQL Firewall, the following features are available:

1. New PL/SQL package, DBMS_SQL_FIREWALL

2. New system privilege, ADMINISTER FIREWALL

3. A set of data dictionary views, DBA_SQL_FIREWALL_*

4. Two roles: SQL_FIREWALL_ADMIN  and SQL_FIREWALL_VIEWER

Oracle recommends that you periodically purge capture or violations logs by using the DBMS_SQL_FIREWALL.PURGE_LOG procedure as part of routine SQL Firewall management tasks. In a well trained environment, violation logs are not expected to be voluminous. Here are details on how to configure the SQL Firewall.

Note: There are now about 1100 supplied packages in the Oracle databases (depending on if we count the ones without a BODY too). DBMS_HCHECK and DBMS_SQL_FIREWALL are available only in 23.2

The 10+6 Oracle PL/SQL pragmas

“What the pragmatist has his pragmatism for is to be able to say, Here is a definition and it does not differ at all from your confusedly apprehended conception because there is no practical difference.” ― Charles Sanders Peirce

The word pragma origins from the ancient Greek word πρᾶγμα (prâgma) and means “a thing done, a fact”.

In the Oracle procedural language PL/SQL, the concept of PRAGMA refers to a compiler directive and is used to give an instruction to the compiler. In other words, a pragma is a compiler directive that tells Oracle to use rules (other than the default rules) for the object.

PRAGMA are processes at compile time and not run time. They do not affect the code and the outcome of a program, they simply convey information to the compiler.

Several websites list mostly 5 PL/SQL pragmas, but actually they are more. Some are documented (10), some are not (6). Here is (most likely) the complete list:

1. The AUTONOMOUS_TRANSACTION pragma marks a routine as autonomous; that is, independent of the main transaction. This is the most commonly used and popular pragma.

In this context, a routine is one of these:

• Schema-level (not nested) anonymous PL/SQL block
• Standalone, package, or nested subprogram
• Method of an ADT
• Noncompound trigger

2. The COVERAGE pragma marks PL/SQL code which is infeasible to test for coverage. These marks improve coverage metric accuracy. The COVERAGE pragma marks PL/SQL source code to indicate that the code may not be feasibly tested for coverage. The pragma marks a specific code section. Marking infeasible code improves the quality of coverage metrics used to assess how much testing has been achieved. Here is an example.

3. The DEPRECATE pragma marks a PL/SQL element as deprecated. The compiler issues warnings for uses of pragma DEPRECATE or of deprecated elements. The associated warnings tell users of a deprecated element that other code may need to be changed to account for the deprecation. Here is a good example of how it works.

4. The EXCEPTION_INIT pragma associates a user-defined exception name with an error code. The EXCEPTION_INIT pragma can appear only in the same declarative part as its associated exception, anywhere after the exception declaration.

5. The INLINE pragma specifies whether a subprogram invocation is to be inlined. Inlining replaces a subprogram invocation with a copy of the invoked subprogram (if the invoked and invoking subprograms are in the same program unit).

6. The RESTRICT_REFERENCES pragma asserts that a user-defined subprogram does not read or write database tables or package variables. The RESTRICT_REFERENCES pragma is deprecated. Oracle recommends using DETERMINISTIC and PARALLEL_ENABLE instead of RESTRICT_REFERENCES.

7. The SERIALLY_REUSABLE pragma specifies that the package state is needed for only one call to the server (for example, an OCI call to the database or a stored procedure invocation through a database link). Do not confuse here OCI with Oracle Cloud Infrastructure! Here it means Oracle Call Interface. After this call, the storage for the package variables can be reused, reducing the memory overhead for long-running sessions. This pragma is appropriate for packages that declare large temporary work areas that are used once in the same session.

The SERIALLY_REUSABLE pragma can appear in the declare_section of the specification of a bodiless package, or in both the specification and body of a package, but not in only the body of a package.

8. The UDF pragma tells the compiler that the PL/SQL unit is a user defined function that is used primarily in SQL statements, which might improve its performance.

9. The next pragma is GoldenGate related. Procedures and packages with the pragma UNSUPPORTED stop apply at the point of procedure invocation so that manual intervention can be taken. Check the list of the packages and procedures that are pragma constructs for replication. Any package or procedure not in this list is not considered a pragma construct for PL/SQL replication and is equivalent to pragma NONE. Note also the 4 pragma options for procedures: AUTO, MANUAL, UNSUPPORTED, and NONE.

10. The latest one (available from Oracle 21c), SUPPRESSES_WARNING_6009 pragma marks a subroutine to indicate that the PLW-06009 warning is suppressed at its call site in an OTHERS exception handler. The marked subroutine has the same effect as a RAISE statement and suppresses the PLW-06009 compiler warning.

The OTHERS exception handler does not issue the compiler warning PLW-06009 if an exception is raised explicitly using either a RAISE statement or the RAISE_APPLICATION_ERROR procedure as the last statement. Similarly, a call to a subroutine marked with the SUPPRESSES_WARNING_6009 pragma, from the OTHERS exception handler, does not issue the PLW-06009 warning.

It is worth checking the example from the link above, not that first you need to run ALTER SESSION SET PLSQL_WARNINGS='ENABLE:(6009)';

And here are the 6 undocumented pragmas:

• PRAGMA BUILTIN defines SQL built-in functions and operators and is an internal pragma being used in the sys.standard package
• PRAGMA FIPSFLAG is another internal pragma used in sys.standard and most likely has something to do with FIPS from NIST
• PRAGMA INTERFACE is gateway for internal functions to C libraries and is used in the sys.standard package specification
• PRAGMA NEW_NAMES is another internal pragma that restricts the use of particular new entries in sys.standard
• PRAGMA SUPPLEMENTAL_LOG_DATA is used by Oracle Locator/Spatial and/or Logminer – I am not 100% sure
• PRAGMA TIMESTAMP sets/modifies the timestamp values of SYS packages

Unified Auditing for Top-Level Statements in the Oracle Database

Auditing a database has undoubtfully its benefits and more, but for the DBA, this can cause serious headaches in terms of managing space and performance. Especially in the cloud, where security and auditing is even more important that on premises.

Did you know that there are 239 available audit actions in 19c? select * from AUDIT_ACTIONS; shows them all.

A new database feature introduced in 19c and 21c, called “auditing top-level statements“, can help us minimize the issue with space and performance (caused by auditing), by ignoring SQL statements that run from within PL/SQL procedures, triggers or functions, as they may be less relevant for auditing purposes.

Inside the Oracle database, a top-level statement is a statement that is executed directly by a user, not a statement that is run from within a PL/SQL procedure.

Top-level statements from the user SYS can be also audited. The unified audit trail can grow immensely if all statements are audited. Especially when there is a large number of audit trail records that are generated for a single statement in the unified audit policy. With auditing only top-level statements, we can reduce the audit of the recursive SQL statements. An example of this scenario would be audits for the DBMS_STATS.GATHER_DATABASE_STATS statement, which can generate over 200,000 individual audit records.

The unified audit records are written to a table in the AUDSYS schema called AUD$UNIFIED. Access to the AUDSYS. AUD$UNIFIED table is provided via the view SYS.UNIFIED_AUDIT_TRAIL. Here is the size of AUD$UNIFIED in an autonomous database where I have not configured any out of the box auditing. The auditing options enabled by default in ADB are logon, alter user, insert, update and change password:

Note that there are two components to the Audit Trail: (1) the AUDSYS schema in the database and (2) the spillover files on disk. More details in Receiving ORA-55940: An error occurred during execution of ktliIngestExternData by Mike Dietrich.

The X$UNIFIED_AUDIT_TRAIL table is owned by SYS, it is not in the SYSAUD schema! Check Unified Auditing – some insights by Thomas Saviour and X$UNIFIED_AUDIT_TRAIL is slow by Magnus Johansson for more details.

Now, configuring an Unified Audit Policy to capture only Top-Level statements is simple:

The ONLY TOPLEVEL clause in the CREATE AUDIT POLICY statement enables you to audit only the SQL statements that are directly issued by an end user by honoring the audit configuration in the audit policy.

In my autonomous database, I have decided to top-level audit all actions from SYS and ADMIN:

Here are other examples of auditing Top-Level statements.

It is important to understand that the ONLY TOPLEVEL clause has no impact on the output for an individual unified audit trail record. The only effect that ONLY TOPLEVEL has on a policy is to limit the number of records generated for the given unified audit policy.

P.S. 10 years go I wrote a blog post entitled Auditing vs. Performance in the Oracle Database. This topic is after all still relevant and important. With a colleague of mine from Australia, about a month ago, we could not figure out why auditing was causing performance issues – after unifying all possible efforts, not records 🙂 The problem could be only reproduced on Exadata. Still a mystery to us.

Running JavaScript from within the Oracle Database using DBMS_MLE

JavaScript is by far the most used language according to Github’s Octoverse Report.

As of 2021 JavaScript is the most commonly used programming language among software developers around the world, with nearly 65 percent of respondents stating that they used JavaScript. HTML/CSS, Python, SQL, and Java rounded out the top five most widely used programming languages around the world.

Did you know that you can run JavaScript code from right inside the Oracle Database?

By using the new 21c PL/SQL multilingual engine package called DBMS_MLE, we can now run JavaScrips from within the Oracle database.

The new package has 18 procedures and 1 function. So, it might take some time to understand what they do and how they should be used.

As of today, it is not possible to create stored JavaScript procedures in the database. But what we can do for now, is store them in a CLOB column in a table and create an executable PL/SQL procedure which will select the CLOB, i.e., the JavaScript procedure, and execute it via DBMS_MLE.EVAL

We cannot only run JavaScript code inside the Oracle Database but also exchange data seamlessly between PL/SQL and JavaScript. The JavaScript code itself can execute PL/SQL and SQL through built-in JavaScript modules. JavaScript data types are automatically mapped to Oracle Database data types and vice versa.

Well, here is a simple example of how to run JavaScript from the database:

The user of DBMS_MLE must have EXECUTE DYNAMIC MLE and EXECUTE ON JAVASCRIPT otherwise you will get again ORA-01031.

For further and more detailed examples, check Lucas Jellema‘s and Stefan Dobre‘s articles.

One might wonder, why run JavaScript code from within Oracle?

Actually, there are several reason why we would want to run JavaScript from within the Oracle database.

Sean Stacey outlined 3 reasons in his blog post How to Run JavaScript In Oracle Database 21c:

1. Use JavaScript with your APEX applications: Stefan Dobre wrote an excellent blog post entitled JavaScript as a Server-Side Language in Oracle APEX 20.2
2. Use your existing JavaScript programs and run them directly against your Oracle database without having to worry about re-writing the logic in PL/SQL. There are about 100K reusable JS libraries that can be leveraged.
3. Not to use the Oracle database simply as a bag of tables with data. There is no need to move the data out of the database to run your JavaScript code against it.

There are several other reasons as well. There are more software developers who are and interested in writing JS code compared to PL/SQL. R and Java are already part of the Oracle database, so it is natural that JavaScript is being embedded as well. Naturally, most likely Python, Ruby, etc. are also in the pipeline.

As a side note (on the humorous side), here is a Twitter screenshot for the ones watching on Nextflix “Emily in Paris” 🙂

Two important additional resources:

Oracle Database Multilingual Engine (MLE): GraalVM in the Database

Executing JavaScript from PL/SQL in Oracle Database 21c with Multi Language

Oracle JavaScript Extension Toolkit (Oracle JET)

 

5 new core DBA features in Oracle Database 21c

If wondering what new can come after so many releases of the Oracle Database – well, here is something for system DBAs.

Here are 5 new features related to Database Vault, Syslog, the new SYS_AUTO_STS_MODULE, and the 2 new DBA packages SYS.DBMS_FLASHBACK_ARCHIVE_MIGRATE & SYS.DBMS_ACTIVITY.

1. No need any longer to disable Oracle Database Vault in every container before the upgrade! We only need to grant the DV_PATCH_ADMIN role to SYS commonly before performing the upgrade. After the upgrade is complete the Database Vault controls work as before. Then we can revoke the DV_PATCH_ADMIN role from SYS. Alternatively, we can explicitly disable Oracle Database Vault in all containers before the upgrade and then after the upgrade explicitly enable Oracle Database Vault in all the containers.

2. A new concept in the Oracle Database is the Object Activity Tracking System.

Object Activity Tracking System (OATS) tracks the usage of various types of database objects. Usage includes operations such as access data manipulation or refresh.

The DBMS_ACTIVITY PL/SQL package contains functions and procedures for configuring Object Activity Tracking System (OATS) information collection and management. Data collected by OATS is used in analyses performed by automatic materialized views.

DBAs can use the DBMS_ACTIVITY.CONFIGURE procedure to control three OATS parameters within a specific database.

ACTIVITY_INTERVAL defines the interval between snapshots:

exec dbms_activity.configure('ACTIVITY_INTERVAL_MINUTES',30);

ACTIVITY_RETENTION_DAYS defines how long snapshots are saved:

exec dbms_activity.configure('ACTIVITY_RETENTION_DAYS',60);

ACTIVITY_SPACE_PERCENT sets how much of available space is reserved for snapshots:

exec dbms_activity.configure('ACTIVITY_SPACE_PERCENT',10);

And use these tables to monitor the activity:

ACTIVITY_CONFIG$
ACTIVITY_TABLE$
ACTIVITY_MVIEW$
ACTIVITY_SNAP_META$

3. SYSLOG destination for common Unified Audit Policies:

Certain predefined columns of unified audit records from common unified audit policies can be written to the UNIX SYSLOG destination.

To enable this new feature we should set UNIFIED_AUDIT_COMMON_SYSTEMLOG which is a new CDB level init.ora parameter. This enhancement enables all audit records from common unified audit policies to be consolidated into a single destination. This feature is available only on UNIX platforms not Windows (of course).

The new parameter has no default, we should set both the facility_clause and the priority_clause values.

Only a subset of unified audit record fields are written to ensure that the audit record entries do not exceed the maximum allowed size for a SYSLOG entry (typically 1024 bytes).

4. SYS_AUTO_STS_MODULE is the new module for the Auto SQL Tuning Sets. The feature exists since Oracle 19.7 so technically not really a new 21c feature although listed as such. Check Automatic SQL Tuning Sets (ASTS) 19c RU 19.7 Onwards (Doc ID 2686869.1)

If you try searching Google or MOS for SYS_AUTO_STS_MODULE, then most likely you will get similar to what I got from my search:

Clearly, Franck Pachot has already met with the auto SQL tuning set module but that is expected behavior – he is one of the pioneers in new features and database research.

First, what is the Automatic SQL Tuning Set? In short, auto STS is an automatic repository for historic SQL performance metrics and execution plans. Oracle is now having the module handling all that automatically.

Recently, Doc ID 2733254.1 shows after a search for the SYS_AUTO_STS_MODULE but the note is about a hang when creating text indexes although SYS_AUTO_STS_MODULE pops up 3 times under SQL ordered by CPU Time.

Out of curiosity, you may run this SQL also against your 21c database and observe the actions the module has been taking:

select ACTION, count(*) 
from V_$SQLAREA_PLAN_HASH 
where module = 'SYS_AUTO_STS_MODULE' 
group by ACTION;

In a real production database (ADW), I noticed that after the module stopped appearing in the AWR report, the performance went back to normal:

5. DBMS_FLASHBACK_ARCHIVE_MIGRATE

A new PL/SQL package called DBMS_FLASHBACK_ARCHIVE_MIGRATE enables the migration of Flashback Data Archive enabled tables from a database on any release (in which the package exists) to any database on any release (that supports Flashback Data Archive).

The package has 3 procedures:

– EXPORT exports the given Flashback Archive enabled base tables, their history and related tablespaces
– EXPORT_ANALYZE analyzes the given Flashback Archive enabled base tables, their history, and related tablespaces for self containment using Transportable tablesapce checks
– IMPORT imports the Flashback Archive enabled base tables that were exported, their history, and related tablespaces

Here are the prerequisites:

Database version >= 11.2
If database version is 11.2, following conditions should be met:
shared_pool_size >= 500M
streams_pool_size >= 40M or
sga_target >= 2G

The DBMS_FLASHBACK_ARCHIVE_MIGRATE package must be compiled on both the source and target databases as SYS. The source file is located at ?/rdbms/admin/crefbamig.sql, using which the package can be created or compiled.

The export and import procedures must be executed as SYS. Since the package uses DBMS_DATAPUMP, DBMS_LOCK, DBMS_SYSTEM, DBMS_SQL and DBMS_SCHEDULER PL/SQL packages, their security models are also applicable.

More on 21c? Check:

1. Introducing Oracle Database 21c
2. A glimpse of what is new in Oracle Database 21c

On Kafka, JSON, PL/SQL and Advanced Queuing in Oracle Database 20c

Oracle Corp. starts today (June 1st, 2020) a new fiscal year as the Oracle Database is slowly moving towards version 20c (still in preview mode only) which comes with almost 500 init.ora parameters and 5326 hidden/underscore parameters. There is a new one for 20c called kafka_config_file. But let us first take a step back and see its connection with Advanced Queuing.

Advanced Queuing is available in all editions of Oracle database, including XE. Since Oracle Advanced Queuing is implemented in database tables, all the operational benefits of high availability, scalability, and reliability are applicable to queue data.

Advanced Queuing can be accessed through the several interfaces: PL/SQL, Visual Basic, Java, Java Message Service, JDBC, ODP.NET, OCI (do not read Oracle Cloud Infrastructure – think of C language), etc.

Using PL/SQL to access Oracle Advanced Queuing is probably the most common method: using the PL/SQL packages DBMS_AQADM and DBMS_AQ.

Reading all that, one might think of Kafka. Kafka is a distributed, partitioned, replicated commit log service providing the functionality of a messaging system, but with a unique design. The aim here is not position Kafka and Oracle AQ against each other but show what is new in Oracle Database 20c that brings Kafka and Oracle together and what is important from DBA point of view.

Todd Sharp explained recently how to use Kafka Connect wth Oracle Streaming Service and Autonomous Database. Let us now see what is new in Oracle Database 20c:

Oracle Database 20c introduces Transactional Event Queues (TEQ), which are partitioned message queues that combine the best of messaging, streaming, direct messages, and publish/subscribe. TEQ operates at scale on the Oracle database. TEQ provides transactional event streaming, and runs in the database in a scale of 10s to 100s of billions of messages per day on 2-node to 8-node Oracle RAC databases, both on-premise and on the cloud. TEQ has Kafka client compatibility, which means, Kafka producer and consumer can use TEQ in the Oracle database instead of a Kafka broker. Check Advanced Queuing in 20c for more details.

1. Advanced Queuing: Kafka Java Client for Transactional Event Queues

Kafka Java Client for Transactional Event Queues (TEQ) enables Kafka application compatibility with Oracle database. This provides easy migration of Kafka applications to TEQ.

You do not have to manage a separate Kafka infrastructure and this new feature simplifies the event-driven application architectures with an Oracle converged database that now includes events data. Starting from Oracle Database 20c, Kafka Java APIs can connect to Oracle database server and use Transactional Event Queues (TEQ) as a messaging platform. Developers can migrate an existing Java application that uses Kafka to the Oracle database. A client side library allows Kafka applications to connect to Oracle database instead of Kafka cluster and use TEQ messaging platform transparently.

Two levels of configuration are required to migrate Kafka application to TEQ messaging platform:

– Database level configuration
– Application level configuration

Kafka application needs to set certain properties which will allow OKafka library to locate the Oracle Database. This is analogous to how Kafka application provides zoo keeper information. These connection properties can be set in the following two ways:

– using database user and password provided in plain text
– using JDBC wallet

The following are the prerequisites for configuring and running Kafka Java client for TEQ in an Oracle Database. Create a database user. Grant the following user privileges:

grant connect, resource to user;
grant execute on dbms_aq to user;
grant execute on dbms_aqadm to user;
grant execute on dbms_aqin to user;
grant execute on dbms_aqjms to user;
grant select_catalog_role to user;

Next, set the correct database init.ora parameter to use TEQ:

streams_pool_size=512M

Set the local listener too:

LOCAL_LISTENER= (ADDRESS=(PROTOCOL=TCP)(HOST= )(PORT=))

2. Advanced Queuing Support for JSON Data Type and PL/SQL

Oracle Database Advanced Queuing now supports the JSON data type.

Many client application and micro-services which use Advanced Queuing for messaging have better performance if they use JSON data type to handle JavaScript Object Notation (JSON) messages.

In this aspect, PUBLIC is granted EXECUTE privilege on all these types:

AQ$_AGENT, AQ$_AGENT_LIST_T, AQ$_DESCRIPTOR, AQ$_NTFN_DESCRIPTOR, AQ$_NTFN_MSGID_ARRAY, AQ$_POST_INFO, AQ$_POST_INFO_LIST, AQ$_PURGE_OPTIONS_T, AQ$_RECIPIENT_LIST_T,
AQ$_REG_INFO, AQ$_REG_INFO_LIST, AQ$_SUBSCRIBER_LIST_T, DEQUEUE_OPTIONS_T, ENQUEUE_OPTIONS_T, QUEUE_PROPS_T, SEEK_INPUT_T, , EK_OUTPUT_T, SYS.MSG_PROP_T, MESSAGE_PROPERTIES_T, MESSAGE_PROPERTIES_ARRAY_T, MSGID_ARRAY_T

Regarding the new features of PL/SQL in 20c, check first the interesting example of Steven Feuerstein on extension of loop iterators.

Check the New Features in Release 20c for Oracle Database PL/SQL for more details on PL/SQL extended iterators, PL/SQL qualified expressions enhancements, SQL macros, the new JSON data type and the new pragma SUPPRESSES_WARNING_6009.

3. Advanced Queuing: PL/SQL Enqueue and Dequeue Support

The following features are new in this release:

– Kafka Java Client for Transactional Event Queues (TEQ) which enables Kafka application compatibility with Oracle Database and thus providing easy migration of Kafka applications to TEQ
– PL/SQL Enqueue and Dequeue Support for JMS Payload and non-JMS Payload in Transactional Event Queues
– Transactional Event Queues for Performance and Scalability
– Simplified Metadata and Schema in Transactional Event Queues
– Support for Message Retention and Seekable Subscribers
– Advanced Queuing Support for JSON Data Type

For all the details, check the Changes in Oracle Database Advanced Queuing Release 20c.

In terms of performance and scalability, Oracle Transactional Event Queues have their Queue tables partitioned in 20c into multiple Event Streams which are distributed across multiple RAC nodes for high throughput messaging and streaming of events.

Remember that in 10.1, AQ was integrated into Oracle Streams and thus Oracle AQ was called “Oracle Streams AQ”. But in 12.1, Oracle Streams got deprecated and AQ was again named just “Oracle AQ”.

And finally: here is the 546 page long Transactional Event Queues and Advanced Queuing User’s Guide along with few good additional articles:

Oracle + Kafka = Better Architecture by Jonathan Wallace
Streaming data from Oracle into Kafka by Robin Moffatt
Extending Oracle Streaming with Kafka Compatibility by Somnath Lahiri

The DBMS_CLOUD and UTL_SMTP packages in the Autonomous Database

New Autonomous Database features are being added all the time. For now, ADB for shared infrastructure supports 18c while 19c can be used only in preview mode.

The preview period for ADB 19c ended yeaterday: January 15th, 2020. From now on, it is no longer possible to provision new preview instances nor clone existing instances to a preview instance. However, existing preview instances will remain available until January 30th when the final termination process will happen. And now, we are awaiting for the Oracle 20c preview version.

So, what else is new in the Autonomous Database (Shared Infrastructure):

The DBMS_CLOUD REST API functions provide a generic API that lets you call any REST API with the following supported cloud services:

Oracle Cloud Infrastructure
Amazon Web Services
Azure Cloud
Oracle Cloud Infrastructure Classic

DBMS_CLOUD supports GET, PUT, POST, HEAD and DELETE HTTP methods. The REST API method to be used for an HTTP request is typically documented in the Cloud REST API documentation.

Check also the summary of the DBMS_CLOUD_ADMIN package and especially the CREATE_DATABASE_LINK procedure as this is the supported way to create a database link in ADB.

To run DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK with a user other than ADMIN you need to grant EXECUTE and CREATE DATABASE LINK privileges to that user. For example, run the following command as ADMIN to grant privileges to JULIAN:

GRANT EXECUTE ON DBMS_CLOUD_ADMIN TO JULIAN;
GRANT CREATE DATABASE LINK TO JULIAN;

Behind the curtains, Oracle runs C##CLOUD$SERVICE.DBMS_CLOUD_DBLINK_INTERNAL.

Note that packages like DBMS_CLOUD, DBMS_CLOUD_ADMIN, DBMS_CLOUD_CORE, DBMS_CLOUD_DBLINK, etc. are owned by C##CLOUD$SERVICE (owns 23 packages), not by SYS!

In ADB, there are few restrictions for some PL/SQL packages. Oracle have removed the UTL_TCP package.

UTL_HTTP Restrictions:

Connections through IP addresses are not allowed
– Only HTTPS connections are allowed (HTTP and HTTP_PROXY are disallowed)
– The only allowed ports are 443 and 8443
– The SET_WALLET and SET_AUTHENTICATION_FROM_WALLET APIs are disallowed
– The WALLET_PATH and WALLET_PASSWORD arguments for the CREATE_REQUEST_CONTEXT, REQUEST, and REQUEST_PIECES APIs are ignored

UTL_SMTP Restrictions:

– The only supported email provider is Oracle Cloud Infrastructure Email Delivery service
– Mail with an IP address in the host name is not allowed
– The only allowed ports are 25 and 587

DBMS_NETWORK_ACL_ADMIN Restrictions:

– Granting ACL privileges on IP addresses is not allowed
– The http_proxy and use_passwords ACL privileges are not allowed

This Oracle example is using DBMS_CLOUD.SEND_REQUEST in order to create and delete an Oracle Cloud Infrastructure Object Storage bucket.

Here are 2 new additions to the ADB feature list:

1. Access Control Lists Honored By ADB Built-in Tools

You can control and restrict access to your Autonomous Database by setting network access control lists (ACLs). When you provision your Autonomous Database you can either choose the database to be accessible from all IP addresses or you can restrict access to your database to a whitelisted set of clients. You can change your decision after provisioning and set or change the access rules.

Here are 3 links for additional details:

1. VCNs and Subnets for details on Virtual Cloud Networks (VCN).
2. Oracle Services: Service Gateway Access for details on setting up a Service Gateway.
3. Transit Routing: Private Access to Oracle Services for details on Transit Routing.

2. Send Emails from ADB using UTL_SMTP

There are 4 steps you must follow in order to send an email from ADB:

1. Configure Email Delivery Service
2. Allow SMTP Access for ADMIN via an Access Control Entry (ACE)
3. Create a PL/SQL Procedure to Send Email
4. Send a Test Email

Check How to Send an Email using UTL_SMTP in Autonomous Database by Can Tuzla for all the details and examples.

At the end, note that now we have the “Next Maintenance” field which shows the date and time for the upcoming maintenance window. All ADB instances are automatically assigned to a maintenance window and different instances can have different maintenance windows.

PL/SQL quiz

Years ago, I saw this quiz on dbasupport. Below, we have 2 PL/SQL blocks, have a look:

BEGIN
  WHILE sysdate = sysdate LOOP
    NULL;
  END LOOP;
END;
/

DECLARE
  x DATE;
BEGIN
  LOOP
    BEGIN
      SELECT null INTO x FROM dual WHERE sysdate = sysdate;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN EXIT;
    END;
  END LOOP;
END;
/

Question is: what happens after you run them? Are the loops above both finite, both infinite or is it so that one of them is finite and the other one infinite?

If you cannot answer the question just run them in SQL*Plus, etc. Then, try to explain why – the reason for being finite or infinite.

I will update this blog post after month or so with the answer.

And I have just run this against ATP (~18c) but the output is same in previous versions too:

with SALES as (select /*+ materialize */ 0/0 from DUAL)
select count(*) from SALES;

ORA-01476: divisor is equal to zero
01476. 00000 -  "divisor is equal to zero"
*Cause:
*Action:

with SALES as (select /*+ inline */ 0/0 from DUAL)
select count(*) from SALES;

  COUNT(*)
----------
         1

Updated on October 31st:

As you can see from Eugen Iacob’s answer/comments below, the answer is read consistency. The first loop is finite, the second is infinite. Well done Eugen!

In SQL sysdate is evaluated only once – unlike in PL/SQL.

DBA Statements

“A statement is persuasive and credible either because it is directly self-evident or because it appears to be proved from other statements that are so.” Aristotle

In Oracle 12.2, there is a new view called DBA_STATEMENTS. It can helps us understand better what SQL we have within our PL/SQL functions, procedures and packages.

There is too little on the Internet and nothing on Metalink about this new view:

PL/Scope was introduced with Oracle 11.1 and covered only PL/SQL. In 12.2, PL/Scope was enhanced by Oracle in order to report on the occurrences of static and dynamic SQL call sites in PL/SQL units.

PL/Scope can help you answer questions such as:
– Where and how a column x in table y is used in the PL/SQL code?
– Is the SQL in my application PL/SQL code compatible with TimesTen?
– What are the constants, variables and exceptions in my application that are declared but never used?
– Is my code at risk for SQL injection and what are the SQL statements with an optimizer hint coded in the application?
– Which SQL has a BULK COLLECT or EXECUTE IMMEDIATE clause?

Details can be found in the PL/Scope Database Development Guide or at Philipp Salvisberg’s blog.

Here is an example: how to find all “execute immediate” statements and all hints used in my PL/SQL units? If needed, you can limit the query to only RULE hints (for example).

1. You need to set the PLSCOPE_SETTINGS parameter and ensure SYSAUX has enough space:

SQL> SELECT SPACE_USAGE_KBYTES FROM V$SYSAUX_OCCUPANTS 
WHERE OCCUPANT_NAME='PL/SCOPE';

SPACE_USAGE_KBYTES
------------------
              1984

SQL> show parameter PLSCOPE_SETTINGS

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
plscope_settings                     string      IDENTIFIERS:NONE

SQL> alter system set plscope_settings='STATEMENTS:ALL' scope=both;

System altered.

SQL> show parameter PLSCOPE_SETTINGS

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
plscope_settings                     string      STATEMENTS:ALL

2. You must compile the PL/SQL units with the PLSCOPE_SETTINGS=’STATEMENTS:ALL’ to collect the metadata. SQL statement types that PL/Scope collects are: SELECT, UPDATE, INSERT, DELETE, MERGE, EXECUTE IMMEDIATE, SET TRANSACTION, LOCK TABLE, COMMIT, SAVEPOINT, ROLLBACK, OPEN, CLOSE and FETCH.

-- start
SQL> select TYPE, OBJECT_NAME, OBJECT_TYPE, HAS_HINT, 
SUBSTR(TEXT,1,LENGTH(TEXT)-INSTR(REVERSE(TEXT), '/*') +2 ) as "HINT" 
from DBA_STATEMENTS
where TYPE='EXECUTE IMMEDIATE' or HAS_HINT='YES';

TYPE              OBJECT_NAME   OBJECT_TYPE  HAS HINT
----------------- ------------- ------------ --- ------------------
EXECUTE IMMEDIATE LASKE_KAIKKI  PROCEDURE    NO  
SELECT            LASKE_KAIKKI  PROCEDURE    YES SELECT /*+ RULE */

-- end

Check also the DBA_STATEMENTS and ALL_STATEMENTS documentation. And the blog post by Jeff Smith entitled PL/Scope in Oracle Database 12c Release 2 and Oracle SQL Developer.

But finally, here is a way how to regenerate the SQL statements without the hints:

-- start
SQL> select TEXT from DBA_STATEMENTS where HAS_HINT='YES';

TEXT
------------------------------------------------------------
SELECT /*+ RULE */ NULL FROM DUAL WHERE SYSDATE = SYSDATE

SQL> select 'SELECT '||
TRIM(SUBSTR(TEXT, LENGTH(TEXT) - INSTR(REVERSE(TEXT), '/*') + 2))
as "SQL without HINT"
from DBA_STATEMENTS where HAS_HINT='YES';

SQL without HINT
-------------------------------------------------------------
SELECT NULL FROM DUAL WHERE SYSDATE = SYSDATE

-- end