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 ... |
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.