Предотвращение усечения имен столбцов sqlplus без форматирования отдельных столбцов

Я не думаю, что 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 )

Вы можете попробовать динамически генерировать команды "column x format 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. Вам нужно будет добавить декодирование, например, чтобы изменить сгенерированную команду «столбец» на ДАТЫ или ЧИСЛА.

ОБНОВЛЕНИЕ. Оказывается, исходный 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"

Это должно обеспечить разумное форматирование. Вы, конечно, можете указать свои собственные предпочтения для максимальной ширины столбцов char и что делать с столбцами 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';

Это своего рода хитрость, если вам не нужно или не нужно форматирование 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, поскольку он также учитывает псевдонимы и получает только запрашиваемые столбцы.

Обновлено: Использование только «заголовка столбца» не работает. По-прежнему необходимо использовать операторы «формат столбца». Так что, пожалуйста, проигнорируйте мой предыдущий ответ.

Ни одно из предложенных решений не позволяет отображать исходные имена столбцов, поэтому я не уверен, почему люди голосуют за них ... У меня есть «хак», который работает с исходным запросом, но мне он действительно не нравится ... То есть вы фактически добавляете или добавляете строку к запросу для каждого столбца, чтобы они всегда были достаточно длинными для заголовка столбца. Если вы находитесь в режиме HTML, как и плакат, небольшой дополнительный белый интервал не повредит ... Это, конечно, замедлит ваш запрос abit ...

например

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;

Конечно, вы могли бы написать хранимую процедуру, чтобы сделать что-то лучше, но на самом деле это кажется излишним для этого простого сценария.

Это все еще не соответствует первоначальному запросу плакатов. В этом случае требуется вручную перечислить столбцы, а не использовать select *. Но, по крайней мере, это решение работает, когда вы хотите детализировать поля.

Однако, поскольку действительно нет проблем с слишком длинными полями в HTML, есть довольно простой способ исправить решение Криса, чтобы он работал в этом примере. Это просто выберите использование максимального значения, которое позволит Oracle. К сожалению, это по-прежнему не будет работать для КАЖДОГО поля каждой таблицы, если вы явно не добавите форматирование для каждого типа данных. Это решение также не будет работать для объединений, поскольку разные таблицы могут использовать одно и то же имя столбца, но другой тип данных.

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;