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.