A Person who won’t read has no advantage over one who can’t read
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 21st 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 topBut? 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 topThe 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
Requirements
- It should work for other files too, so I need to be able to supply a file(path)name as a parameter.
- 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.
- 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. - The script should as much as possible end gracefully with a useful error message whenever I execute it beyond its intended use.
- Executing the script without parameters or with “help” as parameter should put some assistance on screen about how to use the script.
- It should use functions, primarily to avoid repeating stuff but also for readability of the code.
- 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.
The end result
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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 |
"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 run the statement that is passed to it in SQLcl 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 bindvariable 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 bindvariables 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 bindvariable 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); |
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 topFunction writeLine
3 4 5 6 7 |
// 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"); } |
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 topFunction errorMsg
9 10 11 12 13 14 15 16 17 18 19 |
// 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 (""); } |
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 topFunction displayHelp
21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
// 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 ('' ); } |
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
Function runStmnt
63 64 65 66 67 |
// Function will make SQLcl run the statement that is passed in the parameter function runStmnt (stmnt) { sqlcl.setStmt(stmnt); sqlcl.run(); } |
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.
Function setFeedback
69 70 71 72 |
// Function turns feedback on or off function setFeedback (fbStatus) { runStmnt ("set feedback " + fbStatus); } |
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 topFunction appendToBind
74 75 76 77 78 79 80 81 82 83 84 |
// Function that will append a string of characters to the bindvariable 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); } |
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
78 |
var concatenationStmnt = "begin :" + bindName + " := :" + bindName + " || '" + chrBuffer + "'; end;" + "\n/"; |
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
1 2 |
begin :my_bindvar := :my_bindvar || 'string to be added'; end; / |
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
83 |
runStmnt (concatenationStmnt); |
Notice that there is a commented line in between that says
81 |
//writeLine (concatenationStmnt); |
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.
Function readFileToBind
86 87 88 89 90 91 92 93 94 95 96 97 98 99 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 |
// Function reads the file and puts data in the bindvariables 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 bindvariable and empty it if ((lineContents.length === bufferSize) || (char === -1)) { appendToBind (lineContents , bindContents); lineContents = ""; } } // Close the fileReader object clientFile.close(); } |
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 parametersnameOfFile
– The pathname of the file that needs to be readbindContents
– The name of the bindvariable in which the contents of the file should be placedbindPathname
– 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
91 |
var fileReader = Java.type('java.io.FileReader'); // Declare JavaScript variable referencing the FileReader Java class |
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.
96 |
var clientFile = new fileReader(nameOfFile); // Declare a variable referencing an instance of the FileReader class for the requested file |
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
128 |
clientFile.close(); |
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.
103 104 105 106 107 108 |
// 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 + "'"); |
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.
111 112 113 114 115 116 117 118 |
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(); ... |
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.
121 122 123 124 |
if ((lineContents.length === bufferSize) || (char === -1)) { appendToBind (lineContents , bindContents); lineContents = ""; } |
Function mainCode
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 |
// 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"); } } |
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.
134 135 136 |
var defaultBindPathname = "ero_path"; var defaultBindContents = "ero_file"; var paramCount = scriptArguments.length - 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.
142 143 144 145 |
if (paramCount > 3) { errorMsg ("Too many parameters (" + paramCount + ")"); } ... |
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.
145 146 147 148 149 150 |
} else if ((paramCount === 0) || ((paramCount === 1) && (scriptArguments[1].toLowerCase() === "help")) ) { displayHelp (scriptArguments[0], defaultBindContents, defaultBindPathname); } ... |
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.
151 152 |
chosenBindPathname = defaultBindPathname; chosenBindContents = defaultBindContents; |
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.
154 155 156 157 158 159 160 |
if ((paramCount === 3) && (scriptArguments[3] !== "")) { chosenBindPathname = scriptArguments[3]; } if ((paramCount >= 2) && (scriptArguments[2] !== "")) { chosenBindContents = scriptArguments[2]; } |
The pathname of the file that needs to be read is taken from the first parameter.
162 |
filePath = scriptArguments[1]; |
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.
163 164 165 |
setFeedback ("off"); readFileToBind (filePath, chosenBindContents, chosenBindPathname); setFeedback ("on"); |
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.
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 topGet help
To get a reminder of how this script should be used we can execute it with “help” as parameter
Go Back to topRead 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
1 2 3 4 |
select rpad('#',40,'#') ||chr(13)||chr(10)||:ero_file||chr(13)||chr(10)|| rpad('#',40,'#') contents from dual; |
or PLSQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
declare cn_eol constant varchar2(1) := chr(10); eol_1 integer; eol_2 integer; line varchar2(32767); begin eol_1 := instr (:ero_file, cn_eol, 1, 1); eol_2 := instr (:ero_file, cn_eol, 1, 2); line := substr(:ero_file ,eol_1 + 1 ,eol_2 - (eol_1 + 1) ); dbms_output.put_line ('line 2 = '||line); end; / |
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
1 |
Script file2bind d:\temp\try.txt "" my_custom_pathname_name |
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
Comments
A Person who won’t read has no advantage over one who can’t read — 3 Comments
HTML tags allowed in your comment: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">