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.
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.
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.sqlShow 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; /.