23ai introduces dangling predicates as a new feature for CASE statements. Per the Oracle documentation they extend the simple case structure, allowing you to match WHEN clauses for conditions other than single values. That is, prior to 23ai, you could write a CASE statement like this: CASE p_value WHEN 1 then 'White' WHEN 2 then… The post 23ai PL/SQL Dangling Predicates in CASE expressions first appeared on Sean D. Stuber ..read more

When using the new SELECT AI syntax of 23ai, you can specify the model you’d like to use with OpenAI’s ChatGPT service. Before using the functionality, you must first create an ACE (access control entry) for the OpenAI host with your user as the principal and grant execute to the cloud packages DBMS_CLOUD and DBMS_CLOUD_AI. These need to be done with an ADMIN account in your cloud database. BEGIN DBMS_NETWORK_ACL_ADMIN.append_host_ace( HOST => 'api.openai.com', ace => xs$ace_type(privilege_list => xs$name_list('http'), pr ..read more

In two prior articles I used pl/sql and java to build functions that would return the keys of a JSON document as a collection which could be queried as a table. 23c includes a new MLE (multilingual engine) functionality providing a mechanism to define a stored procedure using JavaScript. This seemed like an ideal solution and the syntax to return keys is delightfully simple, by merely invoking the “keys” method json object, which, unlike in SQL, is a native feature in JavaScript. CREATE OR REPLACE FUNCTION getkeys(obj IN JSON) RETURN json AS MLE LANGUAGE JAVASCRIPT q'[return Object.keys(OBJ ..read more

In my previous article I provided a few PL/SQL options for returning a collection of keys from a JSON document. They are simple to use and work well enough for most JSON sources I’ve encountered. SQL> select * from json_keys(json('{"a":"test 1", "b": "test 2", "c" : "test 3"}')); COLUMN_VALUE -------------------------------------------------------------------------------- a b c Iudith Mentzel pointed out a pure-SQL option as well. SQL> SELECT SUBSTR(key_path, 3) 2 FROM JSON_TABLE((SELECT json_dataguide('{"a":"test 1", "b": "test 2", "c" : "test 3"}') FROM DUAL), 3 ..read more

Oracle supports a wide variety of JSON parsing, querying, and construction functionality; but, does not provide a means in SQL of listing the keys of JSON object (at least not as of 23c). While this is not a common need, it is a feature I have wanted a few times. Fortunately, it is an easy limitation to circumvent with a little PL/SQL. The JSON_OBJECT_T type includes a get_keys method which returns a VARRAY. Unfortunately, that type isn’t usable directly in SQL. SQL> select json_object_t('{"a":"test 1", "b": "test 2", "c" : "test 3"}') from dual; Error starting at line : 1 in command - se ..read more

Beginning with Oracle Database version 9.0.1, the ALTER SYSTEM command allowed you to add comments to your parameter changes. These comments can be up to 255 characters long. This feature came with the introduction of the spfile. In earlier versions, all parameter changes were made in text files, where you could embed comments with # prefixes on a line. While this is still supported, spfiles are the modern standard for maintaining parameters. Setting a comment in the spfile would be with the spfile or both scope. ALTER SYSTEM SET parameter_name = 'value' COMMENT='why are you doing this?' SCO ..read more

I found this functional surprise while responding to questions about my previous article. When you invoke the GET_KEYS method of JSON_OBJECT_T, if the collection is empty, 19c will return a NULL value; 21c will return a JSON_KEY_LIST collection of 0 elements. 19c SQL> DECLARE 2 v_version VARCHAR2(20); 3 v_compatible VARCHAR2(20); 4 v_keylist json_key_list; 5 BEGIN 6 DBMS_UTILITY.db_version(v_version, v_compatible); 7 DBMS_OUTPUT.put_line(v_version); 8 DBMS_OUTPUT.put_line(v_compatible); 9 v_keylist := json_object_t('{}').get_k ..read more

When presented with a JSON document you may need to process all of its contents. You’ll need to read each value and if there are any objects or arrays within the document you’ll need to read through the contents of each of them as well. Fortunately, the JSON structure is, intentionally, simple in its construction. When evaluating each value it will fall into only one of three types – it will either be a scalar, an object, or an array. If it’s a scalar (a string, number, Boolean, or null), you can process it with whatever rules you need for that data type. If it’s an object, you recursively pro ..read more

After completing my article about skewness of a sample for Joel Kallman Day I decided I could do one more and write up the related functionality for skewness of a population. Like skewness of a sample, the purpose of the function is to indicate the degree and direction of asymmetry in a data distribution. Like the SKEWNESS_SAMP function in my previous article, the 21c release of the database includes the SKEWNESS_POP aggregate. The usage is similar and here I’ll compare the results of the population vs sample skewness. Here I’m calculating a small, known data set 1,2,4,8,16. SQL> select s ..read more

Several years ago I needed to calculate the skewness of some data. That is, an indication of how asymmetric points are within a distribution. Unfortunately, I found there was no native feature to do this within SQL. So I wrote my own with the Oracle Data Cartridge Interface (ODCI.) Using ODCI you can create your own aggregate functions like MIN, MAX, and AVG. Recently I had cause to revisit that function and thought I could do a little better. This also seemed like a good opportunity to share some of my work in tribute to Joel Kallman for all he gave to the Oracle community. A happy discovery ..read more