Last Updated on 2020-08-23, 18:06 (GMT+0200)

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

The title of this blogpost is (supposedly) a quote from Mark Twain, and I completely agree with it.
I was reminded of this quote when I was working on the subject of this blogpost.

In SQL*Plus you can’t read the contents of a client file.
Well, to be honest, you can do “get scriptname.sql” to place the contents of a SQL script in the execution buffer.
Or you could copy the file you want to read to the server and use utl_file or an external table definition.
But other than that, nothing.
No way to just read the contents of any client file.

Ok, SQL*Plus is limited, no news there.
Let’s turn to the favorite command line tool of the 21^st century, SQLcl.

SQLcl is capable of reading client files and processing them. We know that because it has the LOAD command which reads a CSV file on the client and loads its contents into a table.
But SQLcl does not have a command to just get me the contents of a client file.

So, SQL*Plus can’t read and SQLcl won’t read?

Fortunately, as we’ve discovered in the previous post, SQLcl can be expanded with custom built functionality.

Go straight to

What is this post about
But? Why?
The file2bind.js script
– Requirements
– The end result
– What happens in this script?
– Function writeLine
– Function errorMsg
– Function displayHelp
– Function runStmnt
– Function setFeedback
– Function appendToBind
– Function readFileToBind
– Function mainCode
Using the script
– Wrong number of parameter values
– Get help
– Read a file
– Custom named bindvariables
Whats next?

What is this post about

In this post I will be creating a script that allows me to put the contents of a client file into a bindvariable in SQLcl, so I can read it, manipulate it and/or take action based on it.

Go Back to top

But? Why?

Good question. Why do I want to access the contents of a client file by putting it into a bindvariable?
I could think of several reasons, but the use case I had when I decided to create this functionality had to do with my ‘playground’ database.

In my playground database I often want a clean new PDB to develop or test something new in. When I’m done I throw the PDB away.
Because I’m terribly lazy I have setup a ’template’ PDB for this with all the schema’s and basic tools I always want in such a fresh PDB.
Then I created scripts to make a clone of the template with a name provided as a parameter and one to drop a PDB once it’s no longer needed.

All seems to be great so far.

But I found I was even more lazy than that.
I noticed that every time after creating a new PDB with the call of a single SQL script I manually opened my tnsnames.ora and added a new entry for the new PDB.
And after dropping a PDB I didn’t need anymore, again I manually removed the entry from the tnsnames.ora (that is, if I didn’t forget to).

If only my SQL scripts could access that client file, so it could take care of all that automatically.

With the resulting script they can!!

Go Back to top

The file2bind.js script

With all the information in the previous post and a million obstacles on the way I eventually managed to build the script I wanted.

What I want is a script that I can execute to read my client side tnsnames.ora and put its contents in a bindvariable, which allows me to manipulate the contents and spool (or use another JavaScript to write) it back to the file.
But because I’m a bit of a whiner, I’m not happy with just that.
I have a small list of requirements/nice-to-haves

Go Back to top

Requirements

1. It should work for other files too, so I need to be able to supply a file(path)name as a parameter.
   
2. I can foresee that at some point in time I will be executing this functionality from a SQL script that dynamically assembles the file(path)name that is passed to the script, so to be able to check if the correct file was placed in the bindvariable, I want the script to create a second bindvariable which will contain the file(path)name of the file that was actually read.
   
3. The script should by default use fixed names for the two bindvariables it produces (I chose ero_file and ero_path).
   But I should be able to supply different names for the bindvariables as parameters to the script, which then should be used.
   This enables me to execute the script for more than one file, holding the results in different bindvariables simultaneously.
   
4. The script should as much as possible end gracefully with a useful error message whenever I execute it beyond its intended use.
   
5. Executing the script without parameters or with “help” as parameter should put some assistance on screen about how to use the script.
   
6. It should use functions, primarily to avoid repeating stuff but also for readability of the code.
   
7. I will eventually be creating a custom command for this script in the next blogpost. To make that work elegantly the script should be structured a bit different than strictly necessary when the script is just run as described in the previous post.
   Decisions that are made with the custom command in mind will be indicated in this post.

Easy peasy….

Let me first throw the end result at you.
Don’t let it scare you away. Following the script I’ll discuss what happens in it and how to use it.

Go Back to top

The end result

Go Back to top

What happens in this script?

Everything is implemented in functions.
After all functions have been defined, at the end the ‘body’ of the script is just a call of the “mainCode” function.
Obviously the code of the mainCode function could just as well have been the ‘body’ of the script.
But in the next blogpost I will create a custom command for this script, and then, for readability and because we need to handle parameters differently it is easier to have this code in a function that receives the script parameters as an array through a parameter.
Don’t worry about that too much until you get to the next post.

Let’s go through the various functions from top to bottom

Go Back to top

Function writeLine

A pretty simple function meant to put text on screen using ctx.write.
But as I’ve said before I’m incredibly lazy, so I don’t want to add an end-of-line each time I execute ctx.write, so I use this function that will take the “line” parameter value add an end-of-line to it and hands it over to ctx.write.

Are you finally starting to believe I’m lazy?

Go Back to top

Function errorMsg

Because I want some consistency in the way error messages will be displayed I created this function that will receive an error message in the parameter and uses the writeLine function to write it to screen in the desired format.

Go Back to top

Function displayHelp

Again nothing fancy.
Simply a function that uses the writeLine function to display the help text that is put on screen if the script is executed with “help” as parameter.
It has three parameters to be able to receive the information it needs for the text:
scriptName – The name of this JavaScript script
bindContents – The default name of the bindvariable that will contain the file contents
bindPathname – The default name of the bindvariable that will contain the pathname of the read file

Go Back to top

Function runStmnt

As explained in the previous post, if we want SQLcl to execute a statement, first we need to place in its execute buffer and then it can be run.
This requires executing 2 statements.
This function is only here to reduce that to a single function execution.
Again, laziness. To make sure I don’t forget one.

Go Back to top

Function setFeedback

A simple function that uses the runStmnt function to have SQLcl turn feedback on or off, depending on the value of the “fbStatus” parameter.

Go Back to top

Function appendToBind

Now we finally get to the core of this script.
This function, by concatenation, adds a string to a bindvariable we choose.
It has two parameters: “chrBuffer” and “bindName”.

The line

constructs a PLSQL block that concatenates the string that is in the “chrBuffer” parameter to the end of the bindvariable with the name that is defined in parameter “bindName”.
So, the PLSL block that goes into the “concatenationStmnt” variable might look like

This bindvariable already needs to exist, so it must be declared before calling this function.
Next, SQLcl is instructed to execute the PLSQL block by calling the runStmnt function

Notice that there is a commented line in between that says

It’s there for (a very bad way of) debugging.
Uncommenting it allows to see exactly which PLSQL blocks are executed, in case results are not as expected.

Go Back to top

Function readFileToBind

This function declares the two bindvariables the script should produce, opens the file that needs to be read, reads it one character at a time and appends those characters to the file-contents bindvariable.
It also places the pathname of the file in the bindvariable that is declared for that purpose.

It takes three parameters
nameOfFile – The pathname of the file that needs to be read
bindContents – The name of the bindvariable in which the contents of the file should be placed
bindPathname – The name of the bindvariable in which the pathname of the file should be placed

To be able to read a file we need to declare a variable which references a Java class called FileReader

After which we can create an instance of that class for the file we need to read, effectively opening it, and declare a variable to reference it.

This is done between “try” and “catch”, which enables me to catch any errors that may occur while opening the file.
If any error is encountered the “catch” part will be executed, which displays an error message and immediately exits the function.

At the end of the function, when we’re done with the file it is closed

If the file is opened successfully we have SQLcl execute two statements to declare two bindvariables.
One of type clob that will receive the contents of the file, and one of type varchar2 that will receive the pathname of the file.
Subsequently an “exec” statement is send to SQLcl to put the pathname of the file in the bindvariable declared for this purpose.

The method “read” of the file object returns “the next” character of the file. If there are no characters left to be read (end of file is reached) the method returns -1.
So next character will be read from file and added to the end of a string variable in a while loop until the method returns -1.

After adding a character to the string we check if the length of the string has reached a threshold, defined by variable “bufferSize”.
If that is the case, or if the last character of the file has been read, the string is added to the bindvariable by calling function “appendToBind” and the string variable is emptied.

Go Back to top

Function mainCode

The “mainCode” function is really the ‘body’ of the function, so as such it doesn’t have to be a function.
But in the next blogpost I will turn this script into a custom command in SQLcl. The code we need for that will have to include ’the body’ of this script. To keep that readable it’s a good idea to have the body be just a single call of a function. On top of that, we will see that the handling of script arguments is a bit different then too, so to make it easy on myself, the arguments are passed to the function as a parameter. All will become clear in the next post.

The “mainCode” function gets the list of arguments used to execute the script (i.e. the “args” array) in array-parameter “scriptArguments”.
It starts with checking the parameter values passed to the script and if they seem to be correct in terms of number of parameters, either the help text is displayed (if the parameters indicate that, or no parameter values were supplied) or the pathname of the file and the names of the bindvariables to be used are determined.

First some variables are defined to hold constant values (remember, we can’t use const).
Two variables with the default names for the bindvariables that this script will create and a variable that holds a number indicating the number of parameter values that were passed to the script.
Because the first element in the array that holds the arguments is the script name the number of parameters is the length of the array minus 1.

The script expects at most 3 parameter values, for filename, contents-bind name and pathname-bind name.
So if more parameters are found an error message is displayed, and the script is terminated.

If no parameter values were passed to the script, or exactly 1 and the case-insensitive value is “help”, then the help text is displayed by executing function displayHelp.

If the parameters passed all these checks, we’ve come to the conclusion that a correct amount of parameters is supplied.
First the names that need to be used for the bindvariables are initialized with their default names.

If a third parameter value is present, it represents the desired name of the bindvariable that will hold the pathname of the file that is read.
In that case the appropriate variable is updated to hold this value.
If a second parameter value is present it represents the desired name of the bindvariable in which the file contents will be placed.
In that case the appropriate variable is updated to hold this value.

The pathname of the file that needs to be read is taken from the first parameter.

Ultimately feedback is turned off, function readFileToBind is executed, passing it the pathname of the file and the bindvariable names that have just been determined and feedback is turned back on.

Go Back to top

Using the script

Now that the script is ready, does it work, and how?

The script is saved in a directory and SQLcl is running with that directory as working directory, either because it was started from there or by using CD after starting it.
Note: the script can also be executed from anywhere else by using its complete pathname rather than only its filename.

Go Back to top

Wrong number of parameter values

If the script is executed giving it more parameter values than there are parameters in the script

The error message that I wanted to show up actually does.

Go Back to top

Get help

To get a reminder of how this script should be used we can execute it with “help” as parameter

Go Back to top

Read a file

So, next step is to try and get the contents of a file into a bindvariable.
A dummy file ” d:\temp\try.txt” was created.
First a host command is executed to see what is in the file, so we can check that the contents of the bindvariable will be correct.

Now, execute the script, passing it the file pathname as parameter

Not a whole lot seems to happen. But for this script, that is a good sign. No error messages, so it seems it all went well.
We can check this by printing the bindvariables.
Which bindvariables? Well, since we didn’t tell the script that we wanted specific bindvariable names it used the defaults.
In the help screen we could see what those defaults are:
Bindvariable ero_file should have the contents of the file
Bindvariable ero_path should have the pathname of the file

Victory \o/

Now that the contents of the file is in a bindvariable, obviously anything can be done with it in SQL

or PLSQL

Go Back to top

Custom named bindvariables

We can also instruct the script to put the values in our own custom named bindvariables.
A custom name can be chosen for the file contents (second parameter) or for both file contents (second parameter) and file pathname (third parameter.
There are no named parameters in function calls in JavaScript. There appears to be a solution for that but I haven’t looked into that yet, so I can’t say anything about that for now.

There is no such thing as named parameters in JavaScript.
But because of how the handling of parameters is implemented in this script if you would like to supply a custom name for the bindvariable for the file pathname (third parameter) and use the default name for the bindvariable for the file contents (second parameter) you could supply an empty parameter value for the second parameter.
For example

Go Back to top

What’s next?

In the next post we will take this script and make sure we can call it without constantly having tot o type “script ” at the start of the command.

Go Back to top