How to transfer LONG datatype over DBLINK as VARCHAR?

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;

Advertisement