User-Defined SQL Functions

USoft Developer supports the use of user-defined functions in constraints, batch jobs, decisions, etc. This functionality can be used on RDBMSs with only few native functions, as well as, for example, by Oracle users, to call PL/SQL functions directly.

The syntax is:

RDBMS.[$$RDBMSUSER$$ | $$RDBMSOWNER$$.]<user-defined function>([<argument> [, <argument> ...]])

<argument> ::= { <expression> | LITERAL STRING }

<user-defined function> ::= IDENTIFIER

where:

•"RDBMS" is a keyword that indicates that a user-defined function is specified.

•"$$RDBMSUSER$$" is the current RDBMS user. This is a pseudo column that is not specific for user-defined functions and can be optionally used.

•"$$RDBMSOWNER$$" is the current owner of the application. This is a pseudo column that is not specific for user-defined functions and can be optionally used.

•"<user-defined function>" is an identifier for the user defined function.

•"LITERAL" is a keyword.

•"<expression>" is any expression supported in SQL.

•"IDENTIFIER" is any name with the exception of keywords and reserved words. Reserved words are words that are reserved words in the context of the SQL parser. This may differ for the various RDBMSs. Note that column names, table names and function names are all IDENTIFIERS to the SQL parser. So this will only prevent you from using words like SELECT and FROM which have a special meaning to the SQL parser.

•"STRING" is a single-quoted string.

USoft will pass the user-defined functions through to the RDBMS. If LITERAL is used, USoft will pass the contents of the string through to the RDBMS. For the following statement, for example:

rdbms.EXTEND(SYSDATE, LITERAL 'hour to minute')

the RDBMS statement will look like:

EXTEND(SYSDATE, hour to minute)

Please take note of the following aspects and guidelines when using this feature:

•The USoft SQL parser is case-insensitive, so keywords like LITERAL and RDBMS may also contain lowercase characters.

•LITERAL can only be used in the arguments of a user-defined function. It cannot be used in normal expressions or as the argument of other functions. The SQL parser will not allow you to do so.

•LITERAL should only be used for those arguments that have an exceptional syntax that is not supported by the USoft SQL parser. LITERAL should only be used where you cannot do with normal expressions.

•The string used with the LITERAL keyword is not analyzed by the rules engine. So, if you use column names in the string, the rules engine will not be aware of that. For example, when used in a constraint, the rules engine can not use that information to determine when to evaluate the constraint. Columns used in other arguments can be recognized by the rules engine.

If you want to specify a string within the LITERAL syntax you can use the string escape sequence. For example:

LITERAL 'keyword ''my string'''

will result in:

keyword 'my string'

•The USoft SQL parser will not perform any checking on the user-defined functions or the LITERAL arguments. These will be passed through to the RDBMS. If anything is wrong, the end-user will get a runtime error. The USoft SQL parser will not notice these errors.