Search for a command to run...
I came into contact with Oracle RDBMS at school somewhere near the end of the 90's. Ever since then I've worked with the Oracle Database. Over time I've written a few documents for myself and my colleagues explaining things that are not immediately clear by reading the doc. Eventually all of these should find their way here.
No comments yet. Be the first to comment.
In the 21c SQL Language Reference the following can be found regarding the INSERT statement: But in the 26ai SQL Language Reference that becomes and that small change opens a world of opportunity, reducing the number of context switches in certain ...
My company laptop is large enough (32Gb RAM) to host the containerized versions of the Oracle 23c FREE edition and ORDS. But it has also some limitations on installing and configuring things. I mainly ran into networking issues, which are probably re...
The client I'm contracting for has a transform schema in which data from multiple governmental sources is transformed into one data model. The result is that any single table contains data that originated from multiple sources. Therefore each record ...
For quite a while now, SQLcl has supported coloring query results. To have colored text in a field, the magic delimiters @| and |@ need to be used. After the starting delimiter (@|) you need to provide one or more directives to color the text that fo...
I downloaded the Windows version of GraalVM for Java 17: I Surfed to https://www.oracle.com/java/technologies/downloads/. There I chose the tab labeled GraalVM for JDK 17 (so not JDK 20, JDK 17 nor GraalVM for JDK 20)
The subpage indicates that
GraalVM for JDK 17 binaries are free to use in production and free to redistribute, at no cost, under the GraalVM Free Terms and Conditions. (https://www.oracle.com/downloads/licenses/graal-free-license.html)
The reason I chose 17 over 20, is that the 17 is a Long Term Release (until September 2024), while the 20 is not (only until September 2023, next month, when GraalVM for JDK 21 comes out. (It looks like 21 is the next LTS.))
Afterward, I also read somewhere that SQLcl does not support version 20 yet.
I choose Windows to get a download for that platform. That resulted in the file graalvm-jdk-17_windows-x64_bin.zip.
I also found out that I would be needing a plugin to run Javascript in GraalVM. (Since SQLcl can make use of that.)
I hopped over onto https://www.oracle.com/downloads/graalvm-downloads.html. There, I once again indicated version 17 for Windows and somewhere in the middle of the resulting page is a link to download the JavaScript Runtime That resulted in js-installable-jdk-17-windows-amd64-23.0.1.jar. After trying the installation a first time, I found out that the JavaScript Runtime depends on two other plugins: ICU4J Plugin and Tregex Plugin. Downloading these (from that same page as the first plugin) resulted in icu4j-installable-jdk-17-windows-amd64-23.0.1.jar and regex-installable-jdk-17-windows-amd64-23.0.1.jar.
...boils down to unzipping the zip somewhere, adding its bin directory to the PATH and setting the main directory as JAVA_HOME. The zip contains one folder which is best preserved in its entirety (including its name). So, I made folder C:\pf\GraalVM and dragged graalvm-jdk-17.0.8+9.1 from the zip into there, resulting in C:\pf\GraalVM\graalvm-jdk-17.0.8+9.1\bin (and others).
The plugins were still in my downloads folder: C:\Users\jvdboss1\Downloads*-installable-jdk-17-windows-amd64-23.0.1.jar They need to be installed into Graal using its gu command (GraalVM Component Updater), which resides in its bin-folder:
cd C:\pf\GraalVM\graalvm-jdk-17.0.8+9.1\bin
gu -L install "C:\Users\jvdboss1\Downloads\icu4j-installable-jdk-17-windows-amd64-23.0.1.jar"
gu -L install "C:\Users\jvdboss1\Downloads\regex-installable-jdk-17-windows-amd64-23.0.1.jar"
gu -L install "C:\Users\jvdboss1\Downloads\js-installable-jdk-17-windows-amd64-23.0.1.jar"
Running gu list confirms that I have GraalVM, icu4j, regex and js installed, all version 23.0.1.
At this moment, from that bin directory, running java -version returned Java(TM) SE Runtime Environment Oracle GraalVM 17.0.8+9.1 (build 17.0.8+9-LTS-jvmci-23.0-b14) OK :)
Running echo %path% or just path showed that another Java was there: C:\ProgramData\Oracle\Java\javapath. This is probably something installed by my employer (I don't write any Java programs for my job). It turns out to be a shortcut-folder ("junction") and it contains (only) java.exe, javaw.exe and javaws.exe...
The junction points to its sibling: C:\ProgramData\Oracle\Java\javapath_target_474953
I decided to leave that in the PATH, but put the GraalVM bin-folder first. So now there is ...;C:\pf\GraalVM\graalvm-jdk-17.0.8+9.1\bin;C:\ProgramData\Oracle\Java\javapath;...
While being in the Environment Variables GUI of Windows, I also set JAVA_HOME and gave it the value C:\pf\GraalVM\graalvm-jdk-17.0.8+9.1.
After restarting the command line prompt echo %path% and echo %java_home% confirm that these settings are there. From the default directory, running java -version tells me that GraalVM 17 is being used :)
I already had a SQLcl installation. I went to its bin directory and started it:cd C:\pf\sqlcl\bin sql /nolog
And it still started. :) Doing show java confirmed that it had picked up the JAVA_HOME setting.
Installing a new version of SQLcl is also just about unzipping a zip. From https://www.oracle.com/database/sqldeveloper/technologies/sqlcl/download/ I downloaded sqlcl-23.2.0.178.1027.zip After making sure no more SQLcl was running and all command windows were no longer in the folder, I renamed my existing C:\pf\sqlcl to C:\pf\sqlcl_22_3 (since that was the old version I had).
Next, I dragged the sqlcl folder from the zip to my C:\pf folder.
And that is how you upgrade SQLcl :)
You can always use https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-latest.zip to directly get the latest version, whatever it is. Ideal for DevOps.
SQLcl falls under the Oracle Free License
I already had C:\pf\sqlcl\bin in my path, so running sql /nolog from anywhere starts SQLcl :)
While SQLcl works fine out of the box, obviously, it has many settings which you may want to apply each time. To do that, you can create a glogin.sql or login.sql script, just like for SQL*Plus.
Running show login shows you where that script can reside so that SQLcl picks it up. In my case (having an old fat client installed too) it was:
C:\Oracle\product\12.2.0\client_1\sqlplus\admin\glogin.sql
.\login.sql
C:\Oracle\product\12.2.0\client_1\dbs\login.sql
Notice that the 2nd entry implies that you can have different login.sql scripts per project/environment/..., just by starting SQLcl from a certain directory.
I set up the statusbar, syntax highlighting and DDL generation config. I relaxed the history filter a bit, confirmed the sqlformat (query output), allow empty lines in SQL-scripts and set up the Trivadis Formatter.
Also very important: I explicitly turn autocommit off, but exitcommit on.
That results in the following login.sql script
SET statusbar ON
SET statusbar editmode linecol java cwd encoding username dbid txn timing
SET highlighting ON
--SET highlighting DEFAULT RESET --duh
SET highlighting COMMENT foreground GREEN
--SET highlighting COMMENT background DEFAULT --same as default
SET highlighting STRING foreground MAGENTA
--SET highlighting STRING background DEFAULT --same as default
SET highlighting NUMBER foreground MAGENTA
--SET highlighting NUMBER background DEFAULT --same as default
--SET highlighting PUNCTUATION foreground DEFAULT --same as default
--SET highlighting PUNCTUATION background DEFAULT --same as default
SET highlighting KEYWORD foreground YELLOW
--SET highlighting KEYWORD background DEFAULT --same as default
SET highlighting IDENTIFIER foreground CYAN
--SET highlighting IDENTIFIER background DEFAULT --same as default
--SET highlighting ERROR foreground RED --same as default
--SET highlighting ERROR background DEFAULT --same as default
--SET highlighting ERROR underline ON --same as default
SET ddl CONSTRAINTS_AS_ALTER off
SET ddl FORCE off
SET ddl SEGMENT_ATTRIBUTES off
SET ddl SIZE_BYTE_KEYWORD off
SET ddl STORAGE off
-- Include connect and show in the history
-- Note that connect implies a security risk when passords are entered in the connect statement.
-- But normally, only stored connections are used.
-- When exceptionally making a manual connection, don't include the password in the connect statment.
-- Provide it separately. SQLcl will ask for it.
SET history FILTER history clear
SET sqlformat ANSICONSOLE
--SET sqlformet JSON-FORMATTED
--SET sqlformat delimited ; " "
-- Allow empty lines in SQL statements (Even catproc.sql has them)
SET sqlblanklines ON
-- Use command tvdformat to call the Trivadis formatter
script C:\Repos\Trivadis-PLSQL-Formatter-Settings\sqlcl\format.js --register
-- Set up tformat alias to call tvdformat with our own settings to format the buffer
alias tformat=tvdformat * xml=C:\Users\jvdboss1\Documents\Modified_trivadis_formatter.xml arbori=C:\Repos\Trivadis-PLSQL-Formatter-Settings\settings\sql_developer\trivadis_custom_format.arbori ;
SET autocommit OFF
SET exitcommit ON