Oracle APEX网格中如何将列的逗号分隔值换行显示

问题场景 #

执行查询：

select store_id, error_message from SVC_UPLD where store_id = 19258;

得到结果（错误信息为逗号分隔的长文本，现有换行是显示自动折行，并非实际换行）：

STOREID  ERROR_MESSAGE
19258    Box Number is mandatory.,Quantity cannot have any special 
         characters.,BRAND is not a valid Brand in             
         MFCS.,RRP_CURRENCY is not a valid currency code in MFCS.,HSCODE 
         will be with Minimum as 8 
         chars,SEASON not a valid season.,Selling_Retail_curr is not a 
         valid currency code in MFCS.,GENDER             
         is not a valid Gender in MFCS.,COLOR is invalid.

尝试用拆分函数将错误信息拆分为多行，但得到的是每条错误对应一行数据：

select regexp_substr (
           error_message,
           '[^,]+',
           1,
           level
         ) value
  from   SVC_UPLD 
  connect by level <= 
    length ( error_message ) - length ( replace ( error_message, ',' ) ) + 1;

输出结果：

STOREID    ERROR_MESSAGE

19258      Quantity cannot have any special characters.
19258      BRAND is not a valid Brand in MFCS.
19258      RRP_CURRENCY is not a valid currency code in MFCS.
19258      HSCODE will be with Minimum as 8 chars
19258      SEASON not a valid season.
19258      Selling_Retail_curr is not a valid currency code in MFCS.
19258      GENDER is not a valid Gender in MFCS.
19258      COLOR is invalid.
19258      ALLOCATION SIZE is mandatory.

需要的是同一单元格内每个错误信息换行显示，格式如下：

STOREID    ERROR_MESSAGE
19258      Box Number is mandatory.,
           Quantity cannot have any special characters.,
           BRAND is not a valid Brand in MFCS.,
           RRP_CURRENCY is not a valid currency code in MFCS.,
           HSCODE will be with Minimum as 8 chars,
           SEASON not a valid season.,
           Selling_Retail_curr is not a valid currency code in MFCS.,
           GENDER is not a valid Gender in MFCS.,
           COLOR is invalid.

解决方案 #

使用REGEXP_REPLACE函数，将原字符串中的逗号替换为「逗号+换行符+缩进空格」，让每个错误信息自动换行并对齐：

SELECT store_id,
       REGEXP_REPLACE(error_message, ',', ','||CHR(10)||'           ') AS error_message
FROM SVC_UPLD 
WHERE store_id = 19258;

说明 #

• CHR(10)是Oracle中的换行符，替换后每个逗号后面会插入换行
• 后面的' '（11个空格）是为了和第一行的错误信息开头对齐，可根据需要调整空格数量
• 如果使用SQL*Plus等工具，需确保设置SET LINESIZE足够大，且工具支持显示单元格内的换行，否则可能看不到预期效果

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