Oracle PL/SQL Programming: A Developer's Workbook

Expert

Q:
17-16.	You can assert only WNDS. Clearly, it reads from the database. In addition, DBMS_OUTPUT.PUT_LINE asserts only WNDS and RNDS; it does, in fact, write to the DBMS_OUTPUT buffer and also reads the values of data structures for the buffer. You can assert only the intersection of the purity levels of all referenced programs.
Q:
17-17.	The following implementation of this trace function is really just a “pass-through” to DBMS_OUTPUT.PUT_LINE: /* 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 0; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.ENABLE (1000000); traceit (tab, rowid_in); END; / I cannot call this procedure directly in SQL, so I put it inside a function that always returns 0, but in the process put information into the DBMS_OUTPUT buffer. I also added an exception section to trap and automatically correct for an error that might arise from the DBMS_OUTPUT buffer’s being too small.
Q:
17-18.	Here is one possible implementation: CREATE OR REPLACE FUNCTION traceit ( tab IN VARCHAR2, rowid_in IN ROWID) RETURN INTEGER IS stat PLS_INTEGER; BEGIN DBMS_PIPE.RESET_BUFFER; DBMS_PIPE.PACK_MESSAGE (tab); DBMS_PIPE.PACK_MESSAGE_ROWID (rowid_in); stat := DBMS_PIPE.SEND_MESSAGE ('SQLtrace'); RETURN stat; END; / You might decide to use a pipe-based ...