Oracle SQLPlus中colsep与拼接输出差异及解决方法问询

我执行以下SQL*Plus脚本时：

set heading off;
set feedback off;
set linesize 10000;
set newpage NONE;

select id||'|'||template||'|'||CREATED_DATE from SREAPP.ESSLOG_ERR_MESG_TEMPLATES;

exit;

能得到符合预期的竖线分隔输出：

→ sqlplus -s "$OMC_USERNAME/$OMC_PASSWORD@sredb1_high" @sql/gettemplates.sql | head -3
1693613217|Execution error for request {.request_id}. Reason: You must enter a value for the purchase order, in-transit shipment, transfer order, or RMA when resending the receipt advice.|01-JUN-23 12.00.00.000000 AM
3474006367|Execution error for request {.request_id}. Reason: {.ess_code} Job logic indicated a system error occurred while executing an asynchronous java job for request {.request_id}. Job error is: null|01-JUN-23 12.00.00.000000 AM
4116411670|Execution error for request {.request_id}. Reason: PscSecurityUpdateCredentialExecutor.updateUserCredential() - Got exception while updating puds proxy user credentials for the user: puds.pscr.anonymous.user and storing it in credential store with user key: PUK#_PSCR_ANONYMOUS_USER|01-JUN-23 12.00.00.000000 AM

但如果用colsep '|'的方式查询：

set heading off;
set feedback off;
set linesize 10000;
set newpage NONE;
set colsep '|'

select * from SREAPP.ESSLOG_ERR_MESG_TEMPLATES;

exit;

输出会出现大量多余空格甚至换行：

sqlplus -s "$OMC_USERNAME/$OMC_PASSWORD@sredb1_high" @sql/gettemplates.sql | head -3
1693613217|Execution error for request {.request_id}. Reason: You must enter a value for the purchase order, in-transit shipment, transfer order, or RMA when resending the receipt advice.                                                                                                                                                                                                                     |01-JUN-23 12.00.00.000000 AM                                        |
3474006367|Execution error for request {.request_id}. Reason: {.ess_code} Job logic indicated a system error occurred while executing an asynchronous java job for request {.request_id}. Job error is: null                                                                                                                                                                                                                            |01-JUN-23 12.00.00.000000 AM                                        |
4116411670|Execution error for request {.request_id}. Reason: PscSecurityUpdateCredentialExecutor.updateUserCredential() - Got exception while updating puds proxy user credentials for the user: puds.pscr.anonymous.user and storing it in credential store with user key: PUK#_PSCR_ANONYMOUS_USER                                                                                                                                   |01-JUN-23 12.00.00.000000 AM       

请问如何通过声明分隔符的方式，得到和字符串拼接完全一致的输出，且不需要外部工具处理？

（表结构说明：ID为NUMBER类型，TEMPLATE为CLOB类型，CREATED_DATE为TIMESTAMP类型）

要解决colsep导致的多余空格问题，需要配合设置列格式和输出修剪参数，具体修改后的脚本如下：

set heading off;
set feedback off;
set linesize 10000;
set newpage NONE;
set colsep '|'
-- 移除输出行尾部的多余空格
set trimout on;
-- 移除spool文件尾部的多余空格
set trimspool on;
-- 禁止长列内容自动换行
set wrap off;
-- 为每个列设置紧凑格式，避免默认填充
column id format a20;
column template format a4000;
column created_date format a30;

select id, template, created_date from SREAPP.ESSLOG_ERR_MESG_TEMPLATES;

exit;

关键设置说明 #

1. trimout on / trimspool on：直接移除输出行末尾因列宽填充产生的多余空格，是解决空格问题的核心。
2. column ... format：
   • 给数值类型的ID设置足够长度的字符格式，避免默认数值列的固定宽度填充；
   • 给CLOB类型的TEMPLATE设置足够大的字符格式，确保内容完整且无冗余填充；
   • 给时间戳类型的CREATED_DATE匹配其输出长度的格式，避免不必要的空格。
3. wrap off：防止长文本列自动换行，保持单行输出的完整性。

设置完成后，colsep '|'的输出会和字符串拼接的结果完全一致，无多余空格和换行。

内容的提问来源于stack exchange，提问作者Christian Bongiorno