Happy release day! We’re a bit tardy on our 3rd quarter update, but I’m going to blame corporate red tape and perhaps a bit of Cloud World Hangover.
So, what do you need to know?
New Features!
Friendlier, more helpful error messages
The enhanced error messages are here, with links to our new error message portal.
CODESCAN now has support for PL/SQL and SQL Coding Guidelines
You can either –
SET CODESCAN ON for interactive feedback, a la –
Scan an entire directory of your source code.
I exported all the PL/SQL from my ADMIN schema in my 19c Always Free Autonomous instance.
I asked for both JSON and TEXT reports, that looks like this –
The rules are community sourced, you can find them HERE. And there is an option to avoid rules you don’t agree with, more on that in a follow-up post.
Bug fixes
There are many, many bug fixes included in 23.3, but I’m going to highlight just one of those in this post:
IMPORTING AUTONOMOUS (WALLET) CONNECTIONS FROM SQL DEVELOPER FAILS
I have an Always Free Autonomous Database connection using mTLS, and I have a connection defined for my ADMIN account in SQL Developer.
I want to import it, like so –
And now I’m going to IMPORT this connection to SQLcl’s connection ‘store’ using the CONNMGR command.
If I run the CONNMGR list command, I can see what’s available, and then make my connection!
Tip: avoid the HIGH & MEDIUM services unless you really need it. The parallel stuff can get in the way of day-to-day development work.
There’s more to go over here, but we’ll cover that later.
Enjoy the release! Our next update will be 23.4, scheduled for early December 2023.
9 Comments
Hi, I noticed something I can’t explain when trying sqlcl “build 23.3.0.270.1251” with liquibase.
When I use “generate-schema” and “update” to apply my changes from one schema to other, everything works fine.
But in some circuntances I need to bring only part of the changes to production.
I ‘d tried “diff-changelog” from dev to production and it generated a correct “changes.xml” containing only the changes I want. Good.
But, when I ran “update”, after the changes where applied, the DATABASECHANGELOG_ACTIONS was not loaded with the corresponding DDL. That was confirmed with “update-sql”.
Here is my change:
—-
And here is “SQL> liquibase update-sql -chf v20231026r1714/changes.xml”:
–Starting Liquibase at 17:30:58 (version 4.18.0 #5864 built at 2022-12-02 18:02+0000)
— Loaded 1 change(s)
— *********************************************************************
— Update Database Script
— *********************************************************************
— Change Log: v20231026r1714/changes.xml
— Ran at: 10/26/23, 5:30 PM
— Against: PML_DATA_TLIQUIBASE@jdbc:oracle:thin:@PDB_DES.WORLD
— Liquibase version: 4.18.0
— *********************************************************************
— Lock Database
UPDATE PML_DATA_TLIQUIBASE.DATABASECHANGELOGLOCK SET LOCKED = 1, LOCKEDBY = ‘A5314F04H (172.17.0.1)’, LOCKGRANTED = SYSTIMESTAMP WHERE ID = 1 AND LOCKED = 0;
— Changeset v20231026r1714/changes.xml::1698352210917-1::ricardo (generated)
ALTER TABLE PML_DATA_TLIQUIBASE.URL_SERVICO MODIFY URL_SERVICO VARCHAR2(1000 CHAR);
INSERT INTO PML_DATA_TLIQUIBASE.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES (‘1698352210917-1’, ‘ricardo (generated)’, ‘v20231026r1714/changes.xml’, SYSTIMESTAMP, 49, ‘8:ce272fb18fbb857080c5aeef643a9a5f’, ‘modifyDataType columnName=URL_SERVICO, tableName=URL_SERVICO’, ”, ‘EXECUTED’, NULL, NULL, ‘4.18.0’, ‘8352268410’);
— Release Database Lock
UPDATE PML_DATA_TLIQUIBASE.DATABASECHANGELOGLOCK SET LOCKED = 0, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;
Operation completed successfully.
SQL>
Hi Jeff,
Looks like there is a defect which manifests when we generate changeset for database trigger.
I am using sqlcl 22.3 and generating the change log using command : lb ges -split -sql -syn -gr -co Standard-Release-v1 -la 2023.10.0.
Changelog is getting generated fine but failing during lb update with error “PLS-00103: Encountered the symbol “ALTER” ”
I think the problem is in the same same change set there are two statements, first is create trigger and next is alter trigger enable. and plsql block terminator \ is missing.
Can you please have a look when you have a moment? Please let me know if you need script to replicate this problem.
Many thanks,
Saroj Raut
You typed 22.3, but I’m assuming you meant in the latest release with 23.3?
Ok in my 23.3 SQLcl, i generated a changeLog for a single trigger, using generate-object
The SLASH (/) is there between the CREATE OR REPLACE and ALTER statements.
...CREATE OR REPLACE EDITIONABLE TRIGGER "HR"."UPDATE_JOB_HISTORY"AFTER UPDATE OF job_id, department_id ON employees
FOR EACH ROW
BEGIN
add_job_history(:old.employee_id, :old.hire_date, sysdate,
:old.job_id, :old.department_id);
END;
/
ALTER TRIGGER "HR"."UPDATE_JOB_HISTORY" ENABLE;...
Many thanks for the prompt response Jeff.
Apologies for the typo. Yes, I meant 23.3.
Single trigger is working fine for me as well (lb generate-object -object-type trigger -object-name TEAM_BIU) it’s generate schema where the trailing slash is missing (lb ges -split)
Ok will give that a go next.
Jeff,
This is an off-topic but need your help in a specific topic. I haven found a way to connect sql developer to the database through tls (not mTLS) using oracle client wallet. Could you please point me to any documentation about it?
Thanks in advance!
Many thanks for the prompt response Jeff.
Apologies for the typo. Yes, I meant 23.3.
Single trigger is working fine for me as well (lb generate-object -object-type trigger -object-name TEAM_BIU) it’s generate schema where the trailing slash is missing (lb ges -split)
Hi Jeff,
Have a question , does SQLcl has possibilities to have colors in statusbar, so instead of having sqlprompt with some alerting color (when connected to important DB) to have ability to color you DBID/USER in statusbar?