Last Updated on 2020-10-15, 16:47 (GMT+0200)

This post is the first one of three that together are an introduction to scripting in SQLcl.
Also read part 2 and part 3.

For many years now SQLcl is my favorite command line tool to connect to Oracle databases. (available at the bottom of the SQL Developer download page)
It does (practically) everything SQL*Plus does and a whole lot more. It has many capabilities that SQL*Plus just doesn’t have.
But this post is not about those goodies SQLcl delivers, it’s more or less about what is doesn’t….

No matter how good we think a tool is, and I think SQLcl is great, there will always be things missing, or not quite the way we would want them to be.
SQLcl is no exception to this rule.
Inevitably we run into something that makes us think “I wish it could do ….”.
The team that brings us SQLcl (and SQL Developer and Datamodeler and ORDS) is constantly developing the tool, adding and improving features. Also, they listen amazingly well to feedback from the community. But it might not be the most efficient strategy to wait for them to hopefully implement in a future release the feature that we need today and obviously exactly the way we would like it to be.

Do we have other options? Of course we do!

One of the really awesome things in SQLcl is that if some functionality you need is missing, you can (probably) build it yourself.

SQLcl is built in Java hence uses the Java Virtual Machine (JVM) and allows us to write scripts in scripting languages that the JVM supports.
So, you can use the Oracle Nashorn JavaScript engine to run JavaScript that implements your missing functionality.

Unfortunately when I wanted to create a script to implement some functionality I wanted, I found that the documentation on this is scarce.
Most online resources seem to assume everybody already knows how it works, or at least that supplying a ready made example script without explanation is enough to figure it out.
So I decided to post what I think I learned during my first steps in creating a script for SQLcl, that actually is useful (to me) and isn’t just another “hello world” script.
In my next post “A Person who won’t read has no advantage over one who can’t read” I will discuss this script.
This post will deal with the basic information I needed/learned while combining online information with my own experimenting.

First a little disclaimer: I am neither a JavaScript guru nor a Java wizard.
Hence, this is not a crash course JavaScript but an introduction in how to start expanding SQLcl functionality using JavaScript.
I may have come to wrong conclusions or use suboptimal constructs in the eyes of the experts.
Feel free to correct and/or educate me in the comments, I’m always open for suggestions and eager to learn.

Go straight to

First major question: how do I run a script from SQLcl?
Some general things about JavaScript
– Directive “use strict”
– Statement terminator
– Comments
– Case Sensitivity
– Quoted Strings
– Concatenation
– Variables
– Constants
– Arrays
– Script parameters
– Comparing stuff
– Logical And/Or
– Functions and procedures
– ctx
– util
– Java.type
– Let SQLcl execute statements
Conclusion

First major question: how do I run a script from SQLcl?

Of course you need to have a script to be able to run it, but obviously I first want to make sure I can indeed run a script, and that I understand how, before wasting time developing one that I might not be able to use.

There are two ways to run JavaScript from SQLcl

1. Typing the code (the entire script) at the command line
   First you type the keyword “script” followed by “enter”
   Then you write all the code you need executed
   Finally you execute it with a slash
   
2. Write the code and save it in a script file
   Then type “script [script-filename]” followed by “enter”

We’ll first have a look at option 1, doing it from the command line.
Well, as boring as it is, lets test this with a simple thing that writes “hello friends” to screen …
We can do this using ctx.write like this

But straight away we are disappointed…..

Nothing.
Well, on the bright side, at least there’s no error message either.

The point is: if ctx.write isn’t ended with an end-of-line (“\n”), nothing appears. So, we have to add that to our command.
If you have multiple ctx.write statements where only the last one ends with “\n”, all will appear, but all on the same line.

Now if we run that….

Success \o/
This first (well, second) attempt proves that we can run JavaScript.
Now let’s put the ctx line in a file called “hi.js” and try to run it.

That also works!!
And we see that we can run the script with a fully qualified pathname, or with just the filename if it’s in the current directory.
Also, if the extension of the file is “.js” we can leave it out because it is the default.

So, running scripts is possible.

Go Back to top

Some general things about JavaScript

I know I’ve said that this is not a JavaScript tutorial, and it’s not.
But to develop scripts for SQLcl some things need to be clear.
So here are some things I figured out while developing my first script.
This isn’t even close to being complete. It’s just the basic stuff I needed for the functionality I want.

Go Back to top

Directive “use strict”

Unlike in PLSQL by default you can just introduce a variable in JavaScript whenever you need it, without formally declaring it first.
This seems to provide flexibility. But it can also introduces nightmares.
When for example you make a typo in the name of a variable a new variable with that name is just created on the spot and the script will continue to run.
But the rest of your code is referring to the variable with the correct spelling.
Now you’re left with headaches trying to find out why your code doesn’t produce the output you expect.

For this reason, I personally consider languages that do this immature (Sorry JavaScript fans, sorry Visual Basic fans, etc.)
Thankfully at some point a directive “use strict” was introduced.
If you add

to the top of your script, then if you use a variable that has not been declared before, the script will generate an error message and stop.
The version used by SQLcl knows about this directive.
Since it’s basically just a string, if you run a script that contains this directive in an old version of JavaScript that doesn’t know it, it will be ignored.

In my opinion, every JavaScript code should use this directive!

Go Back to top

Statement terminator

Statements in JavaScript are terminated by a semicolon “;“, just like in PLSQL

Go Back to top

Comments

Line-comments start with two slashes

Block comments are the same as in plsql

Go Back to top

Case Sensitivity

JavaScript is case sensitive, so a variable that is declared as MyVariable can not be referred to as myVariable.
This means that a typo is even easier to make, and I repeat that you should use the “use strict” directive.

Code that can be found online seems to indicate that the most widely used case convention for JavaScript is camelCase.
So: first word in the name in all lowercase, all subsequent words in initcap.
Examples: myVariable, firstInvoiceDate, … 

Go Back to top

Quoted Strings

You may have noticed that the strings I used above are all double quoted.
This is a choice. You can choose either double or single quotes as delimiters for strings. You can even choose one for one string and the other for another string.
I’ve seen both used a lot in scripts I found online.
I do advise to choose a standard and stick to it as much as possible, just for consistency.

Since I’m using this for SQLcl and I therefor will have SQL and PLSQL statements as strings, which often have single quotes, I prefer to use double quotes as delimiter.

If you have a string that contains double quotes, you could choose to use single quotes as delimiter.
Similarly you could use double quotes as delimiter if the string contains single quotes.
But you can also just stick to your standard and escape the ‘problematic’ character with a preceding backslash.
That last option is the way to do it anyway if your string contains both single and double quotes  

Go Back to top

Concatenation

Concatenating strings in JavaScript, as with lots of other languages, is done with the “+” operator.
Like this

Go Back to top

Variables

Assuming you use the “use strict” directive (did I mention you should? You should!), you have to declare a variable before you can use it.
Variables can be declared anywhere in the code.
The datatype of a variable is determined by the value that gets assigned to it.

Either declare a variable and later assign it a value and use it…

Or declare a variable and assign it a value in one statement

Go Back to top

Constants

According to w3schools you should be able to declare constants in JavaScript since its introduction in “ECMAScript 2015”.
However, if you try to declare a constant in a JavaScript script and run it in SQLcl, the “const” keyword is not recognized.
You will run into the error message “Expected an operand but found const”

So, unfortunately currently (SQLcl 20.2) you can not (yet?? I hope) declare constants.
The only alternative at this point is to use variables and make very sure you don’t change their value along the way.

The w3schools page I mentioned above also states that the const keyword is misleading.
Well, it is, because if the constant references an object the properties of the constant object can still be modified.
If the constant references a primitive value however, the value is indeed unchangeable.
So I would still really like to be able to use const in a future version of SQLcl

Go Back to top

Arrays

An array can be initialized by using an array literal: a comma separated list of values between square brackets.
Like this

An empty array can be declared by initializing it with an empty array literal, like this

The elements in an array can be referenced for reading and writing by their index number
The index is 0-based (first element is at index 0, second element is at index 1, etc.)

The number of elements in an array can be determined with the “length” method of the array.

Go Back to top

Script parameters

You can pass parameter values to the script when executing it.
All parameter values that are passed to the script will end up in an array called “args”.

The first element, at index 0, will contain the name of the script you’re executing. So, as long as you run your code from a script rather than from the command line the array will always have at least one element.
You can reference this array to find out how many arguments/parameters were provided to the script, and get the individual parameter values from the various elements of the array.

The output when you run this script with a couple of parameters will look like this

Note that I supplied 3 parameters to the script and the args array contains 4 elements.

The first element, at index 0, contains the name of the script
It will be the name exactly as we have typed it when executing the script. Including or excluding the extension and with the case used when executing it.
In the above example I executed the script “arguments.js” so the first element will be “arguments.js”
If I would have skipped the extension when calling the script because it’s the default anyway and called the script as “ArGuMeNtS” (which in Linux is a different file but not on windows) the element would have been “ArGuMeNtS”.

Go Back to top

Comparing stuff

When you start to compare variables to each other it’s good to understand the difference between =, == and ===

x = y     is an assignment operator (assign value of y to x), not a compare operator
x == y    is true if value of x equals value of y even if their types are different (so here 5 equals “5”)
x === y   is true if value of x equals value of y AND their types are the same (so here 5 does not equal “5”)

The exclamation mark ! is used as “not”.
This is where it could get confusing if you just started to understand the above.
Because you would think the “not” versions of the comparisons would be !== and !===.
But because there is no such thing as a “do not assign” operator, we can just use != and !==.
Not very consistent, but I guess there are arguments for this and I can live with that.
So:

x != y   is true if value of x does not equal value of y
x !== y is true if value of x does not equal value of y OR their types are different

Go Back to top

Logical And/Or

If you want to do: If [expression] and [expression] you would use && as “and”.
If you want to do: If [expression] or [expression] you would use || as “or”.

Go Back to top

Functions and procedures

There is NO construction in JavaScript that is explicitly called a “procedure” as we know them in PLSQL.
Everything is called a function.
What we would call a procedure in PLSQL is just a function that doesn’t return anything in JavaScript.
So, the general syntax for a function is

And the general syntax for a procedure is

A function obviously doesn’t have to have parameters, but the parenthesis are mandatory, so if above function would have no parameters the first line would be

The parenthesis also need to be included when executing a function without parameters, like this

If you omit the parenthesis () the call of the function will not execute it, but return the function object.
We will see an example of that when I talk about creating custom commands in SQLcl in the blogpost “Being in command“

Go Back to top

ctx

We’ve seen ctx before.
There are several places where it’s mentioned that ctx has a lot of methods (Documentation says “Tons of methods”).
However, so far I’ve found no resource where anything other than “write” is mentioned.
It seems to be (or so I understand) a variable referencing a canvas element. I suppose the ‘canvas element’ it is referring to is probably the rectangular box within the SQLcl window.
This makes it logical that it contains the write method so we can ‘print’ things to that window.
I would like to know what other methods are available, but for now I’m afraid I have to say “I haven’t got a clue”.
I have tried (read: blindly guessed) a couple of sort-of-canvas-related methods but no luck at all.
I’ll keep looking.

Go Back to top

util

Util is supplied in the scripting engine and gives us some methods.
From what I’ve seen I didn’t need any of them for the first script I’m building, so I’ll have a closer look at that in the future.

Go Back to top

Java.type

We can also reference Java classes in our JavaScript using Java.type.
Both supplied and custom built Java can be used.
This increases the flexibility and possibilities of our scripts tremendously.
For my script I need to read from a client-side file, so the java.io.FileReader class seems appropriate.
We’ll see how this is done in the next blogpost.

Go Back to top

Let SQLcl execute statements

Just like we can from within a SQL script that is executed using @scriptname, we can have SQLcl execute anything it’s capable of from within the JavaScript script.
For this we use the (surprise, surprise) “sqlcl” object which is a reference to SQLcl itself and has 2 methods.
Disclaimer: I have found only 2 methods so far, but since ctx and util are supposed to have many more methods than are mentioned anywhere I looked, I can’t say for sure that this is all there is.

• setStmt (statements to be run)
• run ()

setStmt receives one parameter containing one or more statements you want SQLcl to execute.
This can be anything SQLcl can do

• A SQL statement (insert, update, delete, create, alter, ….)
• A PLSQL statement (declare begin end)
• Any command you see when you type “help” in SQLcl (set, define, bridge, ….)
• Run a SQL script (@scriptname)
• Etc.

The parameter value is placed in the SQLcl buffer by sqlcl.setStmt.
It is then executed by using the run method

After running a JavaScript script that utilizes these methods. You’ll see that all statements you had SQLcl execute can be found in the history, except of course for the ones that you excluded from being recorded by SQLcl history.

Note that a PLSQL statement should end with a slash on a new line. When trying out simple stuff it appears to work even without a slash, but there are several cases in which it will fail.
Suppose I have a script “MyPlsql.js” that uses the two sqlcl methods I mentioned to execute a very simple PLSQL block that just executes a dbms_output.put_line like this

As you can see the plsql block does not end with a slash on a new line, but if we run the script we see

Works like a charm. So no problem. Right?
Let’s make a SQL script “MyPlsql.sql” which executes this JavaScript script and then do some more SQL stuff, like so

Now, when we execute the SQL script we see

It’s just waiting there, because it thinks we haven’t completed typing our PLSQL block yet.
So let’s type a slash and enter.

It appears that everything from the start of our plsql block in the JavaScript script until it encounters a slash (manually entered after the end of the SQL script) is considered one PLSQL block. And that obviously leads to error messages.

What if we add that slash I mentioned to the plsql block?

As you can see, I just pasted a new line (\n) and a slash (/) to the end of the block.
If we run it now both directly from the sqlcl prompt and from a SQL script, it’ll work as expected

So, although it *might* work in specific, simple circumstances *don’t* use PLSQL blocks with the sqlcl methods without a slash on a new line at the end!

Go Back to top

Conclusion

We can create scripts using JavaScript and Java and execute these scripts from SQLcl.
Next challenge is to use this to enhance SQLcl functionality .
Let me invite you to the next post.

Go Back to top