문서유형ㅣ기술정보
분야ㅣ관리/환경설정
적용제품버전ㅣTibero 7.2.4
문서번호ㅣTADTI149
개요
많은 컬럼을 가진 단일 테이블을 조회할 경우, 각 컬럼의 description을 일일이 조회한 후 SELECT 문을 재작성해야 하는 번거로움이 발생합니다.
이러한 불편을 줄이기 위해 별도의 프로시저를 작성하여 가독성 있게 조회할 수 있습니다.
방법
코드
CREATE OR REPLACE PROCEDURE SYS.SP_PRINT_DATA(
IN_QUERY IN VARCHAR2 /* Query */
)
AUTHID CURRENT_USER
IS
/***********************************************************************************
프로그램명 : SP_PRINT_DATA
수행목적 : 행이 긴 데이터를 세로로 정렬하여 가독성있게 확인
수행방법
SQL> set serveroutput on ;
SQL> exec sp_print_data('select * from v$session') ;
SQL> exec sp_print_table('select * from v$database where STATUS=''RUNNING''') ; -- 조건절 있는 경우
***********************************************************************************/
V_CURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
V_COL_VAL VARCHAR2(4000);
V_STATUS INTEGER;
V_DESC_TB DBMS_SQL.DESC_TAB ;
V_COL_CNT NUMBER;
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS''' ;
DBMS_SQL.PARSE(V_CURSOR, IN_QUERY, DBMS_SQL.NATIVE) ;
DBMS_SQL.DESCRIBE_COLUMNS(V_CURSOR, V_COL_CNT, V_DESC_TB ) ;
FOR I IN 1 .. V_COL_CNT LOOP
DBMS_SQL.DEFINE_COLUMN(V_CURSOR, I, V_COL_VAL, 4000) ;
END LOOP ;
V_STATUS := DBMS_SQL.EXECUTE(V_CURSOR);
DBMS_OUTPUT.PUT_LINE( '-------------------------------------------------------------------------' );
WHILE ( DBMS_SQL.FETCH_ROWS(V_CURSOR) > 0 ) LOOP
FOR I IN 1 .. V_COL_CNT LOOP
DBMS_SQL.COLUMN_VALUE( V_CURSOR, I, V_COL_VAL );
--DBMS_OUTPUT.PUT_LINE( RPAD( V_DESC_TB(I).COL_NAME, 30 ) || ' : ' || V_COL_VAL );
DBMS_OUTPUT.PUT_LINE( RPAD( V_DESC_TB(I).COL_NAME, 30 ) || ' : ' || REPLACE(REPLACE(V_COL_VAL, CHR(10), ''), CHR(13), '') );
END LOOP;
DBMS_OUTPUT.PUT_LINE( '-------------------------------------------------------------------------' );
END LOOP;
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS''';
EXCEPTION
WHEN OTHERS THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS''';
RAISE;
END;
/
CREATE PUBLIC SYNONYM SP_PRINT_DATA FOR SYS.SP_PRINT_DATA;
GRANT EXECUTE ON SYS.SP_PRINT_DATA TO PUBLIC;
기존 조회 방식
많은 컬럼을 가지고 있는 dba_tables 오브젝트를 조회하면 다음과 같습니다.
SQL> select * from dba_tables where rownum < 2;
OWNER
--------------------------------------------------------------------------------------------------------------------------------
TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME PCT_FREE
-------------------------------------------------------------------------------------------------------------------------------- ----------
INI_TRANS LOGGING NUM_ROWS BLOCKS AVG_ROW_LEN DEGREE SAMPLE_SIZE
---------- ------- ---------- ---------- ----------- ---------- -----------
LAST_ANALYZED PARTITIONED
-------------------------------------------------------------------------------------------------------------------------------- -----------
BUFFER_POOL ROW_MOVEMENT DURATION COMPRESSION COMPRESS_FOR DROPPED READ_ONLY TEMPORARY MAX_EXTENTS IOT_TYPE INITIAL_EXTENT NEXT_EXTENT
----------- ------------ ----------- ----------- ------------ ------- --------- --------- ----------- ------------ -------------- -----------
MIN_EXTENTS IS_VIRTUAL INMEMORY INMEMORY_PRIORITY INMEMORY_DISTRIBUTE INMEMORY_COMPRESSION INMEMORY_DUPLICATE
----------- ---------- -------- ----------------- ------------------- -------------------- ------------------
SYS
ALL_DML_INDEX_STAT$
SYSTEM 10
2 YES 566 16 22.1166078 0 566
2025/12/09 NO
DEFAULT DISABLED NO NO NO NO 4294967295 131072 131072
1 N DISABLED
1 row selected.
가독성 프로시저를 활용한 조회
SQL> set serveroutput on ;
SQL> exec sp_print_data('select * from dba_tables where rownum < 2') ;
-------------------------------------------------------------------------
OWNER : SYS
TABLE_NAME : ALL_DML_INDEX_STAT$
TABLESPACE_NAME : SYSTEM
PCT_FREE : 10
INI_TRANS : 2
LOGGING : YES
NUM_ROWS : 566
BLOCKS : 16
AVG_ROW_LEN : 22.1166077738515901060070671378091872792
DEGREE : 0
SAMPLE_SIZE : 566
LAST_ANALYZED : 2025-12-09 12:54:30
PARTITIONED : NO
BUFFER_POOL : DEFAULT
ROW_MOVEMENT : DISABLED
DURATION :
COMPRESSION : NO
COMPRESS_FOR :
DROPPED : NO
READ_ONLY : NO
TEMPORARY : NO
MAX_EXTENTS : 4294967295
IOT_TYPE :
INITIAL_EXTENT : 131072
NEXT_EXTENT : 131072
MIN_EXTENTS : 1
IS_VIRTUAL : N
INMEMORY : DISABLED
INMEMORY_PRIORITY :
INMEMORY_DISTRIBUTE :
INMEMORY_COMPRESSION :
INMEMORY_DUPLICATE :
-------------------------------------------------------------------------
PSM completed.
다중행 조회
1개의 row가 아닌 여러 row도 조회할 수 있습니다.
SQL> set serveroutput on ;
SQL> exec sp_print_data('select * from dba_tables where rownum < 3') ;
-------------------------------------------------------------------------
OWNER : SYS
TABLE_NAME : ALL_DML_INDEX_STAT$
TABLESPACE_NAME : SYSTEM
PCT_FREE : 10
INI_TRANS : 2
LOGGING : YES
NUM_ROWS : 566
BLOCKS : 16
AVG_ROW_LEN : 22.1166077738515901060070671378091872792
DEGREE : 0
SAMPLE_SIZE : 566
LAST_ANALYZED : 2025-12-09 12:54:30
PARTITIONED : NO
BUFFER_POOL : DEFAULT
ROW_MOVEMENT : DISABLED
DURATION :
COMPRESSION : NO
COMPRESS_FOR :
DROPPED : NO
READ_ONLY : NO
TEMPORARY : NO
MAX_EXTENTS : 4294967295
IOT_TYPE :
INITIAL_EXTENT : 131072
NEXT_EXTENT : 131072
MIN_EXTENTS : 1
IS_VIRTUAL : N
INMEMORY : DISABLED
INMEMORY_PRIORITY :
INMEMORY_DISTRIBUTE :
INMEMORY_COMPRESSION :
INMEMORY_DUPLICATE :
-------------------------------------------------------------------------
OWNER : SYS
TABLE_NAME : ALL_DML_INDEX_STAT_HISTORY
TABLESPACE_NAME : SYSTEM
PCT_FREE : 10
INI_TRANS : 2
LOGGING : YES
NUM_ROWS : 0
BLOCKS : 0
AVG_ROW_LEN : 0
DEGREE : 0
SAMPLE_SIZE :
LAST_ANALYZED : 2025-12-09 12:54:30
PARTITIONED : NO
BUFFER_POOL : DEFAULT
ROW_MOVEMENT : DISABLED
DURATION :
COMPRESSION : NO
COMPRESS_FOR :
DROPPED : NO
READ_ONLY : NO
TEMPORARY : NO
MAX_EXTENTS : 4294967295
IOT_TYPE :
INITIAL_EXTENT : 131072
NEXT_EXTENT : 131072
MIN_EXTENTS : 1
IS_VIRTUAL : N
INMEMORY : DISABLED
INMEMORY_PRIORITY :
INMEMORY_DISTRIBUTE :
INMEMORY_COMPRESSION :
INMEMORY_DUPLICATE :
-------------------------------------------------------------------------
PSM completed.