Page 254
Listing 11.11 Continued
DBMS_OUTPUT.put_line(`Compilation errors for ` || Ltype || ` ` || Lname); OPEN get_errors(Lname, Ltype); LOOP -- display all errors for this object FETCH get_errors INTO get_errors_rec; EXIT WHEN get_errors%NOTFOUND; IF (SUBSTR(get_errors_rec.err_text, 1, 4) = `PL/S') THEN GOTO SKIPIT; -- ignore the `PL/SQL: Statement ignored' messages END IF; cols := LENGTH(TO_CHAR(get_errors_rec.line)) + 3; DBMS_OUTPUT.put_line(`[` || TO_CHAR(get_errors_rec.line) || `] ` || RTRIM(get_errors_rec.src_text, wspc)); DBMS_OUTPUT.put_line(LPAD(`^', get_errors_rec.position + cols, `-')); DBMS_OUTPUT.put_line(`[` || TO_CHAR(get_errors_rec.line) || `] ` || get_errors_rec.err_text); DBMS_OUTPUT.put_line(dspc); -- double space errs := errs + 1; <<SKIPIT>> NULL; END LOOP; -- display all errors CLOSE get_errors; DBMS_OUTPUT.put_line(`Errors Found: ` || TO_CHAR(errs)); EXCEPTION WHEN OTHERS THEN BEGIN status := SQLCODE; IF (get_errors%ISOPEN) THEN -- cursor still open CLOSE get_errors; END IF; DBMS_OUTPUT.put_line(`showerrs: ` || SQLERRM(status)); EXCEPTION WHEN OTHERS THEN NULL; -- don't care END; END showerr; /.
Now you get something cleaner and hopefully easier to read:
execute showerr(`showerr1'); Compilation errors for PROCEDURE SHOWERR1 [9] get_errors_rec get_errors%TYPE; --------------------^ [9] PLS-00206: %TYPE must be applied to a variable or column, not `GET_ERRORS' [19] FETCH get_errors INTO get_errors_rec; ------------------------------^ [19] PLS-00320: the declaration of the type of this expression is incomplete or malformed [21] DBMS_OUTPUT.put_line(`At Line/Col: ` || get_errors_rec.line || ------------------------------------------------^
Page 255
[21] PLS-00320: the declaration of the type of this expression is incomplete or malformed [23] DBMS_OUTPUT.put_line(get_errors_rec.text); ------------------------------^ [23] PLS-00320: the declaration of the type of this expression is incomplete or malformed [26] DBMS_OUTPUT.put_line(`Errors Found: ` || TO_CHAR(errs)); --------------------------------------------------------^ [26] PLS-00201: identifier `ERRS' must be declared Errors Found: 5 PL/SQL procedure successfully completed.
I like this much better. Maybe you'd like to control how many source lines are printed before and after the afflicted line, in order to get the context. I leave that to you as an exercise.
Run the following SQL statement to see the status of all your stored programs.
column object_name format a30 column timestamp format a20 SELECT object_name, object_type, timestamp, status FROM user_objects WHERE object_type IN (`FUNCTION', `PROCEDURE', `PACKAGE', `PACKAGE BODY') ORDER BY object_name, object_type;
On my system, I get:
OBJECT_NAME OBJECT_TYPE TIMESTAMP STATUS ---------------------------- ------------ -------------------- ------ CHAR_TO_BOOL FUNCTION 1997-11-01:16:06:43 VALID CHAR_TO_NUMBER FUNCTION 1997-11-01:13:34:26 VALID SHOWERR PROCEDURE 1997-11-05:06:01:31 VALID SHOWERRS PROCEDURE 1997-11-05:05:47:10 VALID SHOWERR1 PROCEDURE 1997-11-04:19:51:31 INVALID SHOW_INDEX PROCEDURE 1997-11-01:13:17:37 VALID6 rows selected.
Notice that the procedure SHOWERR1 exists, even though it failed to compile. It's in there, source code and all. But if you try to run it, you get an error.
Another interesting thing to look at are the code statistics. To view these, run the following statement:
SELECT name, "TYPE", source_size, parsed_size, code_size, error_size FROM user_object_size WHERE "TYPE" IN (`FUNCTION', `PROCEDURE', `PACKAGE', `PACKAGE BODY') ORDER BY name, "TYPE";
Page 256
On my system, I get:
NAME TYPE SOURCE_SIZE PARSED_SIZE CODE_SIZE ERROR_SIZE ---------------- ---------- ---------- ---------- ----------- ---------- CHAR_TO_BOOL FUNCTION 391 841 546 0 CHAR_TO_NUMBER FUNCTION 375 575 384 0 SHOWERR PROCEDURE 2587 5725 3598 0 SHOWERRS PROCEDURE 2227 5035 3073 0 SHOWERR1 PROCEDURE 1149 0 0 495 SHOW_INDEX PROCEDURE 2408 4221 2629 0 6 rows selected.
Notice that SHOWERR1 doesn't have a parsed or code size because it failed to compile. Interestingly, the code and parsed sizes are larger than the source. There really isn't much correlation between source size and parsed and code sizes. It depends on the number of cursors, implicit SQL, subprograms, and so onthings that one might expect to require more storage. The code size gives you an idea how much memory in the SGA each object will occupy.
NOTE |
You can get a quick and dirty view of a particular stored subprogram's parameter list with the DESCRIBE command: |
Packages are collections of programming objects that offer persistence of data, cursors, and other language constructs. When any object within a package is referenced, the entire package is loaded into memory. While this seems like it would be a huge disk hit, remember that once it's loaded, the code is available to all users. As long as any one user is referencing the package, it will remain in the SGA. All program code inside is now available. The alternative would be to load each one separately as a stored subprogram would. Since the code is reentrant, once loaded, it is available to all users. The first disk hit is mitigated by code reentrancy. From the point of view of the server, this is extremely efficient, both in memory use and disk access.
Packages also enable subprogram overloading, a feature not available to mere stored subprograms. With overloading, the same subprogram name is repeated with different combinations and numbers of parameter types. If you look at STANDARD.SQL, you'll see several declarations for TO_CHAR, TO_DATE, and TO_NUMBER. Each declaration takes a parameter of differing data type.
Page 257
Packages actually come in two halves:
The package body contains the actual code for any subprograms mentioned in the specification.
The package specification contains all those language constructs you want to expose to the rest of the world. These can be program variables, user-defined data types, cursors, and subprograms. Anything put in the specification is globally accessible. No executable code is found here, just the declaration of subprograms. Listing 11.12 shows an example of a package header, using a few functions you've seen already.
Listing 11.12 libhdr.sqlHeader for a Proposed Library Package
CREATE OR REPLACE PACKAGE lib IS -- public global user defined types -- public global constants MAXINT CONSTANT BINARY_INTEGER := +2147483647; -- +(2^31 - 1) MININT CONSTANT BINARY_INTEGER := -2147483647; -- -(2^31 - 1) MAXDATE CONSTANT DATE := TO_DATE('31-DEC-4712 AD', `DD-MON-YYYY AD'); MINDATE CONSTANT DATE := TO_DATE('01-JAN-4712 BC', `DD-MON-YYYY BC'); -- public global cursors -- public global variables -- public global subprograms -- convert a BOOLEAN to a STRING (`TRUE', `FALSE', `NULL') FUNCTION bool_to_char(Pbool IN BOOLEAN) RETURN VARCHAR2; -- convert a STRING to BOOLEAN (TRUE, FALSE, NULL) FUNCTION char_to_bool(Pstr IN VARCHAR2) RETURN BOOLEAN; -- safe STRING to NUMBER conversion; returns NUMBER or NULL FUNCTION char_to_number(Pstr IN VARCHAR2, Pformat IN VARCHAR2 DEFAULT NULL) RETURN NUMBER; -- debug flag interface routines PROCEDURE debug_on; -- turns debug on PROCEDURE debug_off; -- turns debug off PROCEDURE debug_toggle; -- toggles debug on/off FUNCTION debug_status -- is debug on/off? RETURN BOOLEAN; END lib; /
Notice the standard CREATE OR REPLACE syntax you saw in other stored subprogram objects. Now, you don't have to explicitly create subprograms as you did for the standalone ones. Instead, you're back to declaring them like you did when they were embedded in blocks, except