Expert

23-20.

Write a procedure called println that allows a developer to display with DBMS_OUTPUT.PUT_LINE strings containing up to 32,767 bytes (32K, the maximum size of a VARCHAR2 variable in PL/SQL).

23-21.

Enhance the procedure in 23-20 to automatically expand the DBMS_OUTPUT buffer size to the maximum if a call to DBMS_OUTPUT.PUT_LINE causes the current buffer size to be exceeded.

23-22.

Create a package based on the println procedure so that you can instead call print.ln and pass a particular type of value (string, date, number, or Boolean) and see the results. Here is the specification for that package:

CREATE OR REPLACE PACKAGE print
IS
   PROCEDURE ln (val IN VARCHAR2);
   PROCEDURE ln (val IN DATE);
   PROCEDURE ln (val IN NUMBER);
   PROCEDURE ln (val IN BOOLEAN);
END print;
/

23-23.

Why does the PRAGMA RESTRICT_REFERENCES for DBMS_OUTPUT.PUT_LINE assert only WNDS and RNDS?

23-24.

As of Oracle 7.3.4 and Oracle 8.0.4, you can now call DBMS_OUTPUT and DBMS_PIPE from within functions, which are then called from within SQL. This allows you to build a trace feature into your SQL. The following function, for example, uses DBMS_OUTPUT.PUT_LINE to display the ROWID of each row touched by a query:

 /* Filename on web page: traceit.sf */ CREATE OR REPLACE FUNCTION traceit ( tab IN VARCHAR2, rowid_in IN ROWID) RETURN INTEGER IS BEGIN DBMS_OUTPUT.PUT_LINE ( tab || '-' || ROWIDTOCHAR (rowid_in)); RETURN ...

Get Oracle PL/SQL Programming: A Developer's Workbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.