Previous | Table of Contents | Next

Page 280

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

Formatting Output with DBMS_OUTPUT

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.

Previous | Table of Contents | Next