|
Being an experienced Oracle Database programmer and having worked for many years
as an Informatica MDM developer, I have many times found a need for - and an ability to create - a utility for Informatica MDM
that can be useful, either as an ad-hoc tool, or even as a day-today instrument to achieve something that was deemed "not possible."
I want to share a few of my utilities with the wider Informatica MDM developer community, in the hope that
some developers may find my utilities useful in their work.
|
|
Notes about MDM SQL Script utilities:
Values are passed to the scripts as command line arguments, like this:
@script-name arg1 arg2 "arg 3" arg4
Arguments that contain spaces must be enclosed in double quotes. To pass an empty value (if accepted by the script), use two double quotes.
A single dash "-" at the end of a line is a command continuation character in SQL*Plus. It may not work with @ command;
use the equivalent START command instead.
Option keywords (those that begin with a dash) are not case-sensitive; all other arguments are case-sensitive.
An -END argument is required and must be the last one, to indicate the end of supplied arguments.
Positional arguments after the -END argument, if any, will not be processed.[*]
All SQL scripts above accept optional command line arguments. A script, when started, tries to read values of the maximum possible number
of positional arguments it accepts. The database client may ask the user to enter a value for every positional argument not provided
in the particular command line. In that case, just press Enter/Return key for every such prompt.[*]
To see a list of all arguments accepted by a particular script, start it with an -H or
--help argument; in that case, -END argument is not required.
More information about arguments' usage and specific requirements can be found inside the script, at the top.
It is strongly recommended to execute these scripts in a command-line tool like SQL*Plus or SQLcl (the former is preferred)
and not in a GUI tool like SQL Developer, probably not in TOAD (not tested yet) and certainly not in DBeaver.
* SQL*Plus and other Oracle Database clients lack important functionalities when it comes to executing SQL scripts:
- They do not provide a way for a script to programmatically determine how many arguments were passed to it. Therefore,
a script that accepts, say, up to 6 arguments has no way to know that only 3 arguments were passed, and when it tries to
process arguments 4, 5, and 6 (referring to them as &4, &5, and &6), the user is prompted to enter a value
for each of those arguments, even though all necessary arguments have been
provided in the command line. Moreover, the script would still not know whether a value was passed in the command line or just entered
by the user when prompted.
- Arguments passed to a script in the command line remain defined in the session after the script execution is completed. (One can see them by
issuing a DEFINE command without parameters - look for substitution variables named 1, 2, 3, etc. They can even be explicitly defined like this: "DEFINE 1=one" ).
As a result, the script may read and process as valid arguments the unrelated positional substitution variables that were not passed to it in the command line but
remain defined in the session from earlier invocations of the same or other scripts with more command line arguments.
A solution employed by our SQL scripts is to require a -END argument that would indicate the last
argument for the current script invocation and ensure that subsequent arguments, if any, are not processed. Our scripts will then erase (make empty) the values of
all positional substitution variables defined in the session - but will not UNDEFINE them. A script will check all substitution variables from 1 up to
the maximum number of arguments it supports; if a specific positional variable is not defined in the session, Oracle Database client (SQL*Plus, etc.)
will prompt user to enter a value, so just press the Enter/Return key at any such prompt.
|