|
Chapter 13 Numeric, LOB, and Miscellaneous Functions |
|
The following sections briefly describe the miscellaneous PL/SQL functions; these are functions that do not fall naturally into particular datatype categories.
The DUMP function "dumps," or returns, the internal representation of the input expression. DUMP is an overloaded function, as is shown by its specification:
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:
| 8 Returns result in octal notation |
| 10 Returns result in decimal notation |
| 16 Returns result in hexadecimal notation |
| 17 Returns result as individual characters |
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:
Check all IN parameters of a procedure and convert NULL values to in the default value setting of the declarations of local variable copies of the parameters:
PROCEDURE no_nulls_allowed (number1_in IN NUMBER, number2_in IN NUMBER) IS local_number1 NUMBER := NVL (number1_in, 0); local_number2 NUMBER := NVL (number2_in, 0); BEGIN ... END;
After fetching the employee information from the database, return the employee's commission as 0 whenever it is NULL.
DECLARE CURSOR emp_cur IS SELECT first_name, last_name, salary, NVL (commission, 0) commission FROM employee WHERE employee_id = :emp.employee_id; BEGIN ... END;
The SQLCODE function returns the number of the exception raised by PL/SQL. The specification for this function is:
FUNCTION SQLCODE RETURN INTEGER
SQLCODE returns values as follows:
If you reference SQLCODE outside of an exception section, it always returns 0, which means normal, successful completion.
If you explicitly raise your own user-defined exception, then SQLCODE returns a value of +1.
If PL/SQL raises the NO_DATA_FOUND exception, then SQLCODE returns a value of +100.
In all other cases, SQLCODE returns a negative value. In other words, if you try to convert a date to a string with TO_CHAR and use the wrong format mask, you might encounter the following error message:
ORA-01830: date format picture ends before converting entire input string
In this case, SQLCODE returns a value of -1830.
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:
EXCEPTION WHEN NO_DATA_FOUND THEN MESSAGE ('No match found for entry!'); WHEN OTHERS THEN MESSAGE ('Error ' || TO_CHAR (SQLCODE) || ': ' || SQLERRM); END;
The SQLERRM returns the error message associated with the specified code. The specification for this function is:
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:
User-Defined Exception
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.
The USER function returns the name of the current account. The specification for USER is as follows:
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:
The name of the Oracle account, which matches the value returned by USER.
The actual name of the user, as in: STEVEN FEUERSTEIN.
Information about the user that relates to the business of the application, such as the user's department and default printer.
Information about the preferences of the user, such as "Display Toolbar" or "Automatically pop up a list of values boxes."
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;
The USERENV function returns information about the current user session or environment. The specification for USERENV is as follows:
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:
An auditing entry identifier
The language, territory, and character set used by your session. The value is returned in this format: language_territory.characterset
An auditing session identifier
The operating system identifier for your current session's terminal. The format of this information will clearly be dependent on your underlying operating system.
The VSIZE function returns the number of bytes used by the internal representation of the input expression. The specification for VSIZE is:
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:
SQL> SELECT VSIZE (SYSDATE) FROM dual; VSIZE(SYSDATE) -------------- 8
|
|
|
|
| 13.2 LOB Function Descriptions |
|
14. Conversion Functions |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.