Oracle PL/SQL Programming: A Developer's Workbook

Chapter 20. Conversion, Numeric, and Miscellaneous Functions

Beginner

Q:
20-1.	There are two related functions that accept a variable number of parameters: GREATEST and LEAST. They are used to return the greatest value in a list of values (GREATEST) or the least value in that list (LEAST).
Q:
20-2.	The snippet calls for you to use the LENGTH function to compute the length of the passed string. The wrinkle, though, is that the LENGTH function returns NULL, not zero, for a NULL string. Hence, you must wrap the call inside the NVL conversion function: /* Print each character in a string */ CREATE OR REPLACE PROCEDURE nvl_test (i_val IN VARCHAR2 DEFAULT NULL) IS str_len NUMBER := NVL (LENGTH (i_val), 0); BEGIN FOR i IN 1 .. str_len LOOP DBMS_OUTPUT.put_line (UPPER (SUBSTR (i_val, i, 1))); END LOOP; END;
Q:
20-3.	Use the USER function to return the current user: /* Set the name of the currently connected user */ BEGIN v_user := USER;
Q:
20-4.	Use the USERENV function to return a variety of environment information, such as the current session ID. The function accepts a string representing the environment variable of interest: ENTRYID Auditing entry identifier LANGUAGE Current language, territory, and character set information SESSIONID Auditing session ID TERMINAL Operating system identifier for your current terminal Here’s an example of how to display the current session ID: SQL> EXEC DBMS_OUTPUT.PUT_LINE(USERENV('SESSIONID'));
Q:
20-5.	Use the ROWIDTOCHAR function to convert a ROWID to a string. Here’s an example: ...