057 - [Oracle PL/SQL] Oracle package - DBMS_OUTPUT

<샘플코드에서 사용한 데이터는 HR 스키마이고, 오라클 설치시 생성할 수 있는 기본 스키마 입니다>

 

 

 

 

 

 

•PUT appends text from the procedure to the current line of the line output buffer.

•NEW_LINE places an end-of-line marker in the output buffer.

•PUT_LINE combines the action of PUT and NEW_LINE (to trim leading spaces).

•GET_LINE retrieves the current line from the buffer into a procedure variable.

•GET_LINES retrieves an array of lines into a procedure-array variable.

•ENABLE/DISABLE enables and disables calls to DBMS_OUTPUT procedures.

 

The buffer size

The minimum is 2,000 and the maximum is unlimited. The default is 20,000.

An integer parameter between 2,000 and 1,000,000 in the ENABLE procedure

 

Actually the DBMS_OUTPUT is not that important for developers

Because they deal with messages using the development applications features

Example: alert windows, console windows

 

 

 

• 버퍼사이즈 오류

# 버퍼사이즈 오류, 버퍼사이즈가 20000으로 제한적이라서 오류가 발생한다.

begin
    for i in 1..30000
    loop
        dbms_output.put_line(lpad(i,7,0));
    end loop;
end;
---------------------------------------------------------
Error report -
ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 97
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at line 4
20000. 00000 -  "%s"
*Cause:    The stored procedure 'raise_application_error'
           was called which causes this error to be generated.
*Action:   Correct the problem as described in the error message or contact
           the application administrator or DBA for more information.




# 버퍼사이즈 제한은 아래처럼 변경 가능하다

begin
    for i in 1..30000
    loop
        dbms_output.enable(1000000); --max 1,000,000
        dbms_output.put_line(lpad(i,7,0));
    end loop;
end;

 

 

 

• dbms_output.enable/disable

# messages not sent untill the PLSQL completed
# test1은 버퍼에 넣었으나, disable 되면서 버퍼내용이 삭제되고, 
# test2는 disable된 상태이므로, 버퍼에 들어가지 못한다.
# test3은 버퍼가 enable 되어서 출력가능함.
begin
    dbms_output.put_line('test');
    dbms_output.disable;
    dbms_output.put_line('test2');
    dbms_output.enable;
    dbms_output.put_line('test3');
end;
---------------------------------------------------------
test3

 

 

 

• put/new_line 예시

begin
    dbms_output.put_line('welcome');
    dbms_output.put('my ');
    dbms_output.put('name ');
    dbms_output.put('is ');
    dbms_output.put('Hong');
    dbms_output.new_line; --이부분이 없으면 put에서 출력자체가 되지 않는다.
    dbms_output.put_line('my name is Hong');
end;
---------------------------------------------------------
welcome
my name is Hong
my name is Hong

 

 

 

• get_line 예시

# get_line을 한번만 호출해서 첫번째출력인 'line 1' 만 출력이된다.
# get_line 
# we need 2 variables
DECLARE
    buffer VARCHAR2(100);
    status INTEGER;
begin
    dbms_output.put_line('line 1');
    dbms_output.put_line('line 2');
    dbms_output.put_line('line 3');
    --This procedure retrieves a single line of buffered information.
    dbms_output.get_line(buffer, status); --both are out parameters
    dbms_output.put_line('Buffer: ' || buffer);
    dbms_output.put_line('Status: ' || status);
/*If the call completes successfully, 
then the status returns as 0. If there are no more 
lines in the buffer, then the status is 1.
*/
end;
---------------------------------------------------------
Buffer: line 1
Status: 0

 

 

 

• get_line 예시

# 멀티라인으로 출력된 경우, 버퍼에서 모두 가져오고 싶다면 아래처럼 get_line을 여러번 호출하면 된다.

DECLARE
    buffer VARCHAR2(100);
    status INTEGER;
    v VARCHAR2(3000);
begin
    dbms_output.put_line('line 1');
    dbms_output.put_line('line 2');
    dbms_output.put_line('line 3');
    for i in 1..5
    loop
        dbms_output.get_line(buffer, status);
        v:=v||buffer||','||status||chr(10);
    end loop;
    dbms_output.put_line(v);
end;
---------------------------------------------------------
line 1,0
line 2,0
line 3,0
,1
,1



# 버퍼에 몇개의 라인이 있는지 모를때는 status 값을 확인하면 된다.

DECLARE
    buffer VARCHAR2(100);
    status INTEGER;
    v VARCHAR2(3000);
begin
    dbms_output.put_line('line 1');
    dbms_output.put_line('line 2');
    dbms_output.put_line('line 3');

    loop
        dbms_output.get_line(buffer, status);
        exit when status=1;
        v:=v||buffer||','||status||chr(10);
    end loop;
    dbms_output.put_line(v);
end;
---------------------------------------------------------
line 1,0
line 2,0
line 3,0

 

 

 

• get_lines 예시

DECLARE
    buffer DBMS_OUTPUT.CHARARR;
-- type chararr is table of varchar2(32767) index by binary_integer
    v_line INTEGER;
begin

    v_line:=10;
    dbms_output.put_line('line 1');
    dbms_output.put_line('line 2');
    dbms_output.put_line('line 3');
    dbms_output.put_line('line 4');
    
    dbms_output.get_lines(buffer, v_line); --the first par is out, the second is in out
    
    dbms_output.put_line( buffer(3)|| ', v_line:' || v_line);
    dbms_output.put_line( buffer(2)|| ', v_line:' || v_line);
    dbms_output.put_line( buffer(1)|| ', v_line:' || v_line);
    dbms_output.put_line( buffer(4)|| ', v_line:' || v_line);
    dbms_output.put_line( 'v_line:' || v_line);

end;
---------------------------------------------------------
line 3, v_line:4
line 2, v_line:4
line 1, v_line:4
line 4, v_line:4
v_line:4



DECLARE
    buffer DBMS_OUTPUT.CHARARR;
-- type chararr is table of varchar2(32767) index by binary_integer
    v_line INTEGER;
begin

    v_line:=10;
    dbms_output.put_line('line 1');
    dbms_output.put_line('line 2');
    dbms_output.put_line('line 3');
    dbms_output.put_line('line 4');
    
    dbms_output.get_lines(buffer, v_line); --the first par is out, the second is in out
    
    for i in 1..v_line
    loop
        dbms_output.put_line( buffer(i)|| ', v_line:' || v_line);
    end loop;
    dbms_output.put_line( 'v_line:' || v_line);

end;
---------------------------------------------------------
line 1, v_line:4
line 2, v_line:4
line 3, v_line:4
line 4, v_line:4
v_line:4