Previous | Table of Contents | Next

Page 247

  END IF;
  IF (NOT char_to_bool(`   False')) THEN
    DBMS_OUTPUT.put_line(`''Tis Untrue!');
  END IF;
  IF (char_to_bool(`NULL     `) IS NULL) THEN
    DBMS_OUTPUT.put_line(`Don''t Care!');
  END IF;
END;
/

The server responds:

'Tis True!
'Tis Untrue!
Don't Care!
PL/SQL procedure successfully completed.

Now try it in a SQL statement.

SELECT char_to_bool(`true') FROM DUAL;

But this time, the server responds:

ERROR:
ORA-06550: line 1, column 12:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

no rows selected

What happened? There's no Boolean data type in SQL. The return value of the function is an unknown SQL data type. The PL/SQL engine has determined that the data type is inappropriate within the context of a SQL statement. The SQL engine subsequently returns no rows.

Calling Stored Programs from PL/SQL

You've already seen how to invoke a stored subprogram from a PL/SQL block. What's a high value use for them? Have you ever tried to compose a query that has a 14-table join? Instead, create a bunch of single-row lookup routines for each master table whose key you would have used in a join. Then, in a PL/SQL block, code just the minimal tables required to drive a cursor loop. Inside the loop, perform the single-row lookups for the attendant data values. Using this technique, I've seen speed improvements of 4,000%. Queries that used to run in 20 minutes can be run in 30 seconds or faster, using this technique. If you're using some sort of report writer to format the output, you can write the data to a temporary table, which can then be scanned by the reporting tool. Obviously, this slows things down somewhat, but it's still faster than bogging down the Oracle Server with a huge, complex query. Plus, it's easier to verify the correctness of the output. If you rigorously test the single-row lookup routines, the only facet you'll have to validate is the substantially smaller query.

Page 248

Now think about how many times you had to code an outer join in a complex query. In a PL/SQL block, it's a piece of cake to test the single-row lookup for no data found, and skip to the end of the loop or carry on, as needed. For example, a Claims Processing program might have a loop in it that looks something like this:

LOOP  -- process all claims for the period selected
  FETCH claims_cur INTO claims_rec;
  EXIT WHEN claims_cur%NOTFOUND;
  -- get related table info
  get_claimant_info(claims_rec.claimant_ssn, status);
  get_provider_info(claims_rec.provider_id, status);
  get_approval_info(claims_rec.approvedby, status);
  IF (status != 0) THEN  -- no approval on file!
    GOTO SKIPIT;  -- skip processing claim
  END IF;
  ...  -- more single row lookups, claims processing
  <<SKIPIT>>  -- continue with next claim
END LOOP;  -- process all claims for the period selected

where status is passed back the SQLCODE result value of the single-row lookup. To make this example really useful, you need to dump information about why the claim wasn't processed (no approval) to some sort of application error table. Even better, inside the lookup routine you could dump the lookup key and error code to an errors table of your design. Then you could later determine the subroutine and key values that caused a claim to go unprocessed. The calling program could then dump additional data ( such as context information) to make troubleshooting easier.

Another use for stored procedures is to implement business rules. Then, the rule can be invoked from a trigger, a client program, or another PL/SQL program. For example:

CREATE OR REPLACE TRIGGER check_approval_status
  BEFORE INSERT ON claim_disbursal
  DECLARE
    status NUMERIC;
  BEGIN  -- check for approval
    get_approval_info(:new.approvedby, status);
    IF (status != 0) THEN  -- no approval on file!
      RAISE_APPLICATION_ERROR(-20100, `No approval on file!');
    END IF;
  END;  -- check for approval
END check_approval_status;

Here, the application-defined exception causes an INSERT to be rolled back. The business rule is defined by a single stored procedure, which can now be used in many places. Should the rule require modification, it can be changed within the body of the stored procedure. The dependent PL/SQL code would then only need to be recompiled.

Debugging with Show Errors

Up to now, your stored procedures have compiled flawlessly. Unfortunately, you can expect to make mistakes leading to compilation errors (I know I do). Fortunately, the information to debug them is at hand. However, the feature provided by Oracle to observe them has some limitations, given its relative simplicity, but there is a better solution.

Page 249

When your unnamed PL/SQL blocks failed to compile, the server dumped the error information straight back to SQL*Plus. With stored subroutines, the procedure is a little different. Immediately after the failed compilation, you type:

SHOW ERRORS

Then, all the errors and the line numbers on which they occurred are displayed for your perusal.

NOTE
SHOW ERRORS only shows the errors for the last stored subprogram or package submitted for compilation. If you submit two in a row, and the first has errors, SHOW ERRORS will only show errors for the second one (if any occurred). However, the error information for both is still available in USER_ERRORS.

Consider the bug-infested code shown in Listing 11.9.

Listing 11.9 showerr1.sql—Show errors Procedure, First Pass

CREATE OR REPLACE PROCEDURE showerr1(
    Pname  IN user_errors.name%TYPE,
    Ptype  IN user_errors.type%TYPE) IS
  CURSOR get_errors(Cname IN user_errors.name%TYPE,
                    Ctype IN user_errors.type%TYPE) IS
    SELECT * FROM USER_ERRORS
    WHERE name = Cname AND type = Ctype
    ORDER BY SEQUENCE;
  get_errors_rec get_errors%TYPE;
  status NUMERIC := 0;
  Lname  user_errors.name%TYPE;
  Ltype  user_errors.type%TYPE;
BEGIN
  Lname  := UPPER(Pname);
  Ltype  := UPPER(Ptype);
  DBMS_OUTPUT.put_line(`Compilation errors for ` || 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
    DBMS_OUTPUT.put_line(`At Line/Col: ` || get_errors_rec.line ||
                         `/' || get_errors_rec.position);
    DBMS_OUTPUT.put_line(get_errors_rec.text);
  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;
  END;
END showerr1;
/.

Previous | Table of Contents | Next