Skip to Main Content
Oracle PL/SQL Programming: A Developer's Workbook
book

Oracle PL/SQL Programming: A Developer's Workbook

by Steven Feuerstein, Andrew Odewahn
May 2000
Intermediate to advanced content levelIntermediate to advanced
594 pages
11h 32m
English
O'Reilly Media, Inc.
Content preview from 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: ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle Database 12c PL/SQL Programming

Oracle Database 12c PL/SQL Programming

Michael McLaughlin
Oracle PL/SQL for DBAs

Oracle PL/SQL for DBAs

Arup Nanda, Steven Feuerstein
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 9781449324070Supplemental ContentErrata Page