0

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.

I need to bring an Oracle NCLOB column to an NVARCHAR column in our MS SQL Server database.

We have no DBA staff to do this for me.

For my purposes, it is not critical to have the entire NCLOB value, so 4000 chars is simply an arbitrarily large number I chose.

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.

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.

qxotk
  • 2,384
  • 5
  • 24
  • 39

1 Answers1

2

Check out http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:367980988799.

You will want to use dbms_lob.substr

Per the post referenced above:

dbms_lob.substr( clob_column, for_how_many_bytes, from_which_byte );

for example:

select dbms_lob.substr( x, 4000, 1 ) from T;

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 ); ....

Patrick Szalapski
  • 8,738
  • 11
  • 67
  • 129
Brian Leach
  • 2,025
  • 1
  • 11
  • 14
  • 1
    I had seen the article you referenced, but I was getting errors from the linked server to Oracle stating the "string buffer is too small" - so I moved on. However, re-reading it due to your reference in the answer, I discovered that since the field is NCLOB I had better allow enough room for double-byte characters. To do this I changed the number of characters from 4000 to 2000 - now works like a charm. – qxotk Feb 12 '14 at 23:16