FUNCTION DUMP (expr_in DATE [, return_format_in BINARY_INTEGER [, start_position_in BINARY_INTEGER [, length_in BINARY_INTEGER ) RETURN VARCHAR2 FUNCTION DUMP (expr_in NUMBER [, return_format_in BINARY_INTEGER [, start_position_in BINARY_INTEGER [, length_in BINARY_INTEGER ) RETURN VARCHAR2 FUNCTION DUMP (expr_in VARCHAR2 [, return_format_in BINARY_INTEGER [, start_position_in BINARY_INTEGER [, length_in BINARY_INTEGER ) RETURN VARCHAR2
where expr_in is the expression to be dumped, return_format_in is a numeric code specifying the format of the returned string, start_position_in is the starting position of the portion of the internal representation to be returned, and length_in is the length of the portion to be returned. The starting position and length arguments perform the same way as in the SUBSTR function described in Chapter 11, Character Functions .
Valid return format numbers are:
The default for the return format is 10 (decimal), the default for the starting position is 1, and the default for length is the full length of the value. So, a fully default call to DUMP will return the full internal representation in decimal notation.
If expr_in IS NULL, then DUMP returns NULL.
The GREATEST function evaluates a list of values and returns the greatest value in that list. (The LEAST function, discussed below, returns the least value.) GREATEST accepts two or more arguments, and there is no upper limit on the number of values you can pass to GREATEST, which makes it especially useful. The specification for GREATEST is:
FUNCTION GREATEST (expr1, expr2 [, expr3 ...) RETURN DATE FUNCTION GREATEST (expr1, expr2 [, expr3 ...) RETURN VARCHAR2 FUNCTION GREATEST (expr1, expr2 [, expr3 ...) RETURN NUMBER
The datatype of the return value of the GREATEST function is determined by the datatype of the first expression (expr1) in the list. In addition, PL/SQL must convert all the additional expressions in the list (expr2, expr3, and so on) to the same datatype as expr1, so they must all be compatible.
This example finds the greatest (most recent) of three dates:
GREATEST (SYSDATE, :emp.hire_date, '13-JAN-1994')
My first expression is a call to the SYSDATE function. My second expression is an Oracle Forms item of type DATE. My third expression is a literal string. This string is converted to a date by PL/SQL with an internal call to TO_DATE. The comparison of the values then proceeds.
The next example finds the greatest (last in alphabetical order) of two strings:
GREATEST (SUBSTR (text_chunk, INSTR (text_chunk, ';') + 1), last_command_entered)
The first expression is comprised of nested calls first to SUBSTR and then to INSTR, to find that part of the text_chunk variables that comes after the first semicolon (
The LEAST function, the opposite of the GREATEST function, evaluates a list of values and returns the least value in that list. LEAST accepts two or more arguments; there is no upper limit on the number of values you can pass to LEAST, which makes it especially useful. The specification for LEAST is as follows:
FUNCTION LEAST (expr1, expr2 [, expr3 ...) RETURN DATE FUNCTION LEAST (expr1, expr2 [, expr3 ...) RETURN VARCHAR2 FUNCTION LEAST (expr1, expr2 [, expr3 ...) RETURN NUMBER
The datatype of the return value of the LEAST function is determined by the datatype of the first expression (expr1) in the list. In addition, PL/SQL must convert all the additional expressions in the list (expr2, expr3, and so on) to the same datatype as expr1, so they must all be compatible.
The NVL function offers a concise way to return or substitute a non-NULL value if the specified value is NULL. You can think of NVL as an abbreviation for "if Null VaLue, then return X." The NVL function is massively overloaded because any type of data can also have a NULL value. Here is the specification:
FUNCTION NVL (string_in IN CHAR, replace_with_in IN CHAR) RETURN CHAR FUNCTION NVL (string_in IN VARCHAR2, replace_with_in IN VARCHAR2) RETURN VARCHAR2 FUNCTION NVL (date_in IN DATE, replace_with_in IN DATE) RETURN DATE FUNCTION NVL (date_in IN NUMBER, replace_with_in IN DATE) RETURN NUMBER FUNCTION NVL (date_in IN CHAR, replace_with_in IN DATE) RETURN BOOLEAN
Note that the CHAR version of NVL also returns a CHAR, or fixed-length, value.
For dates, if date_in is NOT NULL, then return date_in; otherwise, return replace_with_in. The NVL function in this case is therefore equivalent to the following IF statement:
IF date_in IS NOT NULL THEN RETURN date_in; ELSE RETURN replace_with_in; END IF;
NVL simply provides a much cleaner and more concise way of coding this functionality. And since it is a function, you can call it inline to provide substitution of NULL values where such a state of data would disrupt your program. For example, if you calculate the total compensation of an employee as salary plus compensation, then the expression:
salary + commission
will be NULL when commission is NULL. With NVL, however, you can be sure that the calculated value will make sense:
salary + NVL (commission, 0)
The next two examples show some other ways to use the NVL function:
FUNCTION SQLCODE RETURN INTEGER
SQLCODE returns values as follows:
You will find SQLCODE and its sibling function, SQLERRM, most useful in the WHEN OTHERS exception handler. If an error is trapped by WHEN OTHERS, you do not know which exception was raised or which error was encountered. You can, however, use SQLCODE to find out, as shown in this example:
FUNCTION SQLERRM (code_in IN INTEGER := SQLCODE) RETURN VARCHAR2
If you do not provide an error code when you call SQLERRM, it uses the value returned by SQLCODE (see the preceding section). If SQLCODE returns 0, then SQLERRM returns the following message:
ORA-0000; normal, successful completion
If PL/SQL has raised an internal Oracle error or you pass a negative value to SQLERRM, then the function returns the error message provided by Oracle Corporation. If you pass (or allow SQLCODE to pass) a value of +100 to SQLERRM, it returns this message:
ORA-01403: no data found
Any other positive value passed to SQLERRM will result in this message:
The maximum length of a message returned by SQLERRM is 512 bytes. This length includes the error code and all nested messages that may have been flagged by the compiler.
The UID function returns an integer that uniquely identifies the current user. This integer is generated by the Oracle RDBMS when a user connects to the database. The specification for UID is as follows:
FUNCTION UID RETURN NUMBER
When called inline, the UID function looks like a variable since it has no arguments. Remember that when you call UID you will actually issue a SQL call to the RDBMS to extract the UID information for the user. Furthermore, in a distributed SQL statement, the UID always returns the value identifying the user on the local database. You cannot obtain the UID for connections to other, remote databases.
FUNCTION USER RETURN VARCHAR2
Like UID, when called inline, the USER function looks like a variable since it has no arguments. Remember that when you call USER you actually issue a SQL call to the RDBMS to extract the account name for the user. Furthermore, in a distributed SQL statement, the USER always returns the value identifying the user on the local database. You cannot obtain the USER for connections to other, remote databases.
The most common use for the USER function is to initialize an application session with configuration for a user.
Most of the applications I build have a system configuration table (with one row for each system or application) and a separate user configuration table (with one row for each user in each application). This user configuration table might have the following columns:
Assuming an Oracle Forms-based set of screens, each screen the user is able to enter from a Windows icon will contain a When-New-Form-Instance trigger. This trigger calls a procedure to transfer the information from the user configuration table to GLOBAL variables that are then available to all screens for the duration of the session. A sample initialization procedure follows:
PROCEDURE configure_user_globals IS /* || I identify the row from the configuration table using the || USER function provided by PL/SQL. */ CURSOR user_cur IS SELECT username, default_printer, toolbar_status, ... etc ... FROM user_configuration WHERE user_account_name = USER; BEGIN OPEN user_cur; FETCH user_cur INTO :GLOBAL.username, :GLOBAL.default_printer ... etc ...; IF user_cur%NOTFOUND THEN CLOSE user_cur; MESSAGE (' You are not authorized to run this screen!'); RAISE FORM_TRIGGER_FAILURE; ELSE CLOSE user_cur; END IF; END configure_user_globals;
FUNCTION USERENV (info_type_in IN VARCHAR2) RETURN VARCHAR2
where info_type_in can be one of these values (specified in a named constant or a literal string in single quotes). The following list gives options and descriptions of what they return:
FUNCTION VSIZE (expr_in IN DATE) RETURN NUMBER FUNCTION VSIZE (expr_in IN VARCHAR2) RETURN NUMBER FUNCTION VSIZE (expr_in IN NUMBER) RETURN NUMBER FUNCTION VSIZE (expr_in IN CHAR) RETURN NUMBER
If the expression is NULL, then VSIZE returns NULL.
I am not sure when I would use this function in a program, but it comes in very handy when I get a call like this on the phone: "How many bytes does a date value take up in the database?" I could try to remember the answer (it sure seems like something I once knew). I could try to hunt down the answer in one of the many database administration manuals from Oracle, or I could execute the following SQL statement:
SQL> SELECT VSIZE (hiredate) FROM emp WHERE ROWNUM=1 VSIZE(HIREDATE) --------------- 7
I included the WHERE clause for ROWNUM equal to 1 so that I would receive the answer (7 bytes) only once. Otherwise, I would have had the "opportunity" to learn that the VSIZE of hiredate is 7 -- for every record in the emp table.
Interestingly, if you apply VSIZE to the SYSDATE function, you get a slightly different answer:
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.