PL/SQL in DBMS Output

PL/SQL in DBMS output

DBMS_ OUTPUT –> PL / SQL provides in-built packages known as DBMS _ OUTPUT that helps the compiler to display the output and also helps to debug information.

S.NO	Sub program	Purpose
1	DBMS _ OUTPUT . DISABLE ;	Disables message output.
2	DBMS _ OUTPUT . ENABLE ( buffer _ size IN INTEGER DEFAULT 20000 ) ;	Enables message output. A NULL value of buffer _ size represents total buffer size.
3	DBMS _ OUTPUT . GET _ LINE ( line OUT VARCHAR2 , status OUT INTEGER ) ;	Retrieves a single line of buffered information.
4	DBMS _ OUTPUT . GET _ LINE ( lines OUT CHARARR , num lines IN OUT INTEGER ) ;	Retrieves an array of lines from the buffer.
5	DBMS _ OUTPUT . NEW _  LINE ;	Puts an end-of-line marker.
6	DBMS _ OUTPUT . PUT ( item IN VARCHAR2 ) ;	Place a partial line in the buffer.
7	DBMS _ OUTPUT . PUT _ LINE ( item IN VARCHAR2 ) ;	Place a line in the buffer.

PROGRAM TO GET DIFFERENT OUTPUT USING DIFFERENT DBMS OUTPUT

 set serveroutput on;
 DECLARE
    lines dbms_output.chararr;
    num_lines number;
 BEGIN
    dbms_output.disable;
    dbms_output.put_line('Hello Reader!');
    -- enable the buffer with default size 20000
    dbms_output.enable;
    dbms_output.put_line ( ' Hello Reader ! ' ) ;
    dbms_output.put_line( ' Hope you have enjoyed the tutorials ! ' ) ;
    dbms_output.put_line( ' Have a great time exploring pl/sql ! ' ) ;
    num_lines :=  4 ;
    dbms_output.get_lines ( lines, num_lines ) ;
    FOR i IN 1 .. num_lines LOOP
       dbms_output.put_line ( lines ( I ) ) ;
    END LOOP;
 END;

When we execute the code as mentioned above, the compiler will give the output –
anonymous block completed

Hello Reader!
 Hope you have enjoyed the tutorials!
 Have a great time exploring pl/sql! 

Here in this program, we have declared ORACLES predefined DBMS _ OUTPUT procedure lines DBMS _ output . chararr to retrieve any number of array lines from the buffer. Also, we have declared a variable numb _ lines with data type number.

Now in the main block, we have declared DBMS _ output . Disable and DBMS _ output . Put _ line. Due to the disable command, the output won’t be displayed by the compiler.

Also, we have declared DBMS _ output . Enable will automatically set the buffer size to 20000, which is the default ratio and DBMS _ output . Put _ line. Due to the enable command, all the output commands under the 20000 buffer will be displayed.

And we have assigned the value of our declared variable num _ line := 4 , and we have declared DBMS _ output . Get _ line ( lines , numb _ lines ), retrieving an array of lines from the buffer. We have used for command with counter I after the IN variable, we have set the value of lower value. Upper value to num _ lines the program will run from 1 till I become equal to the num _ line and finally DBMS to display the output.