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. |
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.
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.sqlCharacter-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.sqlTesting 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.sqlRunning 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.sqlConverting 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.sqlTesting char_to_bool() in a PL/SQL Block
BEGIN IF (char_to_bool(` true `)) THEN DBMS_OUTPUT.put_line(`''Tis True!');