A script is a single-use object that is used to call functions and assign values in a job or work flow.
Script Usage
There are many use cases for scripts. Including:
- Job initialization
- File existence
- Email alerts
- Status table checks and updates
You can execute a script before data flows for initialization steps, to calculate values that are passed on to other parts of the job or to assign values to variables.
You can execute a script to execute functions, to check for file existence for example. Your job then can use conditionals to determine execution paths.
You can also execute a script after work flows and data flows to send notifications or to record execution information such as time, or a change in the number of rows in a data set.
Data Services Scripting Language
With Data Services scripting language, you can assign values to variables, call functions, and use standard string and mathematical operators. The syntax can be used in both expressions such as WHERE clauses and scripts.
Script Statements
- Assignment statements
- Function calls
- If statements
- While statements
- Expressions
Expressions are a combination of constants, operators, functions, and variables that evaluate to a value of a given data type. Use expressions inside script statements or add expressions to data flow objects.
Basic syntax
Follow these basic syntax rules when you are creating an expression with the Data Services scripting language:
- End each statement with a semicolon (;).
- Start variable names with a dollar sign ($).
- Enclose string values in single quotation marks (' ').
- Start comments with a number sign (#).
- Specify parameters in function calls, even if the function call does not use parameters.
- Substitute the value of the expression with square brackets, for example:
Print(’The value of the start date is:[sysdate()+5]’); - Quote the value of the expression in single quotation marks with curly brackets, for example:
$StartDate = sql(’demo_target’, ’SELECT ExtractHigh FROM Job_Execution_Status WHERE JobName = {$JobName}’);
Syntax for Column and Table References in Expressions
Expressions can be used inside data flow objects and can contain column names.
The Data Services scripting language recognizes column and table names without special syntax. For example, you can indicate the start_date column as the input to a function in the Mapping tab of a query as to_char(start_date, ’dd.mm.yyyy’).
The column start_date must be in the input schema of the query. If there is more than one column with the same name in the input schema of a query, indicate which column is included in an expression by qualifying the column name with the table name. For example, indicate the column start_date in the table status as status.start_date.
Column and table names as part of SQL strings may require special syntax based on the RDBMS that the SQL is evaluated by. For example, select all rows from the LAST_NAME column of the CUSTOMER table as sql(’oracle_ds’,’select CUSTOMER.LAST_NAME from CUSTOMER’).
Quotation Marks
Quotation marks, escape characters, and trailing blanks can all have an adverse effect on your script if used incorrectly.
The type of quotation marks to use in strings depends on whether you are using identifiers or constants.
Identifiers
- An identifier is the name of the object like a table, column, data flow, or function.
- Use double quotation marks in identifiers if they contain non-alphanumeric characters.
- For example, use a double quote in the string "compute large numbers" because the string contains blanks.
Constants
- A constant is a fixed value used in computation.
- There are two types of constants:
- String constants, for example, 'Hello’ or '2007.01.23’
- Numeric constants, for example, 2.14
- Use single quotation marks in string constants and no quotation marks in numeric constants.
Escape Characters
Special characters like a single quote or a backslash must be preceded by an escape character to be evaluated properly in a string. Data Services uses the backslash as the escape character as shown in this table:
| Character | Example |
|---|---|
| Single quote (') | 'World\’s Books’ |
| Backslash (\) | 'C:\\temp’ |
NULLs, Empty Strings, and Trailing Blanks
To conform to the ANSI VARCHAR standard when dealing with NULLs, empty strings, and trailing blanks, Data Services:
- Treats an empty string (' ') as a zero length varchar value, instead of as a NULL value.
- Returns a value of FALSE when you use the operators Equal (=) and Not Equal (<>) to compare to a NULL value.
- Provides IS NULL and IS NOT NULL operators to test for NULL values.
- Treats trailing blanks as regular characters when reading from all sources, instead of trimming them.
- Ignores trailing blanks in comparisons in transforms and functions.
When you assign an empty string (' ') to a variable, Data Services treats the value of the variable as a zero-length string.
An error results if you assign an empty string to a variable that is not a varchar. Use the NULL constant to assign a NULL value to a variable of any type.
Check whether a column (COLX) is null or not with these expressions:
- COLX IS NULL
- COLX IS NOT NULL
Use the function NVL to remove NULL values. Data Services does not check for NULL values in data columns.
For more information on scripting language, see the designer guide
and the reference guide