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

For all the amazing and grand new features coming in 23c for the database… sometimes it’s the little things that are really appreciated. SQL*Plus in 23c has a new PING command that mostly takes the place of TNSPING. This is fantastic news for all the fans of the Instant client that never included this handy utility. I came across requests for the functionality so often I wrote an article a few years ago with some alternatives for this missing functionality. Now, with just the standard 23c client SQL*Plus you can issue PING command and it will give you a sqlnet round trip timing to your current ..read more

In my previous article I showed how to build table functions, including pipelined functions with PL/SQL. To provide a simple, minimal example I created a function that would generate a list of numbers from 1 to some value provided by the invoker. CREATE OR REPLACE FUNCTION nt_function_pipelined(n IN INTEGER) RETURN numtab PIPELINED IS BEGIN FOR i IN 1 .. n LOOP PIPE ROW (i); END LOOP; RETURN; END; SELECT * FROM nt_function_pipelined(10); COLUMN_VALUE ------------ 1 2 3 4 5 6 7 ..read more

In a previous article I looked at creating and using collections within SQL. I mentioned, but did not explore functions returning Nested Table and Varying Array collection types in that article. Those fuctions, known as table functions, will be the subject here. A table function can return either of the two collection types in their entirety or one element (row) at a time. As the name table function implies, you can query the output of such functions as if it were a table. In versions 9i, 10g, and 11g you needed the TABLE clause to query from them. select * from TABLE(my_function) Starting w ..read more