Oracle Database 23ai VirtualBox 23.7 & DBMS_DEVELOPER

• Download the VM
• Read about the new features in 23.7

A quick peek at some new features and improvements

AI Vector Search (23.7 New Features Docs)

Maths and Aggregate functions now supported for VECTORs –

DBMS_DEVELOPER.GET_METADATA (23.7 New Features Docs)

You know about the DBMS_METADATA package, it’s quite handy. We use it internally here to do things like make data pump work and generating DDL for objects, so you can see the SQL behind your TABLE in tools like SQL Developer and SQLcl.

It has a couple of limitations, from the developer perspective.

• it’s a bit slow, esp on the first request/execution
• the privilege model requires DBA’ish level access

So in 23.7 we have a new package, DBMS_DEVELOPER!

For the initial release, it has as single FUNCTION, GET_METADATA. However, I expect this package to have lots and lots of goodies added over time, so keep an eye out! If you have ideas for features to add to this package, feel free to leave a comment here.

Let’s try it out!

What’s it do? Well, it will tell you a LOT of information about your database object. You’re used to seeing this information in the form of reports or screens in tools like SQL Developer or APEX.

Simpler Security Model

I’m logged in as HR, which has very low or minimal level privs.

Using a DBA account, I’m going to grant READ & SELECT on SH.SALES to HR. Quick sidebar: we introduced the READ privilege in 12.1. Have you started using it? It allows a database user to query a table WITHOUT having the ability to also LOCK the object. Tim shows you how this works on his Oracle-Base site.

Let’s make sure the GRANT has ‘worked.’ by querying SH.SALES as HR.

Great, now let’s use DBMS_METADATA to get the DDL for the SALES table.

It’s not working, we get a ORA-31603, as the package requires higher level access than the READ or SELECT on the object permits.

Alright, let’s try our new DBMS_DEVELOPER package.

It works, we get a JSON object, with lots of data, but did you see how fast it is?

Compare this to DBMS_METADATA and asking for the DDL on a local table, HR.EMPLOYEES, where the security model problem becomes moot –

That’s about…20,000% slower. Our tools (SQL Developer, SQLcl, …) can now leverage this new interface to get interesting information, including the create table ddl, on objects to developers faster, and without DBA level privs (or SELECT CATALOG).

Rich, Useful Metadata

Let’s look at the output. Well, that’s going to be challenge, we have 300+ lines of pretty-print formatted JSON information about our TABLE! But I’ll try anyway 🙂

It’s broken down into several categories:

• objectInfo (columns, object level stats)
• indexes
• constraints

And this this is JUST the ‘typical’ level of metadata for our object!

If I run the function again, and this time as for the ‘ALL’ level of detail, you can see for the columns there are an additional 6 or so attributes, basically the column level stats.

Summary

• the package is FAST
• the information is readily accessible for developers
• the amount of information is incredible

If you’re building utilities or services that require information for database objects in your Oracle Database 23ai (and higher) system, the DBMS_DEVELOPER package will quickly become your best friend.