Previous | Table of Contents | Next

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.

Checking the State of a Stored Program or Package

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  VALID

6 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 on—things 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:

DESC SHOWERR PROCEDURE SHOWERR Argument Name Type In/Out Default? ------------------------------ ---------------------- ------ -------- PNAME VARCHAR2(30) IN PTYPE VARCHAR2(12) IN DEFAULT

Building and Using Packages

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

Using Package Parts

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.sql—Header 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

Previous | Table of Contents | Next