Sayantan's Blog On Oracle

Sayantan's blogs on Oracle

Learn Oracle with Sayantan

DBMS_OUTPUT Package In Oracle

Leave a Comment / PLSQL, ORACLE SUPPLIED PACKAGES, PACKAGES / By

DBMS_OUTPUT Package In Oracle

INTRODUCTION:

  • DBMS_OUTPUT package helps us to send messages from stored procedures, functions, packages, triggers or any plsql block.
  • This package is mainly used for debugging purposes.

PROCEDURES AVAILABLE IN DBMS_OUTPUT PACKAGE:

DISABLE:
  • This procedure disables the calls to PUT, PUT_LINE, NEW_LINE, GET_LINE, GET_LINES procedure.
  • It purges the buffer of any remaining information.
--DBMS_OUTPUT Package In Oracle: Example

SET SERVEROUTPUT ON;
BEGIN
DBMS_OUTPUT.DISABLE;
DBMS_OUTPUT.PUT_LINE('123');
END;
/
PL/SQL procedure successfully completed.
DISABLE Output:
ENABLE:
  • This procedure enables the calls to PUT, PUT_LINE, NEW_LINE, GET_LINE, GET_LINES procedure.
  • It is not necessary to call this procedure when you use SERVEROUTPUT option of sqlplus.
--DBMS_OUTPUT Package In Oracle: Example

SET SERVEROUTPUT ON;
BEGIN
DBMS_OUTPUT.DISABLE;
DBMS_OUTPUT.PUT_LINE('123');
DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.PUT_LINE('456');
END;
/
ENABLE Output:
PL/SQL procedure successfully completed.

456
--DBMS_OUTPUT Package In Oracle: Example

SET SERVEROUTPUT ON;
BEGIN
DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.PUT_LINE('456');
END;
/
Output:
--DBMS_OUTPUT Package In Oracle: Example output

PL/SQL procedure successfully completed.

456
--DBMS_OUTPUT Package In Oracle: Example

SET SERVEROUTPUT ON;
BEGIN
  DBMS_OUTPUT.PUT_LINE('456');
END;
/
Output:
PL/SQL procedure successfully completed.

456
GET_LINE:

Call this procedure to return a single line of buffered information.

SYNTAX:
DBMS_OUTPUT.GET_LINE(LINE OUT VARCHAR2,
STATUS OUT INTEGER);
LINE:

Returns a single line buffered information.

Should declare the parameter as varchar2(32767) to avoid the ORA-06502 error – > PLSQL: Numeric or Value error: Character string buffer too small.

STATUS:

If the call is completed successfully then the status returns as 0.

If there are no more lines in the buffer, then the status return as 1.

--DBMS_OUTPUT Package In Oracle: Example

DECLARE
  V_INT NUMBER := 0;
  V_CHAR VARCHAR2(32767);
BEGIN
  SELECT 'EMPLOYEE_ID = '||EMPLOYEE_ID||' NAME = '||FIRST_NAME||' '||LAST_NAME||' SALARY = '||SALARY
  INTO V_CHAR
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30
  FETCH FIRST ROWS ONLY;
  --
  DBMS_OUTPUT.PUT_LINE(V_CHAR);
  DBMS_OUTPUT.GET_LINE(V_CHAR, V_INT);
  --
  DBMS_OUTPUT.PUT_LINE('V_CHAR = '||V_CHAR);
  DBMS_OUTPUT.PUT_LINE('V_INT = '||V_INT);
END;
/
Output:
--DBMS_OUTPUT Package In Oracle: Example output

PL/SQL procedure successfully completed.

V_CHAR = EMPLOYEE_ID = 114 NAME = Den Raphaely SALARY = 1210
V_INT = 0
GET_LINES:

This procedure retrieves an array of lines from buffer.

DATA STRUCTURES:

DBMS_OUTPUT package declares two table type array to use with GET_LINES procedure. They are capturing the text submitted through PUT and PUT_LINE procedures.

CHARARR Table Type

DBMSOUTPUT_LINESARRAY Table Type

TYPE CHARARR IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;

TYPE DBMSOUTPUT_LINESARRAY IS VARRAY(2147483647) OF VARCHAR2(32767);
SYNTAX:
DBMS_OUTPUT.GET_LINES(LINES OUT CHARARR,
NUMLINES IN OUT INTEGER);

DBMS_OUTPUT.GET_LINES(LINES OUT DBMSOUTPUT_LINESARRAY,
NUMLINES IN OUT INTEGER);
--DBMS_OUTPUT Package In Oracle: Example

SET SERVEROUTPUT ON;
DECLARE
  V_EMP_TAB DBMSOUTPUT_LINESARRAY;
  V_INT NUMBER := 15;
  V_CHAR VARCHAR2(32767);
  V_INTEGER NUMBER := 0;
BEGIN
  SELECT 'EMPLOYEE_ID = '||EMPLOYEE_ID||' NAME = '||FIRST_NAME||' '||LAST_NAME||' SALARY = '||SALARY
  BULK COLLECT INTO V_EMP_TAB
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  FOR I IN 1..V_EMP_TAB.COUNT
  LOOP
    DBMS_OUTPUT.PUT_LINE(V_EMP_TAB(I));
  END LOOP;
  DBMS_OUTPUT.GET_LINE(V_CHAR, V_INT);
  WHILE (V_CHAR IS NOT NULL)
  LOOP
    V_INTEGER := V_INTEGER + 1;
    V_EMP_TAB(V_INTEGER) := V_CHAR;
    DBMS_OUTPUT.GET_LINE(V_CHAR, V_INT);
  END LOOP;
  --
  FOR I IN 1..V_EMP_TAB.COUNT
  LOOP
    DBMS_OUTPUT.PUT_LINE('V_CHAR = '||V_EMP_TAB(I));
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('V_INT = '||V_INT);
END;
/
Output:
--DBMS_OUTPUT Package In Oracle: Example output

PL/SQL procedure successfully completed.

V_CHAR = EMPLOYEE_ID = 114 NAME = Den Raphaely SALARY = 1210
V_CHAR = EMPLOYEE_ID = 115 NAME = Alexander Khoo SALARY = 341
V_CHAR = EMPLOYEE_ID = 116 NAME = Shelli Baida SALARY = 319
V_CHAR = EMPLOYEE_ID = 117 NAME = Sigal Tobias SALARY = 308
V_CHAR = EMPLOYEE_ID = 118 NAME = Guy Himuro SALARY = 286
V_CHAR = EMPLOYEE_ID = 119 NAME = Karen Colmenares SALARY = 275
V_INT = 1
NEW_LINE:

This procedure puts an end-of-line marker.

--DBMS_OUTPUT Package In Oracle: Example

BEGIN
  DBMS_OUTPUT.PUT_LINE('First Line');
  DBMS_OUTPUT.PUT_LINE('Second Line');
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT_LINE('First Line');
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT_LINE('Second Line');
END;
/
--DBMS_OUTPUT Package In Oracle: Example output

PL/SQL procedure successfully completed.

First Line
Second Line

First Line


Second Line
PUT:

This procedure places a partial lines in the buffer.

SYNTAX:
DBMS_OUTPUT.PUT(ITEM IN VARCHAR2);
ITEM: Item to buffer
--DBMS_OUTPUT Package In Oracle: Example

BEGIN
  DBMS_OUTPUT.PUT('First Line');
  DBMS_OUTPUT.PUT_LINE('Second Line');
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT('First Line');
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT_LINE('Second Line');
END;
/
Output:
--DBMS_OUTPUT Package In Oracle: Example output

PL/SQL procedure successfully completed.

First LineSecond Line

First Line
Second Line
PUT_LINE:

This procedure places a complete line in the buffer.

SYNTAX:
DBMS_OUTPUT.PUT_LINE(ITEM IN VARCHAR2);

USAGES OF DBMS_OUTPUT PACKAGE:

  • When you build an information part by part and place them into buffer, call PUT
  • It is used when you place an entire line into the buffer then call PUT_LINE
  • When you call PUT_LINE then an end-of-line marker is automatically followed by PUT_LINE.
  • When you call PUT then for end-of-line marker, you explicitly call NEW_LINE.
  • If your line exceed the line limit then you will receive an error.
  • The output that you create using PUT or PUT_LINE is buffered and it cannot be retrieved. This is occurred if the plsql program unit for which its buffered returns to the caller.
--DBMS_OUTPUT Package In Oracle: Example

BEGIN
  DBMS_OUTPUT.PUT('First Line');
  DBMS_OUTPUT.PUT_LINE('Second Line');
  DBMS_LOCK.SLEEP (10);
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT('First Line');
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT_LINE('Second Line');
  DBMS_LOCK.SLEEP (10);
END;
/
Output:
DBMS_OUTPUT Package In Oracle: Output

Its taking 20 sec to print the output. Until the plsql block is completed it will not print the output.

Author

Leave a Comment

Your email address will not be published.