Wednesday, November 30, 2016

SQLcl custom Input prompt and validations

Another quick twitter inspired blog post inspired by the SQLcl account itself.



ACCEPT is a great way to get input but validation has to happen elsewhere as there's no hooks to do something like validate a number in a range without running a sql or plsql that does the validation.

As I've covered in many examples on this blog and in our github repo of examples, SQLcl has the ability to do scripting.  Using a few lines of javascript, I can prompt, validate, loop waiting for a better answer from the user.  This is a very simple example that loops waiting for the correct answer printing a message with each failure.  While this is trivial example it shows how easy it could be to have a custom script that prompts for values then with some scripting validate those values and put it into SQLcl's list of variable used for substitutions.