Oracle9i Supplied PL/SQL Packages and Types Reference Release 1 (9.0.1) Part Number A89852-02 |
|
DBMS_OUTPUT , 6 of 6
You can choose to retrieve from the buffer a single line or an array of lines. Call the GET_LINE
procedure to retrieve a single line of buffered information. To reduce the number of calls to the server, call the GET_LINES
procedure to retrieve an array of lines from the buffer.
You can choose to automatically display this information if you are using Enterprise Manager or SQL*Plus by using the special SET
SERVEROUTPUT
ON
command.
After calling GET_LINE
or GET_LINES
, any lines not retrieved before the next call to PUT
, PUT_LINE
, or NEW_LINE
are discarded to avoid confusing them with the next message.
DBMS_OUTPUT.GET_LINE ( line OUT VARCHAR2, status OUT INTEGER);
DBMS_OUTPUT.GET_LINES ( lines OUT CHARARR, numlines IN OUT INTEGER);
CHARARR
is a table of VARCHAR2
(255).
The DBMS_OUTPUT
package is commonly used to debug stored procedures and triggers, as shown in Example 1. This package can also be used to enable you to retrieve information about an object and format this output, as shown in Example 2.
This is an example of a function that queries the employee table and returns the total salary for a specified department. The function includes several calls to the PUT_LINE
procedure:
CREATE FUNCTION dept_salary (dnum NUMBER) RETURN NUMBER IS CURSOR emp_cursor IS SELECT sal, comm FROM emp WHERE deptno = dnum; total_wages NUMBER(11, 2) := 0; counter NUMBER(10) := 1; BEGIN FOR emp_record IN emp_cursor LOOP emp_record.comm := NVL(emp_record.comm, 0); total_wages := total_wages + emp_record.sal + emp_record.comm; DBMS_OUTPUT.PUT_LINE('Loop number = ' || counter || '; Wages = '|| TO_CHAR(total_wages)); /* Debug line */ counter := counter + 1; /* Increment debug counter */ END LOOP; /* Debug line */ DBMS_OUTPUT.PUT_LINE('Total wages = ' || TO_CHAR(total_wages)); RETURN total_wages; END dept_salary;
Assume the EMP
table contains the following rows:
EMPNO SAL COMM DEPT ----- ------- -------- ------- 1002 1500 500 20 1203 1000 30 1289 1000 10 1347 1000 250 20
Assume the user executes the following statements in the Enterprise Manager SQL Worksheet input pane:
SET SERVEROUTPUT ON VARIABLE salary NUMBER; EXECUTE :salary := dept_salary(20);
The user would then see the following information displayed in the output pane:
Loop number = 1; Wages = 2000 Loop number = 2; Wages = 3250 Total wages = 3250
PL/SQL procedure successfully executed.
In this example, the user has used the EXPLAIN
PLAN
command to retrieve information about the execution plan for a statement and has stored it in PLAN_TABLE
. The user has also assigned a statement ID to this statement. The example EXPLAIN_OUT
procedure retrieves the information from this table and formats the output in a nested manner that more closely depicts the order of steps undergone in processing the SQL statement.
/****************************************************************/ /* Create EXPLAIN_OUT procedure. User must pass STATEMENT_ID to */ /* to procedure, to uniquely identify statement. */ /****************************************************************/ CREATE OR REPLACE PROCEDURE explain_out (statement_id IN VARCHAR2) AS -- Retrieve information fromPLAN_TABLE
into cursorEXPLAIN_ROWS
. CURSOR explain_rows IS SELECT level, id, position, operation, options, object_name FROM plan_table WHERE statement_id = explain_out.statement_id CONNECT BY PRIOR id = parent_id AND statement_id = explain_out.statement_id START WITH id = 0 ORDER BY id; BEGIN
-- Loop through information retrieved fromPLAN_TABLE
: FOR line IN explain_rows LOOP -- At start of output, include heading with estimated cost. IF line.id = 0 THEN DBMS_OUTPUT.PUT_LINE ('Plan for statement ' || statement_id || ', estimated cost = ' || line.position); END IF; -- Output formatted information.LEVEL
determines indention level. DBMS_OUTPUT.PUT_LINE (lpad(' ',2*(line.level-1)) || line.operation || ' ' || line.options || ' ' || line.object_name); END LOOP; END;
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|