Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

August 28, 2008

How to transfer LONG datatype over DBLINK as VARCHAR?

Filed under: Oracle How To — H.Tonguç Yılmaz @ 4:42 am

Siebel as one of sources for our ETL and customer requested S_NOTE_SR.NOTE column with LONG datatype, not a smart idea to transfer this data to DW but customer is usually the king here.

SYS.DBMS_METADATA_UTIL.LONG2VARCHAR($ORACLE_HOME/rdbms/admin/dbmsmetu.sql) function is an undocumented option for this need. It worked for us on 10.2 and I hope if you need it somehow you test it if it will again work for your case. :)


SQL> SELECT column_function
  2    FROM tta01ext_table_cols t
  3   WHERE table_name = 'S_NOTE_SR'
  4     AND table_column = 'NOTE'
  5  ;

COLUMN_FUNCTION
------------------------------------------------------------------------------------------------------------
SYS.dbms_metadata_util.long2varchar@DBLK_SIEBEL(length=>4000,tab=>'SIEBEL.S_NOTE_SR',col=>'NOTE',row=>rowid)

ps: TO_CLOB and DBLINK problem –


-- local test is fine with TO_CLOB
SELECT priv_flg, created, to_char(to_clob(note)), note_type
  FROM src_siebel.s_note_sr
 WHERE rownum < 2; 
 
-- remote test fails with TO_CLOB
SELECT priv_flg, created, to_char(to_clob(note)), note_type
  FROM siebel.s_note_sr@dblk_siebel
 WHERE rownum < 2;
 
ORA-00932: inconsistent datatypes: expected NUMBER got LONG
ORA-02063: önceki line, kaynağı DBLK_SIEBEL

-- remote test is fine with DBMS_METADATA_UTIL.LONG2VARCHAR
SELECT priv_flg,
       created,
       sys.dbms_metadata_util.long2varchar@dblk_siebel(4000,
                                                       'SIEBEL.S_NOTE_SR',
                                                       'NOTE',
                                                       ROWID)
  FROM siebel.s_note_sr@dblk_siebel
 WHERE rownum < 2;

About these ads

2 Comments »

  1. you’ve always got TO_CLOB of course…

    Comment by dombrooks — August 28, 2008 @ 6:58 am | Reply

  2. Dominic hi,

    I was in a rush so couldn’t give the details of the case, For this case the reporting tool forced for VARCHAR and there was OWB transafer over DBLINK.

    Couldn’t format the example case here in the comment section so I appended them at the end of the post above.

    Hope it is more clear now?

    Comment by H.Tonguç Yılmaz — August 28, 2008 @ 8:38 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 76 other followers

%d bloggers like this: