Improvements to SQLcl Engine

Liquibase is an open-source database independent library for tracking, managing, and applying database schema changes.
 
Oracle SQLcl (SQL Developer Command Line) is a small, lightweight, Java-based command-line interface for Oracle Database. SQLcl provides inline editing, statement completion, command recall, and also supports existing SQL*Plus scripts. You can download SQLcl from oracle.com and it is installed in the OCI Cloud Shell by default.
 
The Liquibase feature in SQLcl enables you to execute commands to generate a changelog for a single object or for a full schema (changeset and changelogs). We also add Oracle specific features and enhancements to Liquibase in SQLcl.
 
When your changelog got a set of SQL or PL/SQL command
 
demo@PDB1> $ type tell_me_hello.sql
begin
 dbms_output.put_line('Hello_world');
end;
/
 
demo@PDB1> $ type controller.xml
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
                xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.9.xsd">
 
        <changeSet author="liquibase-docs" id="tell_me_hello"
                                runOnChange="true" runAlways="true" failOnError="true" >
                <sqlFile  dbms="oracle"
                                  encoding="utf8"
                                  endDelimiter="/"
                                  path="tell_me_hello.sql"
                                  relativeToChangelogFile="true"
                                  splitStatements="true"
                                  stripComments="true"
                />
        </changeSet>
</databaseChangeLog>
 
demo@PDB1>
 
the execution of changesets goes fine like this.
 
demo@PDB1> lb up -chf controller.xml
--Starting Liquibase at 2024-11-26T13:02:27.864180300 (version 4.25.0.305.0400 #0 built at 2024-10-31 21:25+0000)
Running Changeset: controller.xml::tell_me_hello::liquibase-docs
 
UPDATE SUMMARY
Run:                          1
Previously run:               0
Filtered out:                 0
-------------------------------
Total change sets:            1
 
Liquibase: Update has been successful. Rows affected: 1
 
 
Operation completed successfully.
 
Hello_world
 
 
But the real challenge arises, when your changeset got references to SQLcl specific commands in the scripts.
 
begin
 dbms_output.put_line('Hello_world');
end;
/
info EMP
 
then the execution of changesets, will fail like this
 
demo@PDB1> lb up -chf controller.xml
--Starting Liquibase at 2024-11-26T13:06:47.741351200 (version 4.25.0.305.0400 #0 built at 2024-10-31 21:25+0000)
Running Changeset: controller.xml::tell_me_hello::liquibase-docs
2024-11-26 13:06:49.602 SEVERE liquibase.changelog ChangeSet controller.xml::tell_me_hello::liquibase-docs encountered an exception.
 
UPDATE SUMMARY
Run:                          1
Previously run:               0
Filtered out:                 0
-------------------------------
Total change sets:            1
 
An error has occurred:
liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for changeset controller.xml::tell_me_hello::liquibase-docs:
     Reason: liquibase.exception.DatabaseException: ORA-00900: invalid SQL statement
 
https://docs.oracle.com/error-help/db/ora-00900/ [Failed SQL: (900) info EMP]
ORA-00900: invalid SQL statement
 
Hello_world
 
 
And this is where the recent enhancement to SQLcl will help us.
 
demo@PDB1> lb show
--Starting Liquibase at 2024-11-26T13:09:21.619566200 (version 4.25.0.305.0400 #0 built at 2024-10-31 21:25+0000)
Liquibase stored parameters
FILE_ENCODING                 UTF-8
SKIP_ROLLBACK                 OFF
SAVE_CAPTURE                  OFF
ENGINE                        JDBC
SHORTEN_SPLIT                 OFF
SCAN                          OFF
SQLBLANKLINES                 ON
 
 
Operation completed successfully.
 
 
 
By default, we execute the SQL using the standard JDBC and it doesn’t know what INFO command does.
 
When we flip the ENGINE to SQLcl, that will execute all the commands in the changesets using SQLcl engine.
 
demo@PDB1> lb set engine SQLCL
--Starting Liquibase at 2024-11-26T13:43:49.884092600 (version 4.25.0.305.0400 #0 built at 2024-10-31 21:25+0000)
 
 
Operation completed successfully.
 
demo@PDB1> lb show
--Starting Liquibase at 2024-11-26T13:43:55.302563700 (version 4.25.0.305.0400 #0 built at 2024-10-31 21:25+0000)
Liquibase stored parameters
FILE_ENCODING                 UTF-8
SKIP_ROLLBACK                 OFF
SAVE_CAPTURE                  OFF
ENGINE                        SQLCL
SHORTEN_SPLIT                 OFF
SCAN                          OFF
SQLBLANKLINES                 ON
 
 
Operation completed successfully.
 
demo@PDB1>
 
demo@PDB1> lb up -chf controller.xml
--Starting Liquibase at 2024-11-26T13:45:16.097107100 (version 4.25.0.305.0400 #0 built at 2024-10-31 21:25+0000)
Running Changeset: controller.xml::tell_me_hello::liquibase-docs
Hello_world
 
 
PL/SQL procedure successfully completed.
 
 
TABLE: EMP
         LAST ANALYZED:2024-10-30 10:02:45.0
         ROWS         :14
         SAMPLE SIZE  :14
         INMEMORY     :DISABLED
         COMMENTS     :
 
Columns
NAME         DATA TYPE           NULL  DEFAULT    COMMENTS
 EMPNO       NUMBER(4,0)         Yes
 ENAME       VARCHAR2(10 BYTE)   Yes
 JOB         VARCHAR2(9 BYTE)    Yes
 MGR         NUMBER(4,0)         Yes
 HIREDATE    DATE                Yes
 SAL         NUMBER(7,2)         Yes
 COMM        NUMBER(7,2)         Yes
 DEPTNO      NUMBER(2,0)         Yes
 
 
 
 
UPDATE SUMMARY
Run:                          1
Previously run:               0
Filtered out:                 0
-------------------------------
Total change sets:            1
 
Liquibase: Update has been successful. Rows affected: 1
 
 
Operation completed successfully.
 
demo@PDB1>
 
 
 

Oracle Database API for MongoDB - Part VII

One of many nifty enhancement introduced in Oracle 23ai(23.6) is JSON Collection views.
 
A JSON Collection view simply maps JSON documents to underlying relational data – there are two flavours.
 

• Duality View – can be directly updatable, which mean we can directly insert, update, or delete the documents, which in-turn modify the data in the underlying relation table.

• JSON collection view – that is not directly updatable, equivalent to Duality view in a read only format. Using a document API, we can only query its contents, however we can update the supported documents indirectly by modify the data in the underlying relation tables.

 
We will explain with a simple test case.
 
demo@ATP23ai> CREATE OR REPLACE JSON relational duality VIEW EMPVIEW_JDVW AS
  2  SELECT JSON {'_id'         : employee_id,
  3                last_name,
  4                'contactInfo' : {email, phone_number},
  5                hire_date,
  6                salary}
  7  FROM employees with update;
 
View created.
 
demo@ATP23ai>
demo@ATP23ai> CREATE OR REPLACE JSON COLLECTION VIEW EMPVIEW_JCVW AS
  2  SELECT JSON {'_id'         : employee_id,
  3                last_name,
  4                'contactInfo' : {email, phone_number},
  5                hire_date,
  6                salary} data
  7  FROM employees;
 
View created.
 
So we created two views – one Duality view and one JSON Collection view.
 
Updates to the duality view are possible.
 
demo@ATP23ai> update EMPVIEW_JDVW v
  2  set data = json('{
  3    "_id" : 100,
  4    "last_name" : "King",
  5    "contactInfo" :
  6    {
  7      "email" : "SKING",
  8      "phone_number" : "1.515.555.0100"
  9    },
 10    "hire_date" : "2013-06-17T00:00:00",
 11    "salary" : 24000
 12  }')
 13  where v.data."_id".number() = 100;
 
1 row updated.
 
But not on the JSON Collection view.
 
demo@ATP23ai> update EMPVIEW_JCVW v
  2  set data = json('{
  3    "_id" : 100,
  4    "last_name" : "King",
  5    "contactInfo" :
  6    {
  7      "email" : "SKING",
  8      "phone_number" : "1.515.555.0100"
  9    },
 10    "hire_date" : "2013-06-17T00:00:00",
 11    "salary" : 24000
 12  }')
 13  where v.data."_id".number() = 100;
where v.data."_id".number() = 100
      *
ERROR at line 13:
ORA-01733: virtual column not allowed here
Help: https://docs.oracle.com/error-help/db/ora-01733/
 
This JSON Collection view sounds like a traditional view.
 
demo@ATP23ai> CREATE OR REPLACE VIEW EMPVIEW_VW AS
  2  SELECT JSON {'_id'         : employee_id,
  3                last_name,
  4                'contactInfo' : {email, phone_number},
  5                hire_date,
  6                salary} data
  7  FROM employees;
 
View created.
 
demo@ATP23ai> update EMPVIEW_VW v
  2  set data = json('{
  3    "_id" : 100,
  4    "last_name" : "King",
  5    "contactInfo" :
  6    {
  7      "email" : "SKING",
  8      "phone_number" : "1.515.555.0100"
  9    },
 10    "hire_date" : "2013-06-17T00:00:00",
 11    "salary" : 24000
 12  }')
 13  where v.data."_id".number() = 100;
where v.data."_id".number() = 100
      *
ERROR at line 13:
ORA-01733: virtual column not allowed here
Help: https://docs.oracle.com/error-help/db/ora-01733/
 
However, the key difference between JSON collection view and the traditional view was, both JSON collection view and Duality view are accessible from either Mongo-DB commands or SQL command where are traditional view is accessible only from SQL commands.
 
Connection to the database from Mongoshell list the JSON Collection view and Duality view but not the traditional views.
 
C:\Users\Rajeshwaran>mongosh --tls --tlsAllowInvalidCertificates "mongodb://demo@G26BE7C92912CDB-ATP23AI.adb.us-ashburn-1.oraclecloudapps.com:27017/demo?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true"
Enter password: *************
Current Mongosh Log ID: 675702044d99615e2886b01c
Connecting to:          mongodb://<credentials>@G26BE7C92912CDB-ATP23AI.adb.us-ashburn-1.oraclecloudapps.com:27017/demo?authMechanism=PLAIN&authSource=%24external&ssl=true&retryWrites=false&loadBalanced=true&tls=true&tlsAllowInvalidCertificates=true&appName=mongosh+2.3.2
Using MongoDB:          4.2.14
Using Mongosh:          2.3.2
mongosh 2.3.4 is available for download: https://www.mongodb.com/try/download/shell
 
For mongosh info see: https://www.mongodb.com/docs/mongodb-shell/
 
demo>
 
demo> show collections
EMP_DV
EMP2
EMPLOYEE
EMPVIEW_JCVW
EMPVIEW_JDVW
PERSON_DV
RAW_SRCH_VRTL_QUERY_MOD_TEST
SCHEDULE_DAY_EXTENDED_DV
STUDENTS_DV
TEST_DV1
X
XBASE
 
Also document API access possible on JSON Collection view and Duality view
 
demo> db.EMPVIEW_JCVW.find({"_id":100});
[
  {
    _id: 100,
    last_name: 'King',
    contactInfo: { email: 'SKING', phone_number: '1.515.555.0100' },
    hire_date: ISODate('2013-06-17T00:00:00.000Z'),
    salary: 24000
  }
]
demo>
 
demo> db.EMPVIEW_JDVW.find({"_id":100});
[
  {
    _id: 100,
    last_name: 'King',
    contactInfo: { email: 'SKING', phone_number: '1.515.555.0100' },
    hire_date: ISODate('2013-06-17T00:00:00.000Z'),
    salary: 24000,
    _metadata: {
      etag: Binary.createFromBase64('h/wZRrIKVHohCC7v+umKvg==', 0),
      asof: Binary.createFromBase64('AAAmA4rj1q4=', 0)
    }
  }
]
 
But not on traditional views…
 
demo> db.EMPVIEW_VW.find({"_id":100});
 
demo> db.EMPVIEW_VW.find();
 
 

Oracle Database API for MongoDB - Part VI

In the pervious blogpost we saw about how to transform the documents from Mongo collections into Relational format, all that can be done using DBMS_JSON_DUALITY a new API introduced in Oracle database 23ai. In this blogpost we will see about how to safely remove the JSON collection from the database and bring the duality view inter-operability between SQL and MongoDB commands.
 
First let’s drop the JSON Collection and recreate the Duality View (DV) in the same name as collections.
 
 
mdb-test2@FREEPDB1> drop table CONF_SCHEDULE purge;
 
Table CONF_SCHEDULE dropped.
 
mdb-test2@FREEPDB1>
mdb-test2@FREEPDB1> CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW CONF_SCHEDULE AS
  2  conf_schedule_root @insert @update @delete
  3  {
  4    "_id"
  5    name
  6    schedule: conf_schedule_schedule @insert @update @delete
  7    {
  8      name
  9      speaker
 10      location
 11      sessionId: session_id
 12      speakerId: speaker_id
 13      scheduleId: schedule_id
 14    }
 15* } ;
 
View CONF_SCHEDULE created.
 
 
Then REST enable the DV like this
 
mdb-test2@FREEPDB1> declare
  2    pragma autonomous_transaction;
  3  begin
  4      ords.enable_object(p_enabled => TRUE,
  5                         p_schema => user,
  6                         p_object => 'CONF_SCHEDULE',
  7                         p_object_type => 'VIEW',
  8                         p_object_alias => 'CONF_SCHEDULE',
  9                         p_auto_rest_auth => FALSE);
 10
 11      commit;
 12  end;
 13* /
 
PL/SQL procedure successfully completed.
 
And verify if the DV is accessible over the API end points, doing a GET request like this.
 
Rajeshwaran@rajeyaba-3WH3DK3 MINGW64 /c/DECS/VSCODE-DOCS/git
$ curl --request GET \
  --url http://localhost:8080/ords/mdb_test2/CONF_SCHEDULE/1
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   843    0   843    0     0    652      0 --:--:--  0:00:01 --:--:--   652{"_id":1,"name":"Beda","schedule":[{"name":"JSON and SQL","speaker":"Tirthankar","location":"Room 1","sessionId":1,"speakerId":1,"scheduleId":1},{"name":"PL/SQL or Javascript","speaker":"Tirthankar","location":"Room 2","sessionId":2,"speakerId":1,"scheduleId":2},{"name":"Oracle on IPhone","speaker":"Jenny","location":"Room 1","sessionId":3,"speakerId":2,"scheduleId":4},{"name":"MongoDB API Internals","speaker":"Julian","location":"Room 4","sessionId":5,"speakerId":4,"scheduleId":6}],"_metadata":{"etag":"73245692311D9B3DA9F43F1CB4DC58B7","asof":"0000000000D80514"},"links":[{"rel":"self","href":"http://localhost:8080/ords/mdb_test2/CONF_SCHEDULE/1"},{"rel":"describedby","href":"http://localhost:8080/ords/mdb_test2/metadata-catalog/CONF_SCHEDULE/item"},{"rel":"collection","href":"http://localhost:8080/ords/mdb_test2/CONF_SCHEDULE/"}]}
 
Similarly, we can update the documents over API call like this.
 
mdb-test2@FREEPDB1> select * from conf_schedule_root where "_id" = 1;
 
       _id NAME
---------- -------------------------
         1 Beda
 
mdb-test2@FREEPDB1> select name,speaker,location,session_id,speaker_id,schedule_id
  2  from conf_schedule_schedule
  3* where "_id_conf_schedule_root" = 1;
 
NAME                      SPEAKER                   LOCATION   SESSION_ID SPEAKER_ID SCHEDULE_ID
------------------------- ------------------------- ---------- ---------- ---------- -----------
JSON and SQL              Tirthankar                Room 1              1          1           1
PL/SQL or Javascript      Tirthankar                Room 2              2          1           2
Oracle on IPhone          Jenny                     Room 1              3          2           4
MongoDB API Internals     Julian                    Room 4              5          4           6
 
mdb-test2@FREEPDB1>
mdb-test2@FREEPDB1>
 
Rajeshwaran@rajeyaba-3WH3DK3 MINGW64 /c/DECS/VSCODE-DOCS/git
$ curl --request PUT \
  --url http://localhost:8080/ords/mdb_test2/CONF_SCHEDULE/1 \
  --header 'Content-Type: application/json' \
  --data '{
        "_id": 1,
        "name": "Beda2",
        "schedule": [
                {
                        "name": "JSON and SQL",
                        "speaker": "Tirthankar",
                        "location": "Room 1",
                        "sessionId": 1,
                        "speakerId": 1,
                        "scheduleId": 1
                },
                {
                        "name": "PL/SQL or Javascript",
                        "speaker": "Tirthankar",
                        "location": "Room 2",
                        "sessionId": 2,
                        "speakerId": 1,
                        "scheduleId": 2
                },
                {
                        "name": "Oracle on IPhone",
                        "speaker": "Jenny",
                        "location": "Room 1",
                        "sessionId": 3,
                        "speakerId": 2,
                        "scheduleId": 4
                },
                {
                        "name": "MongoDB API Internals",
                        "speaker": "Tkyte",
                        "location": "Room 4",
                        "sessionId": 5,
                        "speakerId": 4,
                        "scheduleId": 6
                }
        ]
}'
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  1488    0   843  100   645   2401   1837 --:--:-- --:--:-- --:--:--  4239{"_id":1,"name":"Beda2","schedule":[{"name":"JSON and SQL","speaker":"Tirthankar","location":"Room 1","sessionId":1,"speakerId":1,"scheduleId":1},{"name":"PL/SQL or Javascript","speaker":"Tirthankar","location":"Room 2","sessionId":2,"speakerId":1,"scheduleId":2},{"name":"Oracle on IPhone","speaker":"Jenny","location":"Room 1","sessionId":3,"speakerId":2,"scheduleId":4},{"name":"MongoDB API Internals","speaker":"Tkyte","location":"Room 4","sessionId":5,"speakerId":4,"scheduleId":6}],"_metadata":{"etag":"2A83625487FC53369D5B4C80B3FB5895","asof":"0000000000D80778"},"links":[{"rel":"self","href":"http://localhost:8080/ords/mdb_test2/CONF_SCHEDULE/1"},{"rel":"describedby","href":"http://localhost:8080/ords/mdb_test2/metadata-catalog/CONF_SCHEDULE/item"},{"rel":"collection","href":"http://localhost:8080/ords/mdb_test2/CONF_SCHEDULE/"}]}
 
The highlighted parts of the payload were the changes introduced as part of the API call to see those changes back into the relational tables.
 
After the API call, the data in the relation table looks like this
 
mdb-test2@FREEPDB1> select * from conf_schedule_root where "_id" = 1;
 
       _id NAME
---------- -------------------------
         1 Beda2
 
mdb-test2@FREEPDB1> select name,speaker,location,session_id,speaker_id,schedule_id
  2  from conf_schedule_schedule
  3* where "_id_conf_schedule_root" = 1;
 
NAME                      SPEAKER                   LOCATION   SESSION_ID SPEAKER_ID SCHEDULE_ID
------------------------- ------------------------- ---------- ---------- ---------- -----------
JSON and SQL              Tirthankar                Room 1              1          1           1
PL/SQL or Javascript      Tirthankar                Room 2              2          1           2
Oracle on IPhone          Jenny                     Room 1              3          2           4
MongoDB API Internals     Tkyte                     Room 4              5          4           6
 
  
Now let’s do some changes to the DV (aka JSON collection tables) from Mongo Shell either using MongoDB commands or SQL Commands.
 
mdb_test2> db.CONF_SCHEDULE.find({_id:1});
[
  {
    _id: 1,
    name: 'Beda2',
    schedule: [
      {
        name: 'JSON and SQL',
        speaker: 'Tirthankar',
        location: 'Room 1',
        sessionId: 1,
        speakerId: 1,
        scheduleId: 1
      },
      {
        name: 'PL/SQL or Javascript',
        speaker: 'Tirthankar',
        location: 'Room 2',
        sessionId: 2,
        speakerId: 1,
        scheduleId: 2
      },
      {
        name: 'Oracle on IPhone',
        speaker: 'Jenny',
        location: 'Room 1',
        sessionId: 3,
        speakerId: 2,
        scheduleId: 4
      },
      {
        name: 'MongoDB API Internals',
        speaker: 'Tkyte',
        location: 'Room 4',
        sessionId: 5,
        speakerId: 4,
        scheduleId: 6
      }
    ],
    _metadata: {
      etag: Binary.createFromBase64('KoNiVIf8UzadW0yAs/tYlQ==', 0),
      asof: Binary.createFromBase64('AAAAAADYDlk=', 0)
    }
  }
]
 
mdb_test2> db.aggregate([{$sql:` update CONF_SCHEDULE cs
...             set cs.data = json_transform( cs.data,
...             PREPEND '$.schedule' =  json('{ "name": "Optimizer Fundamentals",
...                     "speaker": "Nigel" ,
...                     "location": "Room 17"}') )
...             where cs.DATA."_id".number() = 1 `}]);
[ { result: 1 } ]
mdb_test2>
mdb_test2> db.CONF_SCHEDULE.find({_id:1});
[
  {
    _id: 1,
    name: 'Beda2',
    schedule: [
      {
        name: 'JSON and SQL',
        speaker: 'Tirthankar',
        location: 'Room 1',
        sessionId: 1,
        speakerId: 1,
        scheduleId: 1
      },
      {
        name: 'PL/SQL or Javascript',
        speaker: 'Tirthankar',
        location: 'Room 2',
        sessionId: 2,
        speakerId: 1,
        scheduleId: 2
      },
      {
        name: 'Oracle on IPhone',
        speaker: 'Jenny',
        location: 'Room 1',
        sessionId: 3,
        speakerId: 2,
        scheduleId: 4
      },
      {
        name: 'MongoDB API Internals',
        speaker: 'Tkyte',
        location: 'Room 4',
        sessionId: 5,
        speakerId: 4,
        scheduleId: 6
      },
      {
        name: 'Optimizer Fundamentals',
        speaker: 'Nigel',
        location: 'Room 17',
        sessionId: null,
        speakerId: null,
        scheduleId: 8
      }
    ],
    _metadata: {
      etag: Binary.createFromBase64('GDXomloGR4q9Dv0nBqVG/w==', 0),
      asof: Binary.createFromBase64('AAAAAADYEME=', 0)
    }
  }
]
mdb_test2>
 
Highlighted above are the changes introduced as part of recent execution.
 
By this way we can start using Duality view as a replacement for Mongo Collections and can be accessible from all over the place as REST API’s or MongoDB command from Mongo shell or SQL commands from Oracle clients.