Oracle假脱机文件转换为.txt文件.标题选项 [英] Oracle spool file to a .txt file. Options for headings

问题描述

我正在尝试将Oracle文件假脱机到.txt文件.我想显示标题，但是我正在做一个TRIM以消除多余的空格，并且标题中显示了"TRIM"，当我有更多字段时，由于标题太长，一些标题没有显示出来.如何消除字段之间的空格或只显示字段名称来显示标题?

I'm trying to spool an Oracle file to a .txt file. I want to display the headings but I am doing a TRIM to eliminate extra spaces and the 'TRIM' is showing up in the header, when I have more fields, some of the headings do not show up because it is too long. How can I either eliminate spaces in between the fields or get my headings to show up with just the field name?

set newpage none;
set space 0;
set feedback off; 
set linesize 5000;
set pagesize 50000; 
set echo off;
set termout off;
set trimspool on;
set colsep '|';
set heading on;
set headsep on;
set underline off;
set trim on;

spool /dch/sap_load/ZPONE_MCC/QE1/mock3/test_export.txt


SELECT
TRIM(    MATNR                  )|| '|' || 
TRIM(    WERKS                  )|| '|' || 
TRIM(    STLAN                  )|| '|' || 
TRIM(    DATUV                  )|| '|' || 
TRIM(    BMEIN                  )|| '|' || 
TRIM(    BMENG                  )|| '|' ||
TRIM(    STLAL                  )|| '|' ||
TRIM(    STLST                  )|| '|' ||
TRIM(    ZTEXT                  )|| '|' ||
TRIM(    AENNR                  )|| '|' ||
TRIM(    REVLV                  )|| '|' ||
TRIM(    LABOR                  )
FROM CLS_MCC1XX_BOM_HEADER;

这是我的输出的样子:

This is how my output looks:

谢谢， 玛西(Marcie)

Thanks, Marcie

推荐答案

您可以按照Matthew Strawbridge的建议使用列别名，但必须将其用双引号而不是单引号引起来:

You can use a column alias as Matthew Strawbridge suggests, but you'd have to enclose it in double-quotes not single quotes:

SELECT ...
AS "MATNR|WERKS|STLAN|DATUV|BMEIN|BMENG|STLAL|STLST|ZTEXT|AENNR|REVLV|LABOR"
FROM CLS_MCC1XX_BOM_HEADER;

更重要的是，您受标识符最大长度(30个字符)的限制，因此在此无效.

and more importantly you're restricted by the maximum length of an identifier, 30 characters, so this won't work here.

另一种方法是改用假标题:

Another option is to make your fake header instead:

set heading off
prompt MATNR|WERKS|STLAN|DATUV|BMEIN|BMENG|STLAL|STLST|ZTEXT|AENNR|REVLV|LABOR
SELECT ...
FROM CLS_MCC1XX_BOM_HEADER;

使用set heading off(或set pagesize 0)抑制真正无用的标头，而是使用prompt SQL * Plus命令编写所需的文本.

You suppress the real, unhelpful header, with set heading off (or set pagesize 0) and instead use the prompt SQL*Plus command to write the text you want.

您也可以select '<your header>' from dual而不是使用prompt，但我觉得它更干净.

You could also select '<your header>' from dual rather then using prompt, but I find it a bit cleaner.

这篇关于Oracle假脱机文件转换为.txt文件.标题选项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！