按Oracle SQL脚本的原样打印CLOB内容 [英] Print CLOB content out as is in Oracle SQL script

问题描述

从这里开始是我要完成的任务的全局视图.我需要根据特定SQL请求的结果创建一个xml文件，并将其存储在客户端计算机上的文件中.为此，我有一个使用xslt执行DBMS_XMLGen的SQL脚本，我将使用sqlplus从命令行运行该脚本并将输出通过管道传递到文件中.

To start with here is the bigger picture of the task I'm trying to do. I need to create a xml file from the results of the particular SQL request and store it in a file on the client computer. For that I have a SQL script that does the DBMS_XMLGen with xslt, which I'm going to run from a command line with sqlplus and pipe the output into a file.

我现在遇到的问题是，必须将XML代码的内容(存储在CLOB中)拆分为DBMS_OUTPUT.PUT_LINE的较小块，并且每个块都以换行符结尾，从而破坏了XML代码.我想知道是否可以像在屏幕上那样打印BLOB的内容吗?

The problem I'm having now is that content of the XML code (stored in CLOB) has to be splitted into smaller chunks for DBMS_OUTPUT.PUT_LINE, and every chunk ends up with a new line character, breaking the structure of the XML code. I wonder if there's a way to print the content of a BLOB as is on the screen?

这是SQL脚本的示例:

Here's the example of the SQL script:

SET SERVEROUTPUT ON FORMAT WRAPPED;
set feedback off
DECLARE
 v_ctx   DBMS_XMLGen.ctxHandle;
 v_xml   CLOB;
 v_xslt  CLOB;
 l_offset number := 1;
BEGIN
  v_ctx := DBMS_XMLGen.newContext('SELECT * FROM TABLE');

--  DBMS_XMLGen.setXSLT(v_ctx, v_xslt); --not relevant here

  v_xml := BMS_XMLGen(v_ctx);
  DBMS_XMLGen.closeContext(v_ctx);

  loop exit when l_offset > dbms_lob.getlength(v_xml);
   DBMS_OUTPUT.PUT_LINE (dbms_lob.substr( v_xml, 255, l_offset));
   l_offset := l_offset + 255;
  end loop;


EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(Substr(SQLERRM,1,255));
 raise;
END;
/

除每255个符号后的换行符外，我得到的输出是正确的.而且我不能只是稍后删除行尾，我需要XML可读性

The output I'm getting is correct apart from the new line character after every 255 symbols. And I can't just remove the end of lines later, I need the XML to be readable

有什么想法吗?

干杯， 狮子座

推荐答案

您可以添加定界符并打印254个字符，然后在notepad ++(扩展模式为~\r\n)中替换此定界符:

You can add a delimiter and print out 254 characters, then in notepad++ (in extended mode ~\r\n) replace this delimiter :

 loop exit when l_offset > dbms_lob.getlength(v_xml);
   DBMS_OUTPUT.PUT_LINE (dbms_lob.substr( v_xml, 254, l_offset) || '~');
   l_offset := l_offset + 255;
  end loop;

这篇关于按Oracle SQL脚本的原样打印CLOB内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！