如何仅从Oracle NCLOB复制前4000个字符到NVARCHAR2(4000)值? [英] How to Copy Only First 4000 Characters from Oracle NCLOB to NVARCHAR2(4000) Value?
问题描述
我是一名程序员，我正在编写一些SQL代码以从远程Oracle数据库中提取数据，并将其插入到本地MS SQL Server的某些表中.
I am a programmer, and I am writing some SQL code to pull data from a remote Oracle database and insert it into some tables on our local MS SQL Server.
我需要将Oracle NCLOB列带入MS SQL Server数据库中的NVARCHAR列.
I need to bring an Oracle NCLOB column to an NVARCHAR column in our MS SQL Server database.
我们没有DBA员工可以为我这样做.
We have no DBA staff to do this for me.
对于我来说，拥有整个NCLOB值并不重要，因此4000个字符只是我选择的任意大数字.
For my purposes, it is not critical to have the entire NCLOB value, so 4000 chars is simply an arbitrarily large number I chose.
我的目标是尽我所能在Oracle端的NVARCHAR2字段中存储尽可能多的数据，并且仍然保持实用性，以便每天刷新一次网络.
My goal is to have as much of the data in my NVARCHAR2 field on the Oracle side as I can and still keep it practical to bring across the network in a daily data refresh.
我已经看到许多关于解决方案的参考，这些解决方案用于获取 all 个数据，并获取 4000个以上个字符，等等.但是，我正在寻找一个简单的版本无需繁重的DBA级别的工作，我就可以轻松地进行安装和维护.
I have seen many many references to solutions to get all the data, and to get data more than 4000 characters, etc. However, I am seeking a simple version that I can easily put in and maintain without heavy-duty DBA-level work.
推荐答案
You will want to use dbms_lob.substr
根据以上引用的帖子:
dbms_lob.substr( clob_column, for_how_many_bytes, from_which_byte );
例如:
select dbms_lob.substr( x, 4000, 1 ) from T;
将为我提供Clob的前4000个字符.请注意，使用时 和我一样，SQL的最大长度为4000.使用plsql可以得到32k:
will get me the first 4000 characters of the clob. Note that when using SQL as I did, the max length is 4000. You can get 32k using plsql:
declare my_var long; begin for x in ( select X from t )
loop
my_var := dbms_lob.substr( x.X, 32000, 1 );
....
declare my_var long; begin for x in ( select X from t )
loop
my_var := dbms_lob.substr( x.X, 32000, 1 );
....
这篇关于如何仅从Oracle NCLOB复制前4000个字符到NVARCHAR2(4000)值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
