Previous | Table of Contents | Next

Page 243

  -- local record variables
  show_index_rec show_index_cur%ROWTYPE;  -- based on cursor
  old_index_info show_index_cur%ROWTYPE;  -- used to detect control break
  -- local variables
  status NUMERIC;
  local_table all_indexes.table_name%TYPE;
BEGIN
  status := 0;
  local_table := UPPER(Ptable);  -- make upper case
  old_index_info.table_owner := `GARBAGE_OWNER';  -- initialize
  old_index_info.table_name  := `GARBAGE_TABLE';
  IF (local_table IS NULL) THEN  -- one table or all?
    DBMS_OUTPUT.put_line(`User ` || USER || `: Index Information for
All ÂTables');
  ELSE

    DBMS_OUTPUT.put_line(`User ` || USER || `: Index Information for Table `
|| Âlocal_table);
  END IF;  -- one table or all?
  OPEN show_index_cur(local_table);
  LOOP       -- get index information
    FETCH show_index_cur INTO show_index_rec;
    EXIT WHEN show_index_cur%NOTFOUND;
    IF (old_index_info.table_owner != show_index_rec.table_owner OR
        old_index_info.table_name  != show_index_rec.table_name) THEN
-- Âcontrol break
      DBMS_OUTPUT.put_line(TB);  -- double spacing between tables
    END IF;
    DBMS_OUTPUT.put_line(`Table Owner: ` || show_index_rec.table_owner || TB ||
                         `Table: ` || show_index_rec.table_name);

    DBMS_OUTPUT.put_line(`Index: ` || show_index_rec.index_name 
Â|| TB || ` in ` || show_index_rec.tablespace_name || TB || show_index_rec.uniqueness || TB || show_index_rec.status); old_index_info := show_index_rec; -- copy new values to old END LOOP; -- get index information CLOSE show_index_cur; EXCEPTION WHEN OTHERS THEN BEGIN status := SQLCODE; DBMS_OUTPUT.put_line(`show_index: ` || SQLERRM(status)); -- display error message IF (show_index_cur%ISOPEN) THEN -- close any open cursors CLOSE show_index_cur; END IF; EXCEPTION WHEN OTHERS THEN NULL; -- don't care END; END show_index; /

This time, the server responds with:

Procedure created.

Page 244

To execute this procedure, you can call it from an anonymous PL/SQL block, or you can use the EXECUTE command for one-liners.

NOTE
The EXECUTE command can only be used to run one-line statements. If your statement spans two or more lines, you must use an anonymous PL/SQL block (using BEGIN .. END). If you can cram two or more statements onto a single line, you can still use EXECUTE. In fact, EXECUTE expands to a BEGIN .. END block on one line. It's just a shorthand.

EXECUTE show_index(`DEPT');

On my system I get:

User SYSTEM: Index Information for Table DEPT

Table Owner: SYSTEM     Table: DEPT
Index: DEPT_PRIMARY_KEY  in USER_DATA   UNIQUE  VALID

PL/SQL procedure successfully completed.

The first time I run this stored procedure, I have to wait for the server to load it into memory. On subsequent calls, running it is much faster because it's already loaded.

This particular example shows some fine features you'll want in your own stored procedures. I always try to be consistent with the organization of local variable declarations, always putting them in the same order. Also, notice the block inside the exception handler to close the cursor in case an error leaves it open. If you don't do this and you do have an exception, the next time you run the stored procedure you will immediately bomb with a CURSOR_ALREADY_OPEN exception. Your users would have to reconnect in order to clear the open cursor because the cursor remains open for the duration of a session until either the cursor is closed or the session is terminated.

If the user wants all tables and their indexes that are visible, simply drop the single input parameter (and parentheses in this case), as with:

EXECUTE get_index;

You have to put yourself in your users' (in this case, developers') shoes to anticipate the various ways in which they might want to use this tool. Better yet, go ask them. You'll be surprised at the answers you'll get.

Calling Stored Programs from SQL

Suppose you aren't satisfied with the Oracle-supplied TO_NUMBER() built-in function. Your complaint with it might be that when a character-to-number conversion fails because the string doesn't represent a valid number, the SQL fails and terminates abruptly. What you'd prefer is that, at the very least, the error is handled gracefully so that processing can continue with the rest of the data set. Let's try to cure this problem with a stored function, as shown in Listing 11.4.

Page 245

Listing 11.4 char2num.sql—Character-to-Number Conversion

CREATE OR REPLACE FUNCTION char_to_number(Pstr IN VARCHAR2, Pformat IN
VARCHAR2 DEFAULT NULL)
RETURN NUMBER IS
BEGIN
  IF Pformat IS NULL THEN  -- optional format not supplied
    RETURN (TO_NUMBER(Pstr));
  ELSE
    RETURN (TO_NUMBER(Pstr, Pformat));  -- format supplied
  END IF;  -- test for optional format
EXCEPTION
WHEN OTHERS THEN  -- unknown value
  RETURN (NULL);
END char_to_number;
/

You can run this stored function in two ways:

First, try it from a PL/SQL block, as shown in Listing 11.5.

Listing 11.5 testc2n1.sql—Testing char_to_number() from a PL/SQL Block

DECLARE
  v VARCHAR2(1) := 0;
  w VARCHAR2(10) := `999.999';      -- try a floating point number
  x VARCHAR2(11) := `+4294967295';  -- try a big positive number
  y CHAR(11) := `-4294967296';      -- try a big negative number
  z VARCHAR2(10) := `garbage';       -- this is NOT a number!
BEGIN
  -- stored function returns NULL on error, so convert NULL to error message
  DBMS_OUTPUT.put_line(v || ` is ` || NVL(TO_CHAR(char_to_number(v)), `NOT
A ÂNUMBER!'));
  DBMS_OUTPUT.put_line(w || ` is ` || NVL(TO_CHAR(char_to_number(w)), `NOT
A ÂNUMBER!'));
  DBMS_OUTPUT.put_line(x || ` is ` || NVL(TO_CHAR(char_to_number(x)), `NOT
A ÂNUMBER!'));
  DBMS_OUTPUT.put_line(y || ` is ` || NVL(TO_CHAR(char_to_number(y)), `NOT
A ÂNUMBER!'));
  DBMS_OUTPUT.put_line(z || ` is ` || NVL(TO_CHAR(char_to_number(z)), `NOT
A ÂNUMBER!'));
END;
/

The server responds:

0 is 0
999.999 is 999.999
+4294967295 is 4294967295
-4294967296 is -4294967296
garbage is NOT A NUMBER!

Page 246

PL/SQL procedure successfully completed.

Okay, now let's try it in a SQL statement, as shown in Listing 11.6.

Listing 11.6 testc2n2.sql—Running char_to_number() from SQL

SELECT `0' str,
       NVL(TO_CHAR(char_to_number(`0')), ` IS NOT A NUMBER!') num FROM DUAL;
SELECT `999.999' str,
       NVL(TO_CHAR(char_to_number(`999.999')), ` IS NOT A NUMBER!') num
FROM ÂDUAL;
SELECT `+4294967295' str,
       NVL(TO_CHAR(char_to_number(`+4294967295')), ` IS NOT A NUMBER!') num
FROM ÂDUAL;
SELECT `-4294967296' str,
       NVL(TO_CHAR(char_to_number(`-4294967296')), ` IS NOT A NUMBER!') num
FROM ÂDUAL;
SELECT `garbage' str,
       NVL(TO_CHAR(char_to_number(`garbage')), ` IS NOT A NUMBER!') num
FROM ÂDUAL;

And you get the same answers as above. It looks kind of goofy converting back to a string when you just got through converting from a string to number, but still it verifies the operation of the stored function, particularly in the last query shown.

Just for fun, let's write the converse of bool_to_char(), called appropriately char_to_bool() (see Listing 11.7).

Listing 11.7 chr2bool.sql—Converting a String to Boolean

CREATE OR REPLACE FUNCTION char_to_bool(Pstr IN VARCHAR2) RETURN BOOLEAN IS
  Lstr VARCHAR2(32767);  -- max string length
  Lbool BOOLEAN := NULL; -- local Boolean value (default)
BEGIN
  Lstr := UPPER(LTRIM(RTRIM(Pstr)));  -- remove leading/trailing spaces, 
Âuppercase IF (Lstr = `TRUE') THEN Lbool := TRUE; ELSIF (Lstr = `FALSE') THEN Lbool := FALSE; END IF; RETURN(Lbool); END char_to_bool; /

Now, let's test it with the PL/SQL statement shown in Listing 11.8.

Listing 11.8 testc2b1.sql—Testing char_to_bool() in a PL/SQL Block

BEGIN
  IF (char_to_bool(`   true    `)) THEN
    DBMS_OUTPUT.put_line(`''Tis True!');

Previous | Table of Contents | Next