Listing 12.2 Continued
EXCEPTION WHEN OTHERS THEN NULL; -- do nothing END; END; /
This might return something similar to the following:
Recompile FUNCTION TABLE_EXISTS SUCCESSFUL 1 Program Objects Recompiled PL/SQL procedure successfully completed.
CAUTION |
If a program object fails to recompile successfully, alter_compile will not tell you! After running alter_compile, you should check the status of the package to make sure it compiled successfully. |
If you provide a program object name that doesn't exist, or you have the type wrong, you get:
ORA-20000: Unable to compile PACKAGE "BLICK", insufficient privileges or does not exist
You can programmatically analyze (generate statistics) for tables, indexes, and clusters. For example, you could analyze all or selected objects in your schema, as shown in Listing 12.3.
Listing 12.3 runstats.sqlRunning Statistics Programmatically
-- analyze all tables, indexes and clusters in your own schema -- computes exact statistics SET ECHO OFF ACCEPT method PROMPT ` ANALYZE Method ([COMPUTE]|ESTIMATE|DELETE): ` ACCEPT estrow PROMPT ` IF ANALYZE Method is ESTIMATE, #Rows (0-n) [100]: ` ACCEPT estpct PROMPT ` IF ANALYZE Method is ESTIMATE, %Rows (0-99) [20]: ` DECLARE -- application-defined exceptions bad_method EXCEPTION; -- user entered an invalid method bad_estrow EXCEPTION; -- user entered an invalid est. #rows bad_estpct EXCEPTION; -- user entered an invalid est. %rows -- cursors CURSOR analyze_obj IS SELECT object_name, object_type FROM user_objects WHERE object_type = ANY (`TABLE', `INDEX', `CLUSTER'); -- constants METHOD CONSTANT VARCHAR2(30) := NVL(UPPER(`&&method'), `COMPUTE'); -- variables estrow NUMBER := NVL(`&&estrow', `100'); -- user input est. #rows estpct NUMBER := NVL(`&&estpct', `20'); -- user input est. pct
Page 281
rec analyze_obj%ROWTYPE; status NUMERIC := 0; cnt NUMERIC := 0; BEGIN DBMS_OUTPUT.enable; -- validate user input IF (METHOD NOT IN (`COMPUTE', `ESTIMATE', `DELETE')) THEN RAISE bad_method; ELSIF (METHOD IN (`COMPUTE', `DELETE')) THEN -- ignore est. #/%row estrow := NULL; estpct := NULL; ELSE -- picked ESTIMATE; must provide either est. #rows or %rows IF (estrow < 1 AND estpct = 0) THEN RAISE bad_estrow; ELSIF (estpct NOT BETWEEN 1 AND 99) THEN RAISE bad_estpct; END IF; END IF; -- validate input OPEN analyze_obj; LOOP -- analyze schema objects FETCH analyze_obj INTO rec; EXIT WHEN analyze_obj%NOTFOUND; -- COMPUTE STATISTICS for this schema only DBMS_OUTPUT.put(`Analyze ` || METHOD || ` ` || rec.object_type || ` ` || rec.object_name); DBMS_DDL.analyze_object(rec.object_type, NULL, rec.object_name, `COMPUTE'); DBMS_OUTPUT.put_line(` SUCCESSFUL'); cnt := cnt + 1; END LOOP; -- analyze schema objects CLOSE analyze_obj; DBMS_OUTPUT.put_line(TO_CHAR(cnt) || ` objects analyzed'); EXCEPTION WHEN bad_method THEN DBMS_OUTPUT.put_line(`Invalid Method! Must be COMPUTE, ESTIMATE or DELETE only'); WHEN bad_estrow THEN DBMS_OUTPUT.put_line(`Invalid Est. #Rows! Must be >= 1'); WHEN bad_estpct THEN DBMS_OUTPUT.put_line(`Invalid Est. %Rows! Must be between 1 and 99'); WHEN OTHERS THEN BEGIN status := SQLCODE; DBMS_OUTPUT.put_line(` FAILED with ` || SQLERRM(status)); IF (analyze_obj%ISOPEN) THEN CLOSE analyze_obj; END IF; EXCEPTION WHEN OTHERS THEN NULL; END; END; /
Page 282
Note all the input validation we have to do. Partly, this is because analyze_object does very little of its own. If you provided a row estimate along with the COMPUTE method, for instance, you'll get:
Analyze TABLE <table> FAILED with ORA-01490: invalid ANALYZE command.
When I compute statistics by picking all the defaults, my run looks like this:
ANALYZE Method ([COMPUTE]|ESTIMATE|DELETE): IF ANALYZE Method is ESTIMATE, #Rows (1-n) [1]: IF ANALYZE Method is ESTIMATE, %Rows (1-99) [20]: old 12: METHOD CONSTANT VARCHAR2(30) := NVL(UPPER(`&&method'), `COMPUTE'); new 12: METHOD CONSTANT VARCHAR2(30) := NVL(UPPER(`'), `COMPUTE'); old 14: estrow NUMBER := NVL(`&&estrow', `1'); -- user input est. #rows new 14: estrow NUMBER := NVL(`', `1'); -- user input est. #rows old 15: estpct NUMBER := NVL(TRUNC(`&&estpct'), `20'); -- user input est. pct new 15: estpct NUMBER := NVL(TRUNC(`'), `20'); -- user input est. pct Analyze COMPUTE TABLE BONUS SUCCESSFUL Analyze COMPUTE TABLE DEPT SUCCESSFUL Analyze COMPUTE TABLE EMP SUCCESSFUL Analyze COMPUTE INDEX PK_DEPT SUCCESSFUL Analyze COMPUTE INDEX PK_EMP SUCCESSFUL Analyze COMPUTE TABLE SALGRADE SUCCESSFUL 6 objects analyzed PL/SQL procedure successfully completed.
If you enter in an invalid method, it complains with:
Invalid Method! Must be COMPUTE, ESTIMATE or DELETE only
as we desired. Play around with it and see how you like it.
This implementation of the ANALYZE command doesn't do VALID STRUCTURE or LIST CHAINED ROWS. In this respect, it's an incomplete implementation, but it's still pretty useful.
If you've been following along since the tutorial, you're already familiar with the put_line procedure in this package. Here are some details regarding the implementation of the package:
You use the procedure enable to turn on the output feature. put_line calls are ignored if output hasn't first been enabled. When you enable output, you can also specify a buffer size, as with:
DBMS_OUTPUT.enable(1000000); -- 1 million bytes is the max
Page 283
Conversely, you can turn output back off with disable:
DBMS_OUTPUT.disable; -- turn off output
You can store a line of text with put_line. It's overloaded to take a DATE, NUMBER or VARCHAR2. You can also store a line of text without terminating it by using the put procedure (which is overloaded in like manner). This is useful if the line you're building requires some logic:
-- excerpt taken from Package rev_eng IF (Ltable IS NULL) THEN -- parameter comments DBMS_OUTPUT.put(`-- ALL TABLES'); ELSE DBMS_OUTPUT.put(`-- TABLE ` || Ltable); END IF; DBMS_OUTPUT.put_line(` FOR OWNER ` || Lowner || `, TABLESPACE ` || Ltspace);
You can terminate text submitted with put by using the new_line procedure. This signals the end of the current line of text with a marker, and also stores the length of the text line along with the text (this is completely transparent to the user). Note that if you try to double or triple space output lines by sending new lines without first using put, it doesn't work:
BEGIN DBMS_OUTPUT.put(`Hey, `); DBMS_OUTPUT.put(`Dan!'); DBMS_OUTPUT.new_line; DBMS_OUTPUT.new_line; DBMS_OUTPUT.put_line(`Time to go Hot Tubbing!'); end;
Gives you:
Hey, Dan! Time to go Hot Tubbing! PL/SQL procedure successfully completed.
It didn't double space. Oh well.
You'll get an exception if you attempt to output a string longer than the acceptable 255 bytes before ending it with new_line:
ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line.
Why did the folks at Oracle limit the string length to 255? Look back to the example where a PL/SQL table of VARCHAR2 (32767) quickly exhausted available memory (see Chapter 10, "PL/SQL Fundamentals," Listing 4.10). So they picked what they considered a reasonable limit.
Strings are returned with get_line. This is what SQL*Plus does to return the strings written with put_line. Unseen, it calls get_line until no more lines are available. You really only need concern yourself with get_line (and its multiple line version, get_lines) if you're writing a 3GL program to receive lines stored with put_line. You can use it in a PL/SQL program if you wish, such as to buffer lines and then access them in FIFO manner, perhaps to insert them into a table.