Because the world needs more PL/SQL blogs
Continuing with Polymorphic Tabe Functions (PTF’s) this post delves further into the DESCRIBE function – specifically the table, package and function involved and the columns of the passed in object. I’ll let the code to the talking… CREATE OR REPLACE PACKAGE BODY poly_pkg AS FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T ) RETURN DBMS_TF.DESCRIBE_T AS BEGIN … Read more
The previous post in this series introduced Polymorphic Table Functions (PTF’s) in a very simplistic way. All they have is a DESCRIBE function. Thus they are referred to as “Describe Only PTF’s”. Oracle does not include them in the query plan. Below are all of the SQL statements from the previous post along with their … Read more
Polymorphic Table Functions (PTF’s) work with input that is not predefined and provide output that is not predefined. Thus they are very flexible. I encourage you to Google the phrase “Oracle Polymorphic Table Functions” to find information on this feature introduced in Oracle 18c. in this series of posts I’ll work through some examples of … Read more
This post begins investigating what goes on behind the scenes to implement a SODA collection in Oracle. Lets dive right in and create a SODA collection named CLCT and put a JSON document in it using the DBMS_SODA PLSQL package. DECLARE v soda_collection_t; d soda_document_t; t NUMBER; BEGIN v := DBMS_SODA.CREATE_COLLECTION (‘CLCT’); d := SODA_DOCUMENT_T( … Read more
Committing a transaction means the transaction cannot be rolled back. There are two types of commits: Explicit – when the COMMIT command is executed Implicit – when DDL (Data Definition Language) is executed DDL includes SQL such as CREATE TABLE. When DDL is executed all un-committed work in the current session is implicitly committed. The … Read more
Simple Oracle Document Access (SODA) comes to PL/SQL! Being Canadian and being in a certain age group I have been patiently waiting for SODA in PLSQL. Two reasons: Its fun to learn new things I get to use the iconic Go For Soda song from Canadian musician Kim Mitchell in a post! And a 1…and … Read more
The Oracle PLSQL JSON_OBJECT function assembles JSON KEY:VALUE pairs. It uses column values from a table or view as the VALUE. It can use hardcoded strings or column values as the KEY. Here is an example creating 3 records in a table and querying them using the JSON_OBJECT function. — record 1 NOT JSON INSERT … Read more
JSON Path Expression Item Method’s (PEIM) were added in Oracle 18c. They provide extensions to interpret JSON data. But the interpretations are stricter than they appear. Take the string() PEIM for instance – the Oracle documentation says it returns an “SQL VARCHAR2(4000) interpretation of the targeted scalar JSON value”. That seems straightforward but… — Create … Read more
Dataguide provides three attributes for JSON data – the path, the datatype and the length. Ever since Oracle 12.2 it has done that dutifully – even for arrays of scalar (non key-value pair) values. But initially it only denoted the fact there was an array, it did not denote the datatype of the entries in … Read more
The previous post used numeric and string value to demonstrate how Oracle JSON Data Guide may change assigned datatypes to accommodate mixed data. This post demonstrates the same concept using JSON boolean values. SQL> INSERT INTO json_data 2 VALUES(1,'{“RAPTOR_LOST”:true}’); 1 row created. SQL> INSERT INTO json_data 2 VALUES(2,'{“RAPTOR_LOST”:”YES”}’); 1 row created. SQL> SELECT JSON_DATAGUIDE(json_col) boolean … Read more