1. Designing Pixel-Perfect Reports in Oracle Transactional Business Intelligence
  2. Reference Information
  3. Extended Function Support in RTF Templates
  4. Extended SQL and XSL Functions

Extended SQL and XSL Functions

Publisher has extended a set of SQL and XSL functions for use in RTF templates.

The syntax for these extended functions is

<?xdofx:expression?>

for extended SQL functions or

<?xdoxslt:expression?>

for extended XSL functions.

You can't mix xdofx statements with XSL expressions in the same context. For example, assume that you had two elements, FIRST_NAME and LAST_NAME to concatenate into a 30-character field and right pad the field with the character "x". You couldn't use the following: 

<?xdofx:rpad(concat(FIRST_NAME,LAST_NAME),30, 'x')?>

because concat is an XSL expression. Instead, you could use the following:

<?xdofx:rpad(FIRST_NAME||LAST_NAME),30,'x')?>

The supported functions are shown in the following table:

SQL Statement or XSL Expression Usage Description

2+3

<?xdofx:2+3?>

Addition

2-3

<?xdofx:2-3?>

Subtraction

2*3

<?xdofx:2*3?>

Multiplication

2 div 3

<?xdofx:2 div 3?>

Division

2**3

<?xdofx:2**3?>

Exponential

3||2

<?xdofx:3||2?>

Concatenation

sdiv()

<?xdoxslt:sdiv(num1,num2, string)?>

Returns a specified value if the result of the function is not a number (NaN). In the syntax shown, num1 is the dividend; num2 is the divisor and string is the value to be returned if NaN is returned.

Examples:

<?xdoxslt:sdiv(10,0, '0')?> would yield '0'

<?xdoxslt:sdiv(10,0, 'None')?> would yield 'None'.

lpad()

<?xdofx:lpad('aaa',10,'.')?>

Pads the left side of a string with a specific set of characters. The syntax for the lpad function is: lpad(string1,padded_length,[pad_string])string1 is the string to pad characters to (the left-hand side).padded_length is the number of characters to return.pad_string is the string that is padded to the left-hand side of string1 .

rpad()

<?xdofx:rpad('aaa',10,'.')?>

Pads the right side of a string with a specific set of characters. The syntax for the rpad function is: rpad(string1,padded_length,[pad_string]).string1 is the string to pad characters to (the right-hand side).padded_length is the number of characters to return.pad_string is the string that is padded to the right-hand side of string1

trim()

<?xdoxslt:trim(' a ')?>

Removes spaces in a string. Enter the text to be trimmed, the function returns the trimmed text.

ltrim()

<?xdoxslt:ltrim(' a ')?>

Removes the leading white spaces in a string.

rtrim()

<?xdoxslt:rtrim(' a ')?>

Removes the trailing white spaces in a string.

truncate()

<?xdoxslt:truncate ( number [, integer ] )?>

Returns number truncated to integer places right of the decimal point. If integer is omitted, then number is truncated to the whole integer value. integer can be negative to truncate values left of the decimal point. integer must be an integer. Example: <?xdoxslt:truncate(-2.3333)?> returns -2 Example: <?xdoxslt:truncate(2.7777, 2)?> returns 2.77 Example: <?xdoxslt:truncate(27.7777, -1)?> returns 20

replicate()

<?xdoxslt:replicate('string', integer)?>

Replicates the specified string the specified number of times. Example: <?xdoxslt:replicate('oracle', 3)?> returns oracleoracleoracle

decode()

<?xdofx:decode('xxx','bbb','ccc','xxx','ddd')?>

Uses the functionality of an IF-THEN-ELSE statement. The syntax for the decode function is: decode(expression, search, result [,search, result]...[, default])expression is the value to compare.search is the value that is compared against expression.result is the value returned, if expression is equal to search.default is returned if no matches are found.

instr()

 
<?xdofx:instr('abcabcabc','a',2)?>

Returns the location of a substring in a string. The syntax for the instr function is: instr(string1,string2,[start_position],[nth_appearance])string1 is the string to search.string2 is the substring to search for in string1.start_position is the position in string1 where the search starts. The first position in the string is 1. If the start_position is negative, the function counts back start_position number of characters from the end of string1 and then searches towards the beginning of string1.nth appearance is the nth appearance of string2.

substr()

<?xdofx:substr('abcdefg',2,3)?>

Extracts a substring from a string. The syntax for the substr function is: substr(string, start_position, length)string is the source string.start_position is the position for extraction. The first position in the string is always 1.length is the number of characters to extract.

left()

<?xdoxslt:left('abcdefg', 3)?>

Extracts the specified number of characters from a string, starting from the left. The syntax is left(string, Numchars) For example, <?xdoxslt:left('abcdefg', 3)?> returns abc

right()

<?xdoxslt:right('abcdefg', 3)?>

Extracts the specified number of characters from a string, starting from the right. The syntax is right(string, Numchars) For example, <?xdoxslt:right('abcdefg', 3)?> returns efg

replace()

<?xdofx:replace(name,'John','Jon')?>

Replaces a sequence of characters in a string with another set of characters. The syntax for the replace function is: replace(string1,string_to_replace,[replacement_string])string1 is the string to replace a sequence of characters with another set of characters.string_to_replace is the string that is searched for in string1.replacement_string is optional. All occurrences of string_to_replace are replaced with replacement_string in string1.

to_number()

<?xdofx:to_number('12345')?>

Converts char, a value of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype that contains a number in the format that is specified by the optional format model fmt, to a value of NUMBER datatype.

format_number()

<?xdoxslt:format_number(12345, n, $_XDOLOCALE)?>

Converts a number to a string and formats the number according to the locale specified in $_XDOLOCALE and to the number of decimal positions specified in n using Java's default symbols. For example: <?xdoxslt:format_number(-12345, 2, 'fr-FR')?> returns -12 345,00

format_number()

<?xdoxslt:format_number(12345, n, s1, s2,$_XDOLOCALE)?>

Converts a number to a string and uses the specified separators: s1 for the thousand separator and s2 for the decimal separator. For example: <?xdoxslt:format_number(12345, 2, 'g', 'd', $_XDOLOCALE)?> returns 12g345d00

pat_format_number()

<?xdoxslt:pat_format_number(12345, '##,##0.00', $_XDOLOCALE)?>

Returns a number formatted with the specified pattern. For example: <?xdoxslt:pat_format_number(12345, '##,##0.00', $_XDOLOCALE)?> returns 12,345.00

to_char()

<?xdofx:to_char(value [,fmt])?>

Converts a value (character, date, or number) to VARCHAR2 datatype, using the optional number format fmt.

For example, <?xdofx:to_char(emp_id)?> returns the employee ID in string format.

For example, <?xdofx:to_char(SYSDATE, 'dd-mm-yyyy')?> returns the current date in dd-mm-yyyy format.

to_date()

<?xdofx:to_date ( char [, fmt [, 'nlsparam']] )

Converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE datatype. The fmt is a date format specifying the format of char. If you omit fmt, then char must be in the default date format. If fmt is 'J', for Julian, then char must be an integer.

format_date()

<?xdoxslt:format_date(./AnyDate,'yyyy-MM-dd','MM/dd/yyyy', $_XDOLOCALE, $_XDOTIMEZONE)?>

Reads date in one format and creates in another format.

sysdate()

<?xdofx:sysdate()?>

Returns the current date and time in XML canonical date format (for example: 1997-07-16T19:20:30.45+01:00). The datatype of the returned value is DATE. The function requires no arguments. See Display the System Date (sysdate) in Reports for information on properly formatting the sysdate in report output.

current_date()

<?xdoxslt:current_date($_XDOLOCALE, $_XDOTIMEZONE)?> Example: <?xdoxslt:current_date('ja-JP', 'Asia/Tokyo')?>

Returns the current date in yyyy-MM-dd format in the given locale and timezone. This function supports only the Gregorian calendar.

current_time()

<?xdoxslt:current_time($_XDOLOCALE, $_XDOTIMEZONE)?> Example: <?xdoxslt:current_time('ja-JP', 'Asia/Tokyo')?>

Returns the current time in the given locale and timezone. This function supports only the Gregorian calendar.

maximum_date()

<?xdoxslt:maximum_date(ELEMENT_NAME)?>

Returns the maximum value of the element in the set. Element should have date value in "yyyy-MM-dd" or "yyyy-MM-dd'T'HH:mm:ss" format. Return value is a number representing the milliseconds since January 1, 1970, 00:00:00 GMT. This number can be converted back to date using the millis_to_date() function.

minimum_date()

<?xdoxslt:minimum_date(ELEMENT_NAME)?>

Returns the minimum value of the element in the set. Element should have date value in "yyyy-MM-dd" or "yyyy-MM-dd'T'HH:mm:ss.ms" format. Return value is a number representing the milliseconds since January 1, 1970, 00:00:00 GMT. This number can be converted back to date using the millis_to_date() function.

millis_to_date()

<?xdoxslt:millis_to_date(number)?>

Translates a date value, which is a number representing the milliseconds since January 1, 1970, 00:00:00 GMT to "yyyy-MM-dd'T'HH:mm:ss.ms+00:00" format. Using the format_date() function, the formatted date value can be further converted to a required date format for display on a report.

date_to_millis()

<?xdoxslt:date_to_millis(date)?>

Translates a date value in "yyyy-MM-dd" or "yyyy-MM-dd'T'HH:mm" format to a number representing the milliseconds since January 1, 1970, 00:00:00 GMT.

minimum()

<?xdoxslt:minimum(ELEMENT_NAME)?>

Returns the minimum value of the element in the set.

date_diff()

<?xdoxslt:date_diff('y', 'YYYY-MM-DD', 'YYYY-MM-DD', $_XDOLOCALE, $_XDOTIMEZONE)?>

This function provides a method to get the difference between two dates in the given locale. The dates must be in "yyyy-MM-dd" format. This function supports only the Gregorian calendar. The syntax is as follows: <?xdoxslt:date_diff('format', 'YYYY-MM-DD', 'YYYY-MM-DD', $_XDOLOCALE, $_XDOTIMEZONE)?> where format is the time value for which the difference is to be calculated. Valid values are:

  • y - for year

  • m - for month

  • w - for week

  • d - for day

  • h - for hour

  • mi - for minute

  • s - for seconds

  • ms - for milliseconds

Example: <?xdoxslt:date_diff('y', '2000-04-08', '2001-05-01', $_XDOLOCALE, $_XDOTIMEZONE)?>

returns 1

Example: <?xdoxslt:date_diff('m', '2001-04-08', '2000-02-01', $_XDOLOCALE, $_XDOTIMEZONE)?>

returns -14

Example: <?xdoxslt:date_diff('d', '2006-04-08', '2006-04-01', $_XDOLOCALE, 'America/Los_Angeles')?>

returns -7

sec_diff()

<?xdoxslt:sec_diff('2000-04-08T20:00:00', '2000-04-08T21:00:00', $_XDOLOCALE, $_XDOTIMEZONE?>

Returns the difference between two dates in seconds in the given locale. The dates must be in "yyyy-MM-dd'T'HH:mm:ss". This function supports only Gregorian calendar. Example: <?xdoxslt:sec_diff('2000-04-08T20:00:00', '2000-04-08T21:00:00', $_XDOLOCALE, $_XDOTIMEZONE?> returns 3600

get_day

<?xdoxslt:get_day('2000-04-08', $_XDOLOCALE)?>

Returns the day value of a date in yyyy-MM-dd format in the given locale. This function supports only the Gregorian calendar. Example: <?xdoxslt:get_day('2000-04-08', $_XDOLOCALE)?> returns 8

get_month

<?xdoxslt:get_month('2000-04-08', $_XDOLOCALE)?>

Returns the month value of a date in yyyy-MM-dd format in the given locale. This function supports only the Gregorian calendar. Example: <?xdoxslt:get_month('2000-04-08', $_XDOLOCALE)?> returns 4

get_year

<?xdoxslt:get_year('2000-04-08', $_XDOLOCALE)?>

Returns the year value of a date in yyyy-MM-dd format in the given locale. This function supports only the Gregorian calendar. Example: <?xdoxslt:get_year('2000-04-08', $_XDOLOCALE)?> returns 2000

month_name

<?xdoxslt:month_name(1, 0, $_XDOLOCALE)?>

Returns the name of the month in the given locale. This function supports only the Gregorian calendar. The syntax for this function is: <?xdoxslt:month_name(month, [abbreviate?], $_XDOLOCALE)?> where month is the numeric value of the month (January = 1) and [abbreviate?] is the value 0 for do not abbreviate or 1 for abbreviate. Example: <?xdoxslt:month_name(12, 1, 'fr-FR')?> returns dec. Example" <?xdoxslt:month_name(1, 0, $_XDOLOCALE)?> returns January

maximum

<?xdoxslt:maximum(ELEMENT_NAME)?>

Returns the maximum value of the element in the set.

abs

<?xdoxslt:abs(-123.45)?>

Returns the absolute value of the number entered. Example: <?xdoxslt:abs(-123.45)?> Returns: 123.45

chr

<?xdofx:chr(n)?>

Returns the character having the binary equivalent to n in either the database character set or the national character set.

ceil()

<?xdofx:ceil(n)?>

Returns smallest integer greater than or equal to n.

floor()

<?xdofx:floor(n)?>

Returns largest integer equal to or less than n.

round (SQL function)

<?xdofx:round ( number [, integer ] )?>

Returns number rounded to integer places right of the decimal point. If integer is omitted, then number is rounded to 0 places. integer can be negative to round off digits left of the decimal point. integer must be an integer. Example: <?xdofx:round (2.777)?> returns 3 Example: <?xdofx:round (2.777, 2)?> returns 2.78

round (XSLT function)

<?xdoxslt:round ( number [, integer ] )?>

Returns number rounded to integer places right of the decimal point. If integer is omitted, then number is rounded to 0 places. integer can be negative to round off digits left of the decimal point. integer must be an integer. Example: <?xdoxslt:round (2.777)?> returns 3 Example: <?xdoxslt:round (2.777, 2)?> returns 2.78

lower()

<?xdofx:lower (char)?>

Returns char, with all letters lowercase. char can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is the same datatype as char.

upper()

<?xdofx:upper(char)?>

Returns char, with all letters uppercase. char can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is the same datatype as char.

length

<?xdofx:length(char)?>

Returns the length of char. LENGTH calculates length using characters as defined by the input character set.

greatest

<?xdofx:greatest ( expr [, expr]... )?>

Returns the greatest of the list of exprs. All exprs after the first are implicitly converted to the datatype of the first expr before the comparison.

least

<?xdofx:least ( expr [, expr]... )?>

Returns the least of the list of exprs. All exprs after the first are implicitly converted to the datatype of the first expr before the comparison.

next_element

<?xdoxslt:next_element(current-group(),.,'<element-name>')?>

Returns the next element in the current group. Returns the element that occurs after the element named. For example: <?xdoxslt:next_element(current-group(),.,'employee')?> returns the element that occurs in the current group after "employee".

prev_element

<?xdoxslt:prev_element(current-group(),..,'<element-name')?>

Returns the previous element in the current group. Returns the element that occurs before the element named. For example: <?xdoxslt:prev_element(current-group(),.,'employee')?> returns the element that occurs in the current group before "employee".

set_array

<?xdoxslt:set_array($_XDOCTX, '<name of hash table>', n, '<value>')?>

Sets a value in a hash table. Syntax is <?xdoxslt:set_array($_XDOCTX, '<name of hash table>', n, '<value>')?>where $_XDOCTX is required to set the context, <name of hash table> is the name that you supply for your table n is the index of the hash table <value> is the value to set in the hash table. For example: <?xdoxslt:set_array($_XDOCTX, 'Employee', 2, 'Jones')?> See get_array below.

get_array

<?xdoxslt:get_array($_XDOCTX, '<name of hash table>', n)?>

Returns the value at the specified index of the hash table. Syntax is <?xdoxslt:get_array($_XDOCTX, '<name of hash table>', n)?> where $_XDOCTX is required to set the context, <name of hash table> is the name you supplied for your table in set_array n is the index value of the element you want returned. For example, used in conjunction with the set_array example above, <?xdoxslt:get_array($_XDOCTX, 'Employee', 2)?> returns Jones

register_replace_string

<?xdoxslt:register_replace_string($_XDOCTX, '<string_to_be_replaced>', '<replacement_string>')?>

Registers the <string_to_be_replaced> with the <replacement_string> . After you register the <replacement_string>, use the normalize_string function to print the <replacement_string>.

For example, if you want to replace 'áàâäéèêëíìîïóòôöúùûü' string  with  'aaaaeeeeiiiioooouuuu' string and print the replacement string, first use the register_replace_string function and then use the normalize_string function.

  1. Register the replacement string. 

    <?xdoxslt:register_replace_string($_XDOCTX, 'áàâäéèêëíìîïóòôöúùûü', 'aaaaeeeeiiiioooouuuu')?>

  2. Print the replacement string.

    <?xdoxslt:normalize_string($_XDOCTX, 'áàâäéèêëíìîïóòôöúùûü')?>

    Output:  aaaaeeeeiiiioooouuuu

The following table shows supported combination functions.

SQL Statement Usage

(2+3/4-6*7)/8

<?xdofx:(2+3/4-6*7)/8?>

lpad(substr('1234567890',5,3),10,'^')

<?xdofx:lpad(substr('1234567890',5,3),10,'^')?>

decode('a','b','c','d','e','1')||instr('321',1,1)

<?xdofx:decode('a','b','c','d','e','1')||instr('321',1,1)?>

Number-To-Word Conversion

This function enables the conversion of numbers to words for RTF template output.

This is a common requirement for check printing.

Syntax of to_check_number() function:

<?xdofx:to_check_number(amount, precisionOrCurrency, caseType, decimalStyle)?>

The following table describes the function attributes.

Attribute Description Valid Value

amount

The number to be transformed.

Any number

precisionOrCurrency

For this attribute you can specify either the precision, which is the number of digits after the decimal point; or the currency code, which governs the number of digits after the decimal point. The currency code doesn't generate a currency symbol in the output.

An integer, such as 2; or a currency code, such as 'USD'.

caseType

The case type of the output.

Valid values are: 'CASE_UPPER', 'CASE_LOWER', 'CASE_INIT_CAP'

decimalStyle

Output type of the decimal fraction area.

Valid values are: 'DECIMAL_STYLE_FRACTION1', 'DECIMAL_STYLE_FRACTION2', 'DECIMAL_STYLE_WORD'

To print the check number in Indian numbering format, use the toCheckNumberIN function instead of to_check_number.

The following table displays the example function as entered in an RTF template and the returned output.

RTF Template Entry Returned Output

<?xdofx:to_check_number(12345.67, 2)?>

Twelve thousand three hundred forty-five and 67/100

<?xdofx:to_check_number(12345.67, 'USD')?>

Twelve thousand three hundred forty-five and 67/100

<?xdofx:to_check_number(12345, 'JPY', 'CASE_UPPER')?>

TWELVE THOUSAND THREE HUNDRED FORTY-FIVE

<?xdofx:to_check_number(12345.67, 'EUR', 'CASE_LOWER', 'DECIMAL_STYLE_WORDS')?>

twelve thousand three hundred forty-five and sixty-seven
<?xdofx:to_check_number(43526152,'INR','CASE_UPPER','DECIMAL_STYLE_WORDS')?> FORTY-THREE MILLION FIVE HUNDRED TWENTY-SIX THOUSAND ONE HUNDRED FIFTY-TWO
<?xdoxslt:toCheckNumberIN(43526152,'INR','CASE_UPPER','DECIMAL_STYLE_WORDS')?> FOUR CRORES THIRTY FIVE LAKHS TWENTY SIX THOUSAND ONE HUNDRED FIFTY TWO