개별 열 형식 없이 sqlplus 열 이름 자르기 방지
기본적으로 sqlplus는 열 이름을 기본 데이터 유형의 길이로 잘라냅니다.데이터베이스에 있는 열 이름의 대부분은 테이블 이름 앞에 붙어 있으므로 잘렸을 때는 동일하게 보입니다.
폐쇄된 프로덕션 환경에서 원격 DBA에게 select * 쿼리를 지정하고 스풀된 결과를 드래그백하여 진단해야 합니다.열이 너무 많아서 개별 열 형식을 지정할 수 없습니다.sqlplus는 열 이름 자르기를 균일하게 제거할 수 있는 옵션을 제공합니까?
(SET Markup HTML ON을 사용하고 있지만, 약어되지 않은 출력이 나오는 한 다른 모달리티, csv 등을 사용할 수 있습니다.)
제안된 해결책들은 원래의 열 이름을 표시하는 데는 효과가 없는데 왜 사람들이 투표를 하는지...원래 요구에 맞는 '핵'이 있긴 한데, 전 그게 정말 마음에 안 들어요...실제로 각 열에 대한 쿼리에 문자열을 추가하거나 접두사를 붙여 항상 열 제목에 사용할 수 있을 정도로 깁니다.만약 당신이 HTML 모드에 있다면, 포스터가 그렇듯이, 약간의 흰색 간격에 의한 피해는 거의 없습니다.물론 질의가 조금 느려질 겁니다...
예.
SET ECHO OFF
SET PAGESIZE 32766
SET LINESIZE 32766
SET NUMW 20
SET VERIFY OFF
SET TERM OFF
SET UNDERLINE OFF
SET MARKUP HTML ON
SET PREFORMAT ON
SET WORD_WRAP ON
SET WRAP ON
SET ENTMAP ON
spool '/tmp/Example.html'
select
(s.ID||' ') AS ID,
(s.ORDER_ID||' ') AS ORDER_ID,
(s.ORDER_NUMBER||' ') AS ORDER_NUMBER,
(s.CONTRACT_ID||' ') AS CONTRACT_ID,
(s.CONTRACT_NUMBER||' ') AS CONTRACT_NUMBER,
(s.CONTRACT_START_DATE||' ') AS CONTRACT_START_DATE,
(s.CONTRACT_END_DATE||' ') AS CONTRACT_END_DATE,
(s.CURRENCY_ISO_CODE||' ') AS CURRENCY_ISO_CODE,
from Example s
order by s.order_number, s.contract_number;
spool off;
물론 더 나은 작업을 수행하기 위해 저장 프로시저를 작성할 수도 있지만, 이 단순한 시나리오에 비해 실제로는 너무 많은 것 같습니다.
이것은 여전히 원래 포스터 요청에도 부합하지 않습니다.열에 수동으로 나열해야 하고 사용하지 않아야 한다는 점에서 *를 선택합니다. 하지만 적어도 필드를 자세히 설명할 때는 사용할 수 있는 솔루션입니다.
그러나 HTML에 너무 긴 필드가 있는 것은 문제가 없기 때문에 크리스의 솔루션을 수정하여 이 예를 적용할 수 있는 다소 간단한 방법이 있습니다.이는 오라클이 허용하는 최대 값의 용도를 선택하는 것에 불과합니다.안타깝게도 모든 데이터 유형에 대한 서식을 명시적으로 추가하지 않는 한 모든 테이블의 모든 필드에서 이 작업이 제대로 수행되지는 않습니다.테이블마다 동일한 열 이름을 사용할 수 있지만 다른 데이터 유형을 사용할 수 있기 때문에 조인에 대해서도 이 솔루션이 작동하지 않습니다.
SET ECHO OFF
SET TERMOUT OFF
SET FEEDBACK OFF
SET PAGESIZE 32766
SET LINESIZE 32766
SET MARKUP HTML OFF
SET HEADING OFF
spool /tmp/columns_EXAMPLE.sql
select 'column ' || column_name || ' format A32766'
from all_tab_cols
where data_type = 'VARCHAR2' and table_name = 'EXAMPLE'
/
spool off
SET HEADING ON
SET NUMW 40
SET VERIFY OFF
SET TERM OFF
SET UNDERLINE OFF
SET MARKUP HTML ON
SET PREFORMAT ON
SET WORD_WRAP ON
SET WRAP ON
SET ENTMAP ON
@/tmp/columns_EXAMPLE.sql
spool '/tmp/Example.html'
select *
from Example s
order by s.order_number, s.contract_number;
spool off;
한 가지 시도해 볼 수 있는 것은 "열 x 형식 a20" 명령을 동적으로 생성하는 것입니다.다음과 같은 것.
set termout off
set feedback off
spool t1.sql
select 'column ' || column_name || ' format a' || data_length
from all_tab_cols
where table_name='YOUR_TABLE'
/
spool off
@t1.sql
set pagesize 24
set heading on
spool result.txt
select *
from YOUR_TABLE;
and rownum < 30;
spool off
이 샘플은 VARCHAR2에서만 작동합니다.DATE 또는 NUMBER에 대해 생성된 "column" 명령을 변경하려면 예를 들어 디코딩을 추가해야 합니다.
업데이트: 원래 SQL은 SQL*Plus의 동작을 실제로 변경하지 않는 것으로 나타났습니다.필드 이름을 하나의 문자 값 A, B, C 등으로 이름을 바꾸는 방법밖에 생각할 수 없었습니다.다음과 같은 방법으로
select 'column ' || column_name ||
' heading "' ||
chr(ascii('A') - 1 + column_id) ||
'"'
from all_tab_cols
where table_name='YOUR_TAB_NAME'
다음과 유사한 출력을 생성합니다.
column DEPT_NO heading "A"
column NAME heading "B"
column SUPERVIS_ID heading "C"
column ADD_DATE heading "D"
column REPORT_TYPE heading "E"
이것은 어느 정도 합리적인 포맷을 제공할 것입니다.물론 LONG, RAW 및 LOB 열의 최대 너비와 수행할 작업에 대해 사용자 자신의 기본 설정을 자유롭게 대체할 수 있습니다.
SELECT 'COLUMN ' || column_name || ' FORMAT ' ||
CASE
WHEN data_type = 'DATE' THEN
'A9'
WHEN data_type LIKE '%CHAR%' THEN
'A' ||
TRIM(TO_CHAR(LEAST(GREATEST(LENGTH(column_name),
data_length), 40))) ||
CASE
WHEN data_length > 40 THEN
' TRUNC'
ELSE
NULL
END
WHEN data_type = 'NUMBER' THEN
LPAD('0', GREATEST(LENGTH(column_name),
NVL(data_precision, data_length)), '9') ||
DECODE(data_scale, 0, NULL, NULL, NULL, '.' ||
LPAD('0', data_scale, '0'))
WHEN data_type IN ('RAW', 'LONG') THEN
'A1 NOPRINT'
WHEN data_type LIKE '%LOB' THEN
'A1 NOPRINT'
ELSE
'A' || TRIM(TO_CHAR(GREATEST(LENGTH(column_name), data_length)))
END AS format_cols
FROM dba_tab_columns
WHERE owner = 'SYS'
AND table_name = 'DBA_TAB_COLUMNS';
것 sqlplus 는합니다.Perl이나 Python과 같은 일종의 스크립트 언어를 사용하여 포맷을 자동화할 수도 있습니다.해인.ALL_TAB_COLS스키마 및 테이블에 대한 보기를 수행한 다음 형식 열 속성을 사용하여 스크립트를 동적으로 만듭니다.물론 ALL_TAB_COLS 보기(또는 이와 동등한 다른 항목)를 쿼리할 수 있는 권한이 있는 경우에만 작동합니다.
이것이 바로 제가 정리한 개념 증명입니다.
#!/usr/bin/python
import sys
import cx_Oracle
response=raw_input("Enter schema.table_name: ")
(schema, table) = response.split('.')
schema = schema.upper()
table = table.upper()
sqlstr = """select column_name,
data_type,
data_length
from all_tab_cols
where owner = '%s'
and table_name = '%s'""" % ( schema, table )
## open a connection to databases...
try:
oracle = cx_Oracle.Connection( oracleLogin )
oracle_cursor = oracle.cursor()
except cx_Oracle.DatabaseError, exc:
print "Cannot connect to Oracle database as", oracleLogin
print "Oracle Error %d: %s" % ( exc.args[0].code, exc.args[0].message )
sys.exit(1)
try:
oracle_cursor.execute( sqlstr )
# fetch resultset from cursor
for column_name, data_type, data_length in oracle_cursor.fetchmany(256):
data_length = data_length + 0
if data_length < len(column_name):
if data_type == "CHAR" or data_type == "VARCHAR2":
print "column %s format a%d" % ( column_name.upper(), len(column_name) )
else:
print "-- Handle %s, %s, %d" % (column_name, data_type, data_length)
except cx_Oracle.DatabaseError, e:
print "[Oracle Error %d: %s]: %s" % (e.args[0].code, e.args[0].message, sqlstr)
sys.exit(1)
try:
oracle_cursor.close()
oracle.close()
except cx_Oracle.DatabaseError, exc:
print "Warning: Oracle Error %d: %s" % ( exc.args[0].code, exc.args[0].message )
print "select *"
print "from %s.%s" % ( schema, table )
XML 포맷이 필요 없거나 원한다면 약간의 해킹이지만 DBMS_XMLGEN 패키지를 사용할 수 있을 것입니다.이 스크립트는 전체 열 이름을 태그 이름으로 하는 임의 쿼리에 대한 XML 파일을 제공합니다.
VARIABLE resultXML clob;
SET LONG 100000; -- Set to the maximum size of the XML you want to display (in bytes)
SET PAGESIZE 0;
DECLARE
qryCtx DBMS_XMLGEN.ctxHandle;
BEGIN
qryCtx := dbms_xmlgen.newContext('SELECT * from scott.emp');
-- now get the result
:resultXML := DBMS_XMLGEN.getXML(qryCtx);
--close context
DBMS_XMLGEN.closeContext(qryCtx);
END;
/
print resultXML
이 기능을 VoraX에 구현하려고 하면 같은 문제가 생겼습니다.다음 버전에서는 다음과 같은 솔루션을 염두에 두고 있습니다.
set feedback off
set serveroutput on
declare
l_c number;
l_col_cnt number;
l_rec_tab DBMS_SQL.DESC_TAB2;
l_col_metadata DBMS_SQL.DESC_REC2;
l_col_num number;
begin
l_c := dbms_sql.open_cursor;
dbms_sql.parse(l_c, '<YOUR QUERY HERE>', DBMS_SQL.NATIVE);
DBMS_SQL.DESCRIBE_COLUMNS2(l_c, l_col_cnt, l_rec_tab);
for colidx in l_rec_tab.first .. l_rec_tab.last loop
l_col_metadata := l_rec_tab(colidx);
dbms_output.put_line('column ' || l_col_metadata.col_name || ' heading ' || l_col_metadata.col_name);
end loop;
DBMS_SQL.CLOSE_CURSOR(l_c);
end;
열 크기를 조정하는 대신 형식 지정 등은 원하는 열 이름으로 열 제목을 강제합니다.DBA에게도 동일한 접근 방식이 적용될 것으로 생각합니다.TAB_COLUMNS 솔루션이지만 별칭도 고려하고 쿼리하는 열만 가져오기 때문에 DBMS_SQL 솔루션을 선호합니다.
편집: "열 제목"만 사용하면 안 됩니다.여전히 "열 형식" 문을 사용해야 합니다.그러니까 제가 전에 했던 대답은 무시해주세요.
테이블이나 뷰에서 모든 열을 선택하는 경우(열을 먼저 쓰는 경우), 데이터를 쓰는 경우) 이 작업이 수행됩니다.
아래에서는 TAB로 구분되었다고 가정합니다. 기호를 두 해야 합니다. 구분 기호의 새하려면 colsep래 listagg다입니다. 쉼표 구분 기호의 예: 새 colsep 행은 다음과 같습니다.set colsep ',' 그리고 새로운 listagg 라인 조정이 대체될 것입니다.chr(9)와 함께chr(44)
/*google "sqlplus OPTION" to get the meaning */
set colsep ' ' --literal TAB; can alter to taste
set HEADING OFF
set UNDERLINE OFF
set PAGESIZE 50000
set LINESIZE 32767
set TERMOUT OFF
set TRIMSPOOL ON
set FEEDBACK OFF
set WRAP OFF
set NEWPAGE none
/*first, write the column names to the file*/
spool "C:\yourPath\output.txt"
select listagg(column_name, chr(9) ) within group (order by column_id)
FROM dba_tab_columns
WHERE owner = 'SomeOwner'
AND table_name = upper('ViewOrTable_Name')
;
/*now append the data*/
spool "C:\yourPath\output.txt" append
select * from SomeOwner.ViewOrTable_Name
where 1 = 1
;
/*stop spooling*/
spool OFF
언급URL : https://stackoverflow.com/questions/351657/preventing-sqlplus-truncation-of-column-names-without-individual-column-formatt
'sourcecode' 카테고리의 다른 글
| 스프링 시큐리티 vs Apache Shi Shiro (0) | 2023.09.26 |
|---|---|
| MySQL 인덱스의 사용 통계를 알아보시겠습니까? (0) | 2023.09.26 |
| 문자열 변환이 있는 경우 wpml-config.xml의 용도는 무엇입니까? (0) | 2023.09.26 |
| 최대 절전 모드를 사용하여 blob 데이터를 청크 단위로 읽기/쓰기 (0) | 2023.09.26 |
| jquery - 성공 시 ajax 결과를 사용한 반환 값 (0) | 2023.09.26 |