Being in command

Last Updated on 2021-05-28, 18:58 (GMT+0200)

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

In the previous post I’ve created functionality for SQLcl that it doesn’t have “out of the box”.
In this post I’ll continue to build on that script, but the information here is valid for any JavaScript script you develop for SQLcl.

In short: I built some functionality in JavaScript that can read a client file and place the contents in a bindvariable.
This script resides in a file called “file2bind.js” so we can use it by telling SQLcl we want to run a script with this name and certain parameter values.
Executing that script is done using the built in command “script”

script file2bind the_file_to_be_read.txt bind_variable_contents bind_variable_pathname

1	script file2bind the_file_to_be_read.txt bind_variable_contents bind_variable_pathname

As I’ve said a couple of times in that post: I’m lazy.
So typing “script ” every time I want to execute this script will get on my nerves pretty quick.

Luckily there are two ways to avoid this

Create an alias (with limitations)
Create a custom command (with some extra work)

This post will deal with both options.

Go straight to

Create an alias
– Introduction to the alias command
– Creating an alias for the script execution
– Conclusion
Create a custom command
– Basic script to start with
– Introducing any new command
– Introducing the “no” command
Some considerations for custom commands
– Changing code
– Persistence
Custom command for the file2bind script
– Arguments of a custom command
– The splitCommandLine function
– The final custom command script

Create an alias

Introduction to the alias command

In case you’re not aware of aliases here’s an incomplete summary (type “help alias” at the prompt for more information):

In SQLcl you can create an alias for lots of things: SQL statements, PLSQL blocks, host commands (provided you end them with a semicolon), etc.
The syntax is

alias [aliasname]=[whatever needs to be executed]

1	alias [aliasname]=[whatever needs to be executed]

An alias has to be ended with either a semicolon or a slash.
After executing for example

alias nr_of_obj=select count(*) object_count from dba_objects;

1	alias nr_of_obj=select count(*) object_count from dba_objects;

we can execute that query by just “typing nr_of_obj” and [enter]

The alias command supports parameters.
If you use bindvariables in the “[whatever needs to be executed]” part they will be replaced by the parameter values.
You can name the bindvariables whatever you want. Replacing them is position based: first parameter value into first bindvariable

alias nr_of_obj=select count(*) object_count 
from  dba_objects
where object_type like upper(:b1)
  and object_name like upper(:b2)
;

alias nr_of_obj=select count(*) object_count

from dba_objects

where object_type like upper(:b1)

and object_name like upper(:b2)

;

Aliases are for the duration of the SQLcl session.
But if you type

alias save

1	alias save

all aliases will be saved to a default save file and automatically loaded when you start a new SQLcl.

Go Back to top

Creating an alias for the script execution

The above means we can make an alias “file2bind” (or whatever you want to call it) for the statement we want to execute

alias file2bind=script file2bind;

1	alias file2bind=script file2bind;

This will work. But in this form it won’t accept parameter values.

As you can see, a warning is displayed because we supply parameter values, but the alias has nothing to handle them with.
And we see the output that we would expect if we run the script without any parameter values.
Hence, no parameter values were passed on to the script.

If we try to do the bind variables trick we learned in the alias introduction

alias file2bind=script file2bind :b1 :b2 :b3;

1	alias file2bind=script file2bind :b1 :b2 :b3;

Unfortunately we get

It apparently does not see the “:b1” (etc.) as bindvariables, but just passes the string itself on as a parameter value to the script.
This is disappointing, but we could have expected it, since in both SQLcl and SQL*Plus we can pretty much only refer to bindvariables from within SQL and PLSQL.
And since this alias doesn’t execute SQL or PLSQL but an internal “script” command, this is the result.
I do hope the handling of bind variables will be expanded in a future release, but for now (version 20.2) this is it.

Go Back to top

Conclusion

We can create an alias to easily run a script, but only in two cases

The script doesn’t require any parameters
The alias is for a specific use case in which we supply the specific parameter values for that case

We could therefore create an alias for example to use the script to read the tnsnames.ora file like this

alias get_tnsnames=file2bind d:\tnsadmin\tnsnames.ora my_tnsnames tnsnames_location

1	alias get_tnsnames=file2bind d:\tnsadmin\tnsnames.ora my_tnsnames tnsnames_location

But for reading other files we would need other aliases, or call the script manually.

Or………

Go Back to top

Create a custom command

SQLcl provides the possibility to extend the command listener, instructing it to react to a command line and execute what we want.
Effectively this adds a new, custom command to the commands SQLcl knows.
Kind of like an alias, but much lower level, so you get a lot more control.

This may sound difficult, but it’s really not that hard.

Go Back to top

Basic script to start with

Before applying this to our script that reads files into bind variables, let’s first try to understand how this works by applying it to a script that doesn’t need much understanding itself.
The script I want to create a custom command for will just put a message on screen.

"use strict";
// Function writes text and an end-of-line to screen
function writeLine (line) {
  ctx.write (line + "\n");
}
function mainCode () {
  writeLine ("Yes!");
}
mainCode();

"use strict";

// Function writes text and an end-of-line to screen

function writeLine (line) {

ctx.write (line + "\n");

}

function mainCode () {

writeLine ("Yes!");

}

mainCode();

I’ll put this in a file called “testCommand.js” and execute it

Okay, the script works, now let’s focus on what is needed to turn this into a custom command.

Go Back to top

Introducing any new command

I want SQLcl to run the functionality we just built whenever I type “no”, so it will respond with “Yes!”.
By default SQLcl doesn’t know how to handle “no”, or even that it should.

To make SQLcl understand that it needs to perform some action when we type our made up command we need to extend the SQLcl command listener and then register the extended command listener with the SQLcl command registry.
Both are Java classes, so we’ll start with defining variables that reference these classes so we can use their methods and properties.

// a variable referencing the Command Registry of SQLCL
var CommandRegistry = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandRegistry");
// a variable referencing the CommandListener
var CommandListener =  Java.type("oracle.dbtools.raptor.newscriptrunner.CommandListener")

// a variable referencing the Command Registry of SQLCL

var CommandRegistry = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandRegistry");

// a variable referencing the CommandListener

var CommandListener = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandListener")

Once we’ve done the stuff that is specific for our custom command, at the end of this code we extend the listener and register it with this

// Extend the CommandListener
var myCustomCommand = Java.extend(CommandListener, {
                                    handleEvent: ??? ,
                                    beginEvent:  ??? ,
                                    endEvent:    ???
                                  }
                                 );
// Register the command
CommandRegistry.addForAllStmtsListener(myCustomCommand.class);

// Extend the CommandListener

var myCustomCommand = Java.extend(CommandListener, {

handleEvent: ??? ,

beginEvent: ??? ,

endEvent: ???

}

);

// Register the command

CommandRegistry.addForAllStmtsListener(myCustomCommand.class);

In the extension of the command listener we have to supply it three things, a handleEvent, a beginEvent and an endEvent.
All three need to be the definition of a function.

handleEvent is what is executed when a command from the command line is run.
beginEvent is what happens before a command from the command line is run. (a kind of ‘before statement trigger’)
endEvent is what happens after a command from the command line is run. (a kind of ‘after statement trigger’)

So handleEvent is where you implement your command.
If you want stuff to happen before the command is executed you implement that in beginEvent. For example, Kris Rice wrote a simple autocorrect script that uses beginEvent to correct common typo’s in commands, like automatically changing “form” into “from”.
I haven’t yet figured out a good use case for endEvent, but now that you know what you can do with beginEvent, you’ll probably know when you run into a good use case.

But how do we get that function code in there?
You can just type a function declaration without a function name (an anonymous function) in the places where there are “???” in the code above.
But for readability I think it’s better to define the functions separately from the “Java.extend” call.
In JavaScript you can declare a function expression and have it referenced by a variable.
Such a variable can then be used as that function.

var myFunction = function (param1, param2) {
                    --implementation of the functionality--
                 }

var myFunction = function (param1, param2) {

--implementation of the functionality--

}

As we’ve seen before executing a function requires parenthesis after the function name, and if the parenthesis are not included the definition of the function is returned.
We can use that here to create variables that reference the function declarations we need and supply the variable names to “Java.extend”.
So the code would look like this

// a variable referencing the Command Registry of SQLCL
var CommandRegistry = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandRegistry");
// a variable referencing the CommandListener
var CommandListener =  Java.type("oracle.dbtools.raptor.newscriptrunner.CommandListener")
// the function expressions we need
var custom_handle = function (conn,ctx,cmd) {
                    }
var custom_begin  = function (conn,ctx,cmd) {
                    }
var custom_end    = function (conn,ctx,cmd) {
                    }
// Extend the CommandListener
var myCustomCommand = Java.extend(CommandListener, {
                                    handleEvent: custom_handle ,
                                    beginEvent:  custom_begin  ,
                                    endEvent:    custom_end
                                  }
                                 );
// Register the command
CommandRegistry.addForAllStmtsListener(myCustomCommand.class);

// a variable referencing the Command Registry of SQLCL

var CommandRegistry = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandRegistry");

// a variable referencing the CommandListener

var CommandListener = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandListener")

// the function expressions we need

var custom_handle = function (conn,ctx,cmd) {

}

var custom_begin = function (conn,ctx,cmd) {

}

var custom_end = function (conn,ctx,cmd) {

}

// Extend the CommandListener

var myCustomCommand = Java.extend(CommandListener, {

handleEvent: custom_handle ,

beginEvent: custom_begin ,

endEvent: custom_end

}

);

// Register the command

CommandRegistry.addForAllStmtsListener(myCustomCommand.class);

Three variables are declared for three functions and the variables are then used in the “Java.extend”.
No parenthesis are used when referencing the (function) variables, so what happens is that the function definitions are passed on to the “Java.extend”.

As you can see, all three functions have three parameters. The listener will provide values for those parameters that can be used within the function.
Parameter “conn” is a reference to your database connection and “cmd” is a reference to the command line.
I haven’t figured out yet what ctx is. The code above is a template you can use for any custom command that you want to register.
Currently the three functions don’t do anything at all. For a real custom command they need to be developed.

Go Back to top

Introducing the “no” command

The goal is to have SQLcl act when the command “no” is typed.
So we need to implement a handleEvent function.
The beginEvent and endEvent do not have to do anything for this.

When you look at the ’template’ above you may have noticed that nowhere in the code do we tell the listener for which command this functionality is

As a consequence the code we are adding to the listener will be executed for each and every command that is executed in SQLcl.
(That is not 100% true, but we’ll get to that later, for now, just accept that it’s true).
This means that in the code that will be added to the listener we need to check if what is typed on the command line is something that this specific piece of code should react to or not. For this we need to know what exactly was typed on the command line.
The complete command line can be retrieved by the “getSql” method of the “cmd” parameter.
By the way that parameter also has a “setSql” method to write to the command line, which could be convenient in the beginEvent.

The “getSql” method returns a string, so we can use string methods on it.
This line

var commandLineStmnt = cmd.getSql().toLowerCase().trim();

1	var commandLineStmnt = cmd.getSql().toLowerCase().trim();

Will retrieve the string entered on the command line, convert all characters to lowercase and trim spaces that may be at the start or end of the string.

So, our custom_handle function expression could be something like this

var custom_handle = function (conn,ctx,cmd) {
                      var commandLineStmnt = cmd.getSql().toLowerCase().trim();
                      if ( commandLineStmnt === "no") {
                        --do whatever needs to be done when command "no" is executed--
                      }
                    }

var custom_handle = function (conn,ctx,cmd) {

var commandLineStmnt = cmd.getSql().toLowerCase().trim();

if ( commandLineStmnt === "no") {

--do whatever needs to be done when command "no" is executed--

}

But there is one thing we really should add to this, apart from the actual functionality.
I said before, the code is executed for every command executed in SQLcl, and that that wasn’t true.
What is true is that this code is executed for every command in SQLcl that has not been handled yet by the listener by the time it reaches this code.
If this code has handled a command, i.e. it came to the conclusion that a “no” command was issued, It’s a good thing to let the listener know the command was handled and no other handlers need to look at it anymore. Likewise, if the code did not handle the command because the command line was not recognized as a “no” command the listener should know about that so other handlers might pick it up.

The function tells the listener the command has been handled by returning “true“, or that the command has not been handled by returning “false“.
So our function would become

var custom_handle = function (conn,ctx,cmd) {
                      var commandLineStmnt = cmd.getSql().toLowerCase().trim();
                      if ( commandLineStmnt === "no") {
                        --do whatever needs to be done when command "no" is executed--
                        return true;   //exit the function and indicate the command has been handled
                      }
                      return false;  //exit the function and indicate the command has not been handled
                    }

var custom_handle = function (conn,ctx,cmd) {

var commandLineStmnt = cmd.getSql().toLowerCase().trim();

if ( commandLineStmnt === "no") {

--do whatever needs to be done when command "no" is executed--

return true; //exit the function and indicate the command has been handled

}

return false; //exit the function and indicate the command has not been handled

}

Note that the if statement is pretty simple. Too simple really.
In it’s current form it will recognize a case insensitive “no” command, but simply adding parameters for example, like “no param1” will cause this code to decide to not handle the command.
For this example it’s enough, but for your real world scripts you need to think about how to check for the command. You can make that as fancy as you wish.
I’ve seen scripts that basically check for “if the command line starts with ‘MyNewCommand’ then”. It works, but is risky because if ever in the future you decide to implement a new command as a variant of this one and call it “MyNewCommandSpecial”, it will not be handled, because the handler for “MyNewCommand” will have picked it up.

Later I will show how I personally handle the command line strings in a way that is a bit more robust.

Okay, we’re left with the final question. What goes where it now says “–do whatever needs to be done when command “no” is executed–“.
In our simple script above all functionality is implemented in functions. The body of the script just executes the ” mainCode();” function.

All we have to do is paste the code we have discussed at the bottom of the script and move (yes move, not copy) the invocation of the mainCode function to the custom_handle function expression.
Now it may be clear why I made a script body that is just one function call.
This makes the part where we register our own command more readable, because we only need to inject one function call.

This leads to the following script

"use strict";
// Function writes text and an end-of-line to screen
function writeLine (line) {
  ctx.write (line + "\n");
}
function mainCode () {
  writeLine ("Yes!");
}
//mainCode();  // this is moved to the custom_handle
//########################################### Custom Command Registration ####
// a variable referencing the Command Registry of SQLCL
var CommandRegistry = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandRegistry");
// a variable referencing the CommandListener
var CommandListener =  Java.type("oracle.dbtools.raptor.newscriptrunner.CommandListener")
// a Function expression for the code to handle the command
var custom_handle = function (conn,ctx,cmd) {
                      var commandLineStmnt = cmd.getSql().toLowerCase().trim();
                      if ( commandLineStmnt === "no") {
                        mainCode();
                        return true;  //exit the function and indicate the command has been handled
                      }
                      return false;  //exit the function and indicate the command has not been handled
                    }
// a Function expression for the code to run before the command is executed
var custom_begin = function (conn,ctx,cmd) {
                   }
// a Function expression for the code to run after the command is executed
var custom_end = function (conn,ctx,cmd) {
                 }
// Extend the CommandListener
var myCustomCommand = Java.extend(CommandListener, {
                                    handleEvent: custom_handle ,
                                    beginEvent:  custom_begin  ,
                                    endEvent:    custom_end
                                  }
                                 );
// Register the command
CommandRegistry.addForAllStmtsListener(myCustomCommand.class);

"use strict";

// Function writes text and an end-of-line to screen

function writeLine (line) {

ctx.write (line + "\n");

}

function mainCode () {

writeLine ("Yes!");

}

//mainCode(); // this is moved to the custom_handle

//########################################### Custom Command Registration ####

// a variable referencing the Command Registry of SQLCL

var CommandRegistry = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandRegistry");

// a variable referencing the CommandListener

var CommandListener = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandListener")

// a Function expression for the code to handle the command

var custom_handle = function (conn,ctx,cmd) {

var commandLineStmnt = cmd.getSql().toLowerCase().trim();

if ( commandLineStmnt === "no") {

mainCode();

return true; //exit the function and indicate the command has been handled

}

return false; //exit the function and indicate the command has not been handled

}

// a Function expression for the code to run before the command is executed

var custom_begin = function (conn,ctx,cmd) {

}

// a Function expression for the code to run after the command is executed

var custom_end = function (conn,ctx,cmd) {

}

// Extend the CommandListener

var myCustomCommand = Java.extend(CommandListener, {

handleEvent: custom_handle ,

beginEvent: custom_begin ,

endEvent: custom_end

}

);

// Register the command

CommandRegistry.addForAllStmtsListener(myCustomCommand.class);

After saving this script again to a file called “testCommand.js” and executing it we should be able to use the new command “no”.

Apart from the fact that SQLcl is now a bit stubborn it works as I intended.

Go Back to top

Some considerations for custom commands

I’ve said before that the code we produced is added to the listener.
I also said that it is not named code.
This has some implications.

Go Back to top

Changing code

Especially during development of a script it is very common to

Run the script to register the custom command
Execute the command and be not happy with the result
Decide to change something in the implementation
Run the script to register the custom command again
Execute the command again

At this point to your frustration you will notice that the behavior of the command didn’t change at all.
Why?
Because the second time, the code was added to the listener, but there was already a handler that gets triggered by your custom command. So now there are two.
So, when you run your command, the listener will reach the previous handler which will report back that the command has been handled, and the second handler for the command will never be executed.

UPDATE:
In fact there is now a way to unregister a previously registered custom command.
The way to do this is discussed in the next blogpost “Hit the road Jack, and don’t you come back no more….”
A way to to register and unregister with just 2 lines of code is discussed in “Your library is your paradise”

So far I haven’t found any possibility to remove such an old handler.
Since the code isn’t named code I can imagine it is in fact impossible to do so, because how would you refer to the code that you want removed?

The only way to do this that I have found is to ‘start over’, i.e. close the SQLcl window and open a new one.

This makes me stress that you really should first develop, test and debug your script as a script that needs to be run from the command line.
Only once the ‘regular’ script works without problems and you’re pleased with the results, should you add the functionality to create a custom command for it.
This minimizes the amount of times you need to open a new SQLcl window.

Go Back to top

Persistence

If a custom command is added to the listener this is not persistent.
When the SQLcl window is closed, and a new SQLcl window is opened the custom command is gone again.
This can be prevented by executing the script in your login.sql script which is executed at each login, making sure the custom command is registered again.

This is an easy to implement way to always have your custom command available.

However!!!
Remember that the code gets added to the listener each time the script is run while a SQLcl window is open.
So if you open a SQLcl window, have it open all day and during the day you go from database to database, from schema to schema, reconnecting let’s say 30 times in this window, then by the end of the day you will have added this code to the listener 30 times.

Let me stress that this doesn’t have to be a problem, because if your code is done right only the first one will be executed for your custom command and it will indicate that the command has been handled.

But if you like things to be ‘clean’ and you want to avoid this, I suggest to not run the script in login.sql but to create a batch script (for windows or equivalent for Linux or iOS) that will start SQLcl and will on startup execute the script. This will make sure the command will only get registered once for a SQLcl window.

Go Back to top

Custom command for the file2bind script

In the previous post I built a script named “file2bind.js” that can read the contents of a client file and place it in a bind variable.
With everything we learned above it’s time to turn this script into a custom command.

In an ideal world we would just copy and paste the ’template’ we discussed above for the simple script, make it invoke the mainCode function and we’re done.

If only life would be that simple
Unfortunately, the simple script doesn’t have parameters, and the file2bind script does.

Go Back to top

Arguments of a custom command

“But?” I hear you say, “in your first blogpost you told us there’s an array called args with all the arguments”.
Correct. I’m glad you were paying attention. Well done!

The problem is that the args array contains the arguments of running **the script** not the command.
The script is the one we executed to register our code as a custom command.
So, if we look at the args array in our code we’ll see that the first element is the name of the script we executed to register the custom command and since there were no parameters when running that script, there are no other elements. So, it’s useless for our purpose.

What can we do?
All we have available, through “cmd.getSql”, is the string as it was typed at the command line.
So, we will need to parse that to extract the parameters that are in there.

The original script, designed to be run directly from the command line, utilizes the args array already.
So, the easiest at this point is to create an array of arguments based on the command line.
Then we can just supply that array to our existing code instead of the args array and everything will work without changing the original code.
Another advantage of this is that we’ll have split the command line in the command and the various parameters, if any.
This means that just like in the args array, the first element will contain the command and the others will be the parameter values.
Then the event handler can simply look at the first element of the array to check if the command executed was the custom command it needs to act upon.

JavaScript has a string method “split()” which splits a string based on a separator you can choose.
Using this method with space as separator is a really quick and easy, but also dirty way to achieve this.
Why dirty? Problem is that if you ever have parameters that contain spaces (so, enclosed in double quotes) this split method will not produce the desired result.
In the case of this file2bind script that could happen if the filename contains spaces.
So, I prefer to create a function to handle this splitting.

Go Back to top

The splitCommandLine function

The function I am introducing is “splitCommandLine”

// Function takes the entire commandline and splits it into individual arguments.
// The end result is an array like the args array: first element=command executed, other elements are parameters
function splitCommandLine (commandLine) {
  var posStart;
  var posEnd;
  var clArgs = [];
  commandLine = commandLine.trim();
  while (commandLine.length > 0) {
    if (commandLine.charAt(0) == '"') {
      // Starts with double quote, so argument runs from this double quote until the next
      // Do not include the double quotes
      posStart     = 1;
      posEnd       = commandLine.indexOf ('"', 1);
      // If no other double quote to match the first one is found, raise an error
      if (posEnd == -1) {
        errorMsg ("UNMATCHED DOUBLE QUOTE");
        return;
      }
      // No trim, because a parametervalue between double quotes has to be taken literally
      clArgs[clArgs.length] = commandLine.substring(posStart, posEnd);
    } else {
      // Does not start with double quote, so argument runs from first character until the first space
      // If there is no space, then until the end of the string
      posStart     = 0;
      posEnd       = commandLine.indexOf (" ");
      if (posEnd == -1) {
        posEnd     = commandLine.length;
      }
      clArgs[clArgs.length] = commandLine.substring(posStart, posEnd).trim();
    }
    commandLine           = commandLine.substring(posEnd + 1).trim();
  }
  return clArgs
}

// Function takes the entire commandline and splits it into individual arguments.

// The end result is an array like the args array: first element=command executed, other elements are parameters

function splitCommandLine (commandLine) {

var posStart;

var posEnd;

var clArgs = [];

commandLine = commandLine.trim();

while (commandLine.length > 0) {

if (commandLine.charAt(0) == '"') {

// Starts with double quote, so argument runs from this double quote until the next

// Do not include the double quotes

posStart = 1;

posEnd = commandLine.indexOf ('"', 1);

// If no other double quote to match the first one is found, raise an error

if (posEnd == -1) {

errorMsg ("UNMATCHED DOUBLE QUOTE");

return;

}

// No trim, because a parametervalue between double quotes has to be taken literally

clArgs[clArgs.length] = commandLine.substring(posStart, posEnd);

} else {

// Does not start with double quote, so argument runs from first character until the first space

// If there is no space, then until the end of the string

posStart = 0;

posEnd = commandLine.indexOf (" ");

if (posEnd == -1) {

posEnd = commandLine.length;

}

clArgs[clArgs.length] = commandLine.substring(posStart, posEnd).trim();

}

commandLine = commandLine.substring(posEnd + 1).trim();

}

return clArgs

}

The command line is passed to the function. The while loop al line 8 extracts arguments from the command line and places each one in the next element of an array.
The code checks if the first character is a double quote. If it is, the argument is the part of the string until the next double quote.
If it isn’t then the argument is the part of the string until the next space.
The argument (with double quotes removed) is placed in an element of the array and removed from the command line string.
If it is a quoted argument all spaces are left untouched, if it isn’t, spaces before and after the argument are trimmed.

Currently this function still isn’t perfect. If an argument is enclosed in double quotes, and has a literal double quote in its value, this isn’t handled correctly yet.
I will probably enhance the function to support this in the future, but for now: if you need this, there’s your homework. Gives you a nice opportunity to choose your own method of escaping a literal double quote ?

Go Back to top

The final custom command script

Now we include this function in the ’template’ we created above for the simple script
Then we implement that ’template’ at the end of the file2bind script and move the call of the “mainCode” function into the “custom_handle” function expression.
The end result will then be

"use strict";
// Function writes text and an end-of-line to screen
// (Just a ctx.write with an added newline at the end)
function writeLine (line) {
  ctx.write (line + "\n");
}
// function displays an error message on screen
function errorMsg (message) {
  writeLine ("");
  writeLine ("##################################################");
  writeLine ("== ERROR == ");
  writeLine (message);
  writeLine ("");
  writeLine ("Call this script with parameter 'help' for usage");
  writeLine ("##################################################");
  writeLine ("");
}
// Prints the Help Text on screen
function displayHelp (scriptName, bindContents, bindPathname) {
  writeLine (''                                                                           );
  writeLine ('===========================================================================');
  writeLine ('Usage of script ' + scriptName                                              );
  writeLine ('~~~~~~~~~~~~~~~~~~~~~~~~~'                                                  );
  writeLine (scriptName + ' help'                                                         );
  writeLine ('  => Display this help text'                                                );
  writeLine (''                                                                           );
  writeLine (scriptName + ' pathname [contents-bind] [pathname-bind]'                     );
  writeLine ('  => Will read the contents of file "pathname" and place it in a'           );
  writeLine ('     bindvariable  of type "clob" with the name specified in'               );
  writeLine ('     "contents-bind".'                                                      );
  writeLine ('     The pathname of the file that is read is placed in a bindvariable'     );
  writeLine ('     with the name specified in "pathname-bind".'                           );
  writeLine ('     The names of the bindvariables are optional. If omitted, default'      );
  writeLine ('     names will be used'                                                    );
  writeLine (''                                                                           );
  writeLine ('     The reason the pathname of the file that is read is also placed in'    );
  writeLine ('     a bindvariable, is to allow checking if the correct file is read'      );
  writeLine ('     in cases where for example a substitution variable is used for the'    );
  writeLine ('     filename or its location when calling this script.'                    );
  writeLine (''                                                                           );
  writeLine ('     Only the first parameter (pathname) is mandatory'                      );
  writeLine (''                                                                           );
  writeLine ('     Param'                                                                 );
  writeLine ('     pathname      : path and name of the textfile that needs to be read'   );
  writeLine ('                     e.g. D:\\Temp\\MyFile.txt'                             );
  writeLine ('                          "D:\\Temp\\My File.txt"'                          );
  writeLine ('                          /tmp/MyFile.txt'                                  );
  writeLine (''                                                                           );
  writeLine ('     contents-bind : The name of the bindvariable in which the contents'    );
  writeLine ('                     of file "pathname" is placed'                          );
  writeLine ('                     If omitted "' + bindContents + '" is used'             );
  writeLine (''                                                                           );
  writeLine ('     pathname-bind : The name of the bindvariable in which the pathname'    );
  writeLine ('                     of file "pathname" is placed'                          );
  writeLine ('                     If omitted "' + bindPathname + '" is used'             );
  writeLine ('===========================================================================');
  writeLine (''                                                                           );
}
// Function will make SQLcl run the statement that is passed in the parameter
function runStmnt (stmnt) {
  sqlcl.setStmt(stmnt);
  sqlcl.run();
}
// Function turns feedback on or off
function setFeedback (fbStatus) {
  runStmnt ("set feedback " + fbStatus);
}
// Function that will append a string of characters to the bind variable
function appendToBind (chrBuffer , bindName) {
  // Remember a plsql block is executed by a slash
  // so add a newline+slash at the end of the statement
  var concatenationStmnt = "begin :" + bindName + " := :" + bindName + " || '" + chrBuffer + "'; end;" + "\n/";
  //debug -- In case of unexpected behavior uncomment the next line
  //writeLine (concatenationStmnt);
  runStmnt (concatenationStmnt);
}
// Function reads the file and puts data in the bind variables
function readFileToBind (nameOfFile, bindContents, bindPathname) {
  // Declare variables
  var bufferSize  = 32767;                            // Maximum number of bytes to be appended to the clob at a time
  var lineContents = '';                              // buffer (string of characters) to be appended to the bindvariable
  var fileReader  = Java.type('java.io.FileReader');  // Declare JavaScript variable referencing the FileReader Java class
  var char;                                           // Current character to be processed
  // Open fileReader object
  try {
    var clientFile = new fileReader(nameOfFile);      // Declare a variable referencing an instance of the FileReader class for the requested file
  } catch (e) {
    // If *anything* goes wrong on opening the file, report that and exit
    errorMsg ("Unable to read specified file: " + nameOfFile);
    return;
  }
  // Declare the bindvariables for the path-name and contents of the file
  runStmnt ("variable " + bindContents + " clob");
  runStmnt ("variable " + bindPathname + " varchar2(500)");
  // Put the path-name of the file in the appropriate bindvariable
  runStmnt ("exec :" + bindPathname + " := '" + nameOfFile + "'");
  // Loop until all characters in the file have been processed
  char        = clientFile.read();   // Get first character from file
  while (char !== -1) {
    // Add the character to the character-buffer
    lineContents = lineContents + String.fromCharCode(char);
    // Read the next character from the file
    char = clientFile.read();
    // If the buffer reached its maximum size OR we've reached the end of the file,
    // append the buffer to the bind variable and empty it
    if ((lineContents.length === bufferSize) || (char === -1)) {
      appendToBind (lineContents , bindContents);
      lineContents = "";
    }
  }
  // Close the fileReader object
  clientFile.close();
}
// Function contains the main functionality of the script
function mainCode (scriptArguments) {
  // Main code
  var defaultBindPathname = "ero_path";
  var defaultBindContents = "ero_file";
  var paramCount          = scriptArguments.length - 1;
  var filePath;
  var chosenBindContents;
  var chosenBindPathname;
  if (paramCount > 3) {
    errorMsg ("Too many parameters (" + paramCount + ")");
  } else if ((paramCount === 0) ||
             ((paramCount === 1) && (scriptArguments[1].toLowerCase() === "help"))
            ) {
    displayHelp (scriptArguments[0], defaultBindContents, defaultBindPathname);
  } else {
    chosenBindPathname = defaultBindPathname;
    chosenBindContents = defaultBindContents;
    if ((paramCount === 3) && (scriptArguments[3] !== "")) {
      chosenBindPathname = scriptArguments[3];
    }
    if ((paramCount >= 2) && (scriptArguments[2] !== "")) {
      chosenBindContents = scriptArguments[2];
    }
    filePath = scriptArguments[1];
    setFeedback ("off");
    readFileToBind (filePath, chosenBindContents, chosenBindPathname);
    setFeedback ("on");
  }
}
// Execute the Main Code
//mainCode (args);     // this is moved to the custom_handle
//########################################### Custom Command Registration ####
// Function takes the entire commandline and splits it into individual arguments.
// The end result is an array like the args array: first element=command executed, other elements are parameters
function splitCommandLine (commandLine) {
  var posStart;
  var posEnd;
  var clArgs = [];
  commandLine = commandLine.trim();
  while (commandLine.length > 0) {
    if (commandLine.charAt(0) == '"') {
      // Starts with double quote, so argument runs from this double quote until the next
      // Do not include the double quotes
      posStart     = 1;
      posEnd       = commandLine.indexOf ('"', 1);
      // If no other double quote to match the first one is found, raise an error
      if (posEnd == -1) {
        errorMsg ("UNMATCHED DOUBLE QUOTE");
        return;
      }
      // No trim, because a parametervalue between double quotes has to be taken literally
      clArgs[clArgs.length] = commandLine.substring(posStart, posEnd);
    } else {
      // Does not start with double quote, so argument runs from first character until the first space
      // If there is no space, then until the end of the string
      posStart     = 0;
      posEnd       = commandLine.indexOf (" ");
      if (posEnd == -1) {
        posEnd     = commandLine.length;
      }
      clArgs[clArgs.length] = commandLine.substring(posStart, posEnd).trim();
    }
    commandLine           = commandLine.substring(posEnd + 1).trim();
  }
  return clArgs
}
// a variable referencing the Command Registry of SQLCL
var CommandRegistry = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandRegistry");
// a variable referencing the CommandListener
var CommandListener =  Java.type("oracle.dbtools.raptor.newscriptrunner.CommandListener")
// a Function expression for the code to handle the command
var custom_handle = function (conn,ctx,cmd) {
                      var stmntArgs = splitCommandLine (cmd.getSql());  // split command line in array of arguments
                      // If first argument is the command we need to handle here, that call main code
                      if ( stmntArgs[0].toLowerCase() === "file2bind") {
                        mainCode (stmntArgs);
                        return true;  //exit the function and indicate the command has been handled
                      }
                      return false;  //exit the function and indicate the command has not been handled
                    }
// a Function expression for the code to run before the command is executed
var custom_begin = function (conn,ctx,cmd) {
                   }
// a Function expression for the code to run after the command is executed
var custom_end = function (conn,ctx,cmd) {
                 }
// Extend the CommandListener
var myCustomCommand = Java.extend(CommandListener, {
                                    handleEvent: custom_handle ,
                                    beginEvent:  custom_begin  ,
                                    endEvent:    custom_end
                                  }
                                 );
// Register the command
CommandRegistry.addForAllStmtsListener(myCustomCommand.class);

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

"use strict";

// Function writes text and an end-of-line to screen

// (Just a ctx.write with an added newline at the end)

function writeLine (line) {

ctx.write (line + "\n");

}

// function displays an error message on screen

function errorMsg (message) {

writeLine ("");

writeLine ("##################################################");

writeLine ("== ERROR == ");

writeLine (message);

writeLine ("");

writeLine ("Call this script with parameter 'help' for usage");

writeLine ("##################################################");

writeLine ("");

}

// Prints the Help Text on screen

function displayHelp (scriptName, bindContents, bindPathname) {

writeLine ('' );

writeLine ('===========================================================================');

writeLine ('Usage of script ' + scriptName );

writeLine ('~~~~~~~~~~~~~~~~~~~~~~~~~' );

writeLine (scriptName + ' help' );

writeLine (' => Display this help text' );

writeLine ('' );

writeLine (scriptName + ' pathname [contents-bind] [pathname-bind]' );

writeLine (' => Will read the contents of file "pathname" and place it in a' );

writeLine (' bindvariable of type "clob" with the name specified in' );

writeLine (' "contents-bind".' );

writeLine (' The pathname of the file that is read is placed in a bindvariable' );

writeLine (' with the name specified in "pathname-bind".' );

writeLine (' The names of the bindvariables are optional. If omitted, default' );

writeLine (' names will be used' );

writeLine ('' );

writeLine (' The reason the pathname of the file that is read is also placed in' );

writeLine (' a bindvariable, is to allow checking if the correct file is read' );

writeLine (' in cases where for example a substitution variable is used for the' );

writeLine (' filename or its location when calling this script.' );

writeLine ('' );

writeLine (' Only the first parameter (pathname) is mandatory' );

writeLine ('' );

writeLine (' Param' );

writeLine (' pathname : path and name of the textfile that needs to be read' );

writeLine (' e.g. D:\\Temp\\MyFile.txt' );

writeLine (' "D:\\Temp\\My File.txt"' );

writeLine (' /tmp/MyFile.txt' );

writeLine ('' );

writeLine (' contents-bind : The name of the bindvariable in which the contents' );

writeLine (' of file "pathname" is placed' );

writeLine (' If omitted "' + bindContents + '" is used' );

writeLine ('' );

writeLine (' pathname-bind : The name of the bindvariable in which the pathname' );

writeLine (' of file "pathname" is placed' );

writeLine (' If omitted "' + bindPathname + '" is used' );

writeLine ('===========================================================================');

writeLine ('' );

}

// Function will make SQLcl run the statement that is passed in the parameter

function runStmnt (stmnt) {

sqlcl.setStmt(stmnt);

sqlcl.run();

}

// Function turns feedback on or off

function setFeedback (fbStatus) {

runStmnt ("set feedback " + fbStatus);

}

// Function that will append a string of characters to the bind variable

function appendToBind (chrBuffer , bindName) {

// Remember a plsql block is executed by a slash

// so add a newline+slash at the end of the statement

var concatenationStmnt = "begin :" + bindName + " := :" + bindName + " || '" + chrBuffer + "'; end;" + "\n/";

//debug -- In case of unexpected behavior uncomment the next line

//writeLine (concatenationStmnt);

runStmnt (concatenationStmnt);

}

// Function reads the file and puts data in the bind variables

function readFileToBind (nameOfFile, bindContents, bindPathname) {

// Declare variables

var bufferSize = 32767; // Maximum number of bytes to be appended to the clob at a time

var lineContents = ''; // buffer (string of characters) to be appended to the bindvariable

var fileReader = Java.type('java.io.FileReader'); // Declare JavaScript variable referencing the FileReader Java class

var char; // Current character to be processed

// Open fileReader object

try {

var clientFile = new fileReader(nameOfFile); // Declare a variable referencing an instance of the FileReader class for the requested file

} catch (e) {

// If *anything* goes wrong on opening the file, report that and exit

errorMsg ("Unable to read specified file: " + nameOfFile);

return;

}

// Declare the bindvariables for the path-name and contents of the file

runStmnt ("variable " + bindContents + " clob");

runStmnt ("variable " + bindPathname + " varchar2(500)");

// Put the path-name of the file in the appropriate bindvariable

runStmnt ("exec :" + bindPathname + " := '" + nameOfFile + "'");

// Loop until all characters in the file have been processed

char = clientFile.read(); // Get first character from file

while (char !== -1) {

// Add the character to the character-buffer

lineContents = lineContents + String.fromCharCode(char);

// Read the next character from the file

char = clientFile.read();

// If the buffer reached its maximum size OR we've reached the end of the file,

// append the buffer to the bind variable and empty it

if ((lineContents.length === bufferSize) || (char === -1)) {

appendToBind (lineContents , bindContents);

lineContents = "";

}

// Close the fileReader object

clientFile.close();

}

// Function contains the main functionality of the script

function mainCode (scriptArguments) {

// Main code

var defaultBindPathname = "ero_path";

var defaultBindContents = "ero_file";

var paramCount = scriptArguments.length - 1;

var filePath;

var chosenBindContents;

var chosenBindPathname;

if (paramCount > 3) {

errorMsg ("Too many parameters (" + paramCount + ")");

} else if ((paramCount === 0) ||

((paramCount === 1) && (scriptArguments[1].toLowerCase() === "help"))

) {

displayHelp (scriptArguments[0], defaultBindContents, defaultBindPathname);

} else {

chosenBindPathname = defaultBindPathname;

chosenBindContents = defaultBindContents;

if ((paramCount === 3) && (scriptArguments[3] !== "")) {

chosenBindPathname = scriptArguments[3];

}

if ((paramCount >= 2) && (scriptArguments[2] !== "")) {

chosenBindContents = scriptArguments[2];

}

filePath = scriptArguments[1];

setFeedback ("off");

readFileToBind (filePath, chosenBindContents, chosenBindPathname);

setFeedback ("on");

}

// Execute the Main Code

//mainCode (args); // this is moved to the custom_handle

//########################################### Custom Command Registration ####

// Function takes the entire commandline and splits it into individual arguments.

// The end result is an array like the args array: first element=command executed, other elements are parameters

function splitCommandLine (commandLine) {

var posStart;

var posEnd;

var clArgs = [];

commandLine = commandLine.trim();

while (commandLine.length > 0) {

if (commandLine.charAt(0) == '"') {

// Starts with double quote, so argument runs from this double quote until the next

// Do not include the double quotes

posStart = 1;

posEnd = commandLine.indexOf ('"', 1);

// If no other double quote to match the first one is found, raise an error

if (posEnd == -1) {

errorMsg ("UNMATCHED DOUBLE QUOTE");

return;

}

// No trim, because a parametervalue between double quotes has to be taken literally

clArgs[clArgs.length] = commandLine.substring(posStart, posEnd);

} else {

// Does not start with double quote, so argument runs from first character until the first space

// If there is no space, then until the end of the string

posStart = 0;

posEnd = commandLine.indexOf (" ");

if (posEnd == -1) {

posEnd = commandLine.length;

}

clArgs[clArgs.length] = commandLine.substring(posStart, posEnd).trim();

}

commandLine = commandLine.substring(posEnd + 1).trim();

}

return clArgs

}

// a variable referencing the Command Registry of SQLCL

var CommandRegistry = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandRegistry");

// a variable referencing the CommandListener

var CommandListener = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandListener")

// a Function expression for the code to handle the command

var custom_handle = function (conn,ctx,cmd) {

var stmntArgs = splitCommandLine (cmd.getSql()); // split command line in array of arguments

// If first argument is the command we need to handle here, that call main code

if ( stmntArgs[0].toLowerCase() === "file2bind") {

mainCode (stmntArgs);

return true; //exit the function and indicate the command has been handled

}

return false; //exit the function and indicate the command has not been handled

}

// a Function expression for the code to run before the command is executed

var custom_begin = function (conn,ctx,cmd) {

}

// a Function expression for the code to run after the command is executed

var custom_end = function (conn,ctx,cmd) {

}

// Extend the CommandListener

var myCustomCommand = Java.extend(CommandListener, {

handleEvent: custom_handle ,

beginEvent: custom_begin ,

endEvent: custom_end

}

);

// Register the command

CommandRegistry.addForAllStmtsListener(myCustomCommand.class);

Save the above script to a file named, for example, “file2bind_cmd.js” and execute it, and after that you can just use the new command “file2bind”

And it works. Now just add “script c:\wherever\you\saved\the\file\file2bind_cmd.js” to your login.sql, and this command will always be at your fingertips.

Go Back to top

Reacties

Being in command — 3 reacties

Turloch O'Tierney op 2021-03-12 om 14:07 (GMT+0200) schreef:

(I work for Oracle)
I am mostly responsible for alias command (someone else did script command – I probably get to bug fix script command)
Alias is pretty flexible – I am trying to find where it can be improved and where more documentation is required.

Features that might be handy:
-A bit of confusion – alias uses binds (better for sql parse) instead of (more flexible) substitution variables
– however you can change binds to substitution variables with tosub command (rather than sqlplus style column new_val),
-tosub command (bind to substitution variable see help tosub) – as well as official functionality – good to force a bind reference –
i.e. force a bind reference: make sure that bind is required in alias arguments (and force the bind position in alias arguments).
issue: varchar2 only
-alias command code can be q string or bind (and plsql and SQLCL script – not SQL only)
issue: q string and bind (containing command code) not documented (it made help too long/too confusing)
-create and run alias within an alias -> can run generated SQLCL(including PLSQL/SQL) code
issue: the drop of the created alias – and potentially other feedback/text visible to user – might be hard to avoid/mask.
-‘output bind’ (I have not tried this recently)
– make sure ‘output’ bind is set before calling alias
– binds set on input are changed when alias is finished (i.e. not reset to starting value).
issue: some people might consider not set back to starting value a bug. You could use substitution variables for varchar2 output.
(substitution variables are not implicitly set/reset).

What are the problems with the alias command?
Looks like binds cannot be used ‘everywhere’ (but substitutions can (or its a bug – i.e. internally might use pre substitution sql text))
i.e. your issue is Is this what you want to work: alias file2bind=script file2bind :b1 :b2 :b3;
You _can_ do: (force new binds to be visible to alias and therefore can be used as parameters)
SQL> alias file2bind=q”
4* /
SQL> file2bind 1 2
OBJECT_COUNT
_______________
0
11:16
where: SQL> alias nr_of_obj=select count(*) object_count
2 from dba_objects
3 where object_type like upper(:b1)
4 and object_name like upper(:b2)
5* ;
11:18
You did not give your real file2bind statement. If you want output bind available – (from memory I should write a test case) you need to set it before call (so it will not be wiped on exit, only ‘new binds’ are passed in as parameters, and wiped on exit – binds changed within the alias are changed permanently – i.e. can be output binds). (For extreme flexibility – you can write sqlcl code into an bind, use that bind to create an alias, run the alias, and delete the alias. (i.e. run generated code))

Antwoorden ↓
Turloch O'Tierney op 2021-03-12 om 14:23 (GMT+0200) schreef:

(My reply come out too long/formatted badly)

I am responsible for alias command – some features that might come in handy:

#1/ help tosub – bind var to substitution var. (Or force a bind reference in script execution order)

#2/ code for alias command can be given by q string – or bind variables (not just one line not just sql – but sqlcl scripts including script, plsql and sql). (Not documented)

#3/ run generated code – by creating running deleting alias at run time

#4/ output binds – existing binds are not reset to value at start of alias time.

#5/ substitution variables are not implicitly set or reset in alias.

I might also be assigned to fix script/javascript bugs. (I have not been assigned to script bugs so far).

Antwoorden ↓
- Turloch O'Tierney op 2021-03-12 om 14:48 (GMT+0200) schreef:
  
  (As the author has coded client side script/javascript works in this case (client side file contents) rather than server side plsql),
  I am clearing up/finding any alias issues
  
  SQL> alias file2bind=q…/ was eaten by markdown
  
  was an alias set by q sting the alias contained:
  tosub thesub=:b1
  tosub thesub2=:b2
  nr_of_obj
  
  so you can call it with
  
  file2bind 1 2
  
  with :b1 = 1
  and
  :b2 = 2 passed into file2bind
  
  You could set existing binds however you want (input or output) as long as they are not command line arguments. i.e. lobs in this case for file contents to be output.
  
  Antwoorden ↓

Geef een reactie Reactie annuleren

Deze site gebruikt Akismet om spam te verminderen. Bekijk hoe je reactie-gegevens worden verwerkt.

HTML tags allowed in your comment: <a href="" title=""> <abbr title=""> <acronym title=""> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <q cite=""> <s> <strike> <pre class="" title="" data-url="">

EvROCS

Completing the puzzle

Being in command

Go straight to

Create an alias

Introduction to the alias command

Creating an alias for the script execution

Conclusion

Create a custom command

Basic script to start with

Introducing any new command

Introducing the “no” command

Some considerations for custom commands

Changing code

Persistence

Custom command for the file2bind script

Arguments of a custom command

The splitCommandLine function

The final custom command script

Reacties

Being in command — 3 reacties

Geef een reactie Reactie annuleren