Custom Function for accessing lob column at remote database link

Symptom:
When I select a table at remote database with lob data,

select remark2 from salesorder@dblink;

it shows the following error


ORA-22992: cannot use LOB locators selected from remote tables
22992. 00000 -  "cannot use LOB locators selected from remote tables"
*Cause:    A remote LOB column cannot be referenced.
*Action:   Remove references to LOBs in remote tables.
Error at Line: 23 Column: 8


I surf the web, get the idea using temporary file.  So I create a custom function for facilitate similar queries.

Solution:  3 steps to get it solve


1. Creating a global temp table at your local database for cache remote db lob data

create global temporary table clob_cache 
( lobtable varchar2(200), 
lobfield varchar2(200), 
lobkey varchar2(200), 
lobdata clob ) 
on commit preserve rows;

2.  Create a function at local database for access the remote lob data

create or replace function fn_remote_clob
(
  p_table in varchar2,
  p_field in varchar2,
  p_rowid in varchar2
) return clob
as
  pragma autonomous_transaction;
  l_tempclob clob;
  l_sql varchar2(4000);
begin
    delete from clob_cache ;
    l_sql := 'insert into clob_cache (lobtable, lobfield, lobkey, lobdata) select '''||p_table||''','''||p_field||''','''||p_rowid||''','||p_field||' from '||p_table||' where rowid = '''||p_rowid||'''';
    EXECUTE IMMEDIATE l_sql;
    l_tempclob := null;
    select lobdata into l_tempclob from clob_cache
      where lobtable = p_table and lobfield= p_field and lobkey=p_rowid;
    commit;
    return l_tempclob;
end fn_remote_clob;


3. Select the data with function

select fn_remote_clob('salesorder@dblink','remark2',ROWID) as remark2
from salesorder@dblink;


The above is old method, which might create error ORA-01555 due to transaction processing issue. The following is new method.

New Solution: Two steps to get it solve

1. create function for CLOB
create or replace function FN_REMOTE_CLOB
( dblnk in varchar2
  ,tbl  in varchar2
  ,col  in varchar2
  ,wherecond in varchar2)
return clob
is
  retval clob;
  tmpraw varchar2(4000);  
  tmplen number;
  tmpchk number;
  chksize number;
begin
  --preset vars
  chksize:=2000;
  dbms_lob.createtemporary (retval,true);
  execute immediate 'select dbms_lob.getlength@'||dblnk||' ('||col||') from '||tbl||'@'||dblnk||' where '||wherecond into tmplen;

  -- precalc  
  tmpchk:=floor(nvl(tmplen, 0)/chksize);

  -- applicate frist chunks  
  for i in 0 .. tmpchk-1
  loop  
    execute immediate 'select dbms_lob.substr@'||dblnk||'('||col||','||chksize||','||((i*chksize)+1)||') from '||tbl||'@'||dblnk||' where '||wherecond into tmpraw;
    dbms_lob.append(retval,tmpraw);
  end loop;

  -- applicate last entry
  if (tmplen-(tmpchk*chksize)) > 0 then
    execute immediate 'select dbms_lob.substr@'||dblnk||'('||col||','||(tmplen-(tmpchk*chksize))||','||((tmpchk*chksize)+1)||') from '||tbl||'@'||dblnk||' where '||wherecond into tmpraw;
    dbms_lob.append(retval,tmpraw);
  end if;
  return retval;
end FN_REMOTE_CLOB;


2. Select the table with created function

select a.scno, FN_REMOTE_CLOB('DBLINK',SALESORDER', 'REMARK2', 'ROWID='''||a.ROWID||'''') from SALESORDER@DBLINK a;

or define your condition when ROWID is not applicable for VIEW instead of table

select a.scno, FN_REMOTE_CLOB('DBLINK',SALESORDER', 'REMARK2', 'SCNO='''||a.SCNO||'''') from VIEW_SALESORDER@DBLINK a;

p.s. the following function is for handling BLOB

create or replace function FN_REMOTE_BLOB
( dblnk in varchar2
  ,tbl  in varchar2
  ,col  in varchar2
  ,wherecond in varchar2)
return blob
is
  retval blob;
  tmpraw raw(2000);  
  tmplen number;
  tmpchk number;
  chksize number;
begin
  --preset vars
  chksize:=2000;
  dbms_lob.createtemporary (retval,true);
  execute immediate 'select dbms_lob.getlength@'||dblnk||' ('||col||') from '||tbl||'@'||dblnk||' where '||wherecond into tmplen;

  -- precalc  
  tmpchk:=floor(nvl(tmplen, 0)/chksize);

  -- applicate frist chunks  
  for i in 0 .. tmpchk-1
  loop  
    execute immediate 'select dbms_lob.substr@'||dblnk||'('||col||','||chksize||','||((i*chksize)+1)||') from '||tbl||'@'||dblnk||' where '||wherecond into tmpraw;
    dbms_lob.append(retval,tmpraw);
  end loop;

  -- applicate last entry
  if (tmplen-(tmpchk*chksize)) > 0 then
    execute immediate 'select dbms_lob.substr@'||dblnk||'('||col||','||(tmplen-(tmpchk*chksize))||','||((tmpchk*chksize)+1)||') from '||tbl||'@'||dblnk||' where '||wherecond into tmpraw;
    dbms_lob.append(retval,tmpraw);
  end if;
  return retval;
end FN_REMOTE_BLOB;





That's it, hope no more guy suffering from similar problem.  Have a good day.

Oracle PLSQL - Handling Email Subject with mime header encoding in RFC2047 (Oracle 9i, 10g and 11g)

In Oracle 11g, it is very easy with function UTL_ENCODE.MIMEHEADER_ENCODE()


select utl_encode.MIMEHEADER_ENCODE('Oracle 發送 Mail 測試')  as t from dual;

you will get the mime header encoded string
=?UTF-8?Q?Oracle=20=E7=99=BC=E9=80=81=20Mail=20=E6=B8=AC=E8=A9=A6?=
then you can use this on UTL_SMTP
WRITE_DATA(L_CONN, 'Subject', utl_encode.MIMEHEADER_ENCODE('Oracle 發送 Mail 測試') );



In Oracle 9i & 10g

However this  does not work in earlier Oracle version like 9i.
Then you need to do this in three steps

1. get the current database character set
SELECT VALUE into L_CHARSET FROM v$nls_parameters  WHERE parameter LIKE 'NLS_CHARACTERSET' ;

2. convert the subject string (P_SUBJECT) to UTF8 character set
P_SUB := 'Oracle 發送 Mail 測試';
L_SUB := UTL_RAW.CAST_TO_RAW(CONVERT(P_SUBJECT,'UTF8',L_CHARSET));
now the  L_SUB will be 4F7261636C6520E799BCE98081204D61696C20E6B8ACE8A9A6



3. format it to the quoted mime header
source : 4F7261636C6520E799BCE98081204D61696C20E6B8ACE8A9A6
target :  =?UTF-8?Q?=4F=72=61=63=6C=65=20=E7=99=BC=E9=80=81=20=4D=61=69=6C=20=E6=B8=AC=E8=A9=A6?=
actually, we need to add a prefix "=?UTF-8?Q?" and a suffix "?=" to the source.
and add "=" every two characters to source

to do this we need a function regexp_replace() on 10g, however this is not available in 9i.  We are using one from Phil's Blog.  The code is attached at end of this page.

     L_SUB := '=?UTF-8?Q?'||regexp_replace( L_SUB, '(..)', '=\1' )||'?=';


then the L_SUB will be same as our target. Simply write this to subject header.

    WRITE_DATA(L_CONN, 'Subject', L_SUB );

REGEXP_REPLACE / REGEXP_LIKE for Oracle 9i 

from Phil's Blog

create or replace
FUNCTION regexp_replace (
   source_char       IN   VARCHAR2
 , pattern           IN   VARCHAR2
 , replace_string    IN   VARCHAR2
 , POSITION          IN   PLS_INTEGER DEFAULT 1
 , occurrence        IN   PLS_INTEGER DEFAULT 0
 , match_parameter   IN   VARCHAR2 DEFAULT NULL)
   RETURN VARCHAR2 AS
/*************************************************************************
 * Program  : regexp_replace
 * Version  : 1.0
 * Author   : Philip Moore
 * Date     : 20-JUN-2009 Anno Domini
 * Purpopse : This provides a pseudo "REGEXP_REPLACE" function for Oracle 9iR2
 * Warnings : Do NOT compile in an Oracle 10GR2 database (or above)!
 *************************************************************************/
   -- Variables
   l_source_string                    VARCHAR2 (32767);
   l_temp_string                      VARCHAR2 (32767);
   l_flags                            VARCHAR2 (10);
   l_occurrence                       PLS_INTEGER;
   l_end_of_pattern_pos               PLS_INTEGER;
   l_string_pos                       PLS_INTEGER;
BEGIN
   -- Substr the source_char to start at the position specified
   l_source_string := SUBSTR (source_char, POSITION);

   -- Set up the flags argument
   IF occurrence = 0 THEN
      l_flags := 'g';
   ELSE
      l_flags := NULL;
   END IF;

   l_flags := l_flags || match_parameter;

   -- Now replace the regular expression pattern globally if "g"
   IF INSTR (l_flags, 'g') > 0 THEN
      owa_pattern.CHANGE (line          => l_source_string
                        , from_str      => pattern
                        , to_str        => replace_string
                        , flags         => l_flags);
   -- Not a global replace - loop until the "occurrence"th occurrence is replaced...
   ELSE
      l_string_pos := 0;
      l_occurrence := 0;
      WHILE l_string_pos < LENGTH (l_source_string) AND l_occurrence < occurrence LOOP
         l_string_pos := l_string_pos + 1;

         l_end_of_pattern_pos := owa_pattern.amatch (line          => l_source_string
                                                   , from_loc      => l_string_pos
                                                   , pat           => pattern
                                                   , flags         => match_parameter);

         IF l_end_of_pattern_pos != 0 THEN
            l_occurrence := l_occurrence + 1;
         END IF;
      END LOOP;

      IF l_occurrence = occurrence THEN
         l_temp_string := SUBSTR (l_source_string
                                , l_string_pos
                                , (l_end_of_pattern_pos - l_string_pos));
         owa_pattern.CHANGE (line          => l_temp_string
                           , from_str      => pattern
                           , to_str        => replace_string
                           , flags         => l_flags);
         l_source_string := SUBSTR (l_source_string
                                  , 1
                                  , l_string_pos - 1) || l_temp_string || SUBSTR (l_source_string, l_end_of_pattern_pos);
      END IF;
   END IF;

   -- Piece the string back together if needed...
   IF POSITION > 1 THEN
      l_source_string := SUBSTR (source_char
                               , 1
                               , (POSITION - 1)) || l_source_string;
   END IF;

   RETURN l_source_string;
END regexp_replace;
/


CREATE OR REPLACE FUNCTION regexp_like (source_char     IN VARCHAR2
                                      , pattern         IN VARCHAR2
                                      , match_parameter IN VARCHAR2 DEFAULT NULL)
RETURN INTEGER
AS
/*************************************************************************
 * Program  : regexp_like
 * Version  : 1.0
 * Author   : Philip Moore
 * Date     : 20-JUN-2009 Anno Domini
 * Purpopse : This provides a pseudo "REGEXP_LIKE" operator for Oracle 9iR2
 * Warnings : Do NOT compile in an Oracle 10GR2 database (or above)!
 *************************************************************************/
  -- Variables
  l_return INTEGER;
BEGIN
   IF owa_pattern.match (line  => source_char
                       , pat   => pattern
                       , flags => match_parameter) THEN
      l_return := 1;
   ELSE
      l_return := 0;
   END IF;

   RETURN l_return;
END regexp_like;
/