Re: Select ing a 6K CLOB through DB LINK

In article <bba7c11413ec19fb668b3ee8967267fe.4284_at_mygate.mailgate.org>, "Randy says...
>
>Hello all--
>
>I have been successfully selecting the first 4000 bytes of a clob through a
>database link by defining a view over the clob table that uses the
>dbms_lob.substr call (EG. DBMS_LOB.SUBSTR(DESCR,4000,1) DESCR.)
>
>When I extend the substr length out to 6000 bytes, I receive and ORA-06502
>"...character string buffer too small" error when I try to select the same clob
>data through the database link. I have no problem using the view with 6000
>specified when I query the table directly and don't use the link.
>

You'll get that locally as well:

SQL> create table t ( x clob );

Table created.

SQL> declare
  2 l_data varchar2(6000) default rpad('*',6000,'*');   3 begin
  4 insert into t values ( l_data);
  5 end;
  6 /

PL/SQL procedure successfully completed.

SQL> select dbms_lob.substr(x,6000,1) from t; select dbms_lob.substr(x,6000,1) from t

       *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 1

You can only select VARCHAR2(4000) with SQL -- regardless of local or remote. As soon as you exceed 4000 (2000 in 7.x) it'll stop working.

Will it work for you to select out the clob in 2 pieces?

select dbms_lob.substr( clob_col, 4000, 1 ) part1,

       dbms_lob.substr( clob_col, 4000, 4001 ) part2   from t
/

?

>I have been looking through the documentation and can't find any reference as
>to what character string buffer I am exceeding. Can anybody point me in the
>right direction as to what buffer setting, and how to change it?
>
>Note that I am selecting other columns from the table, so more than just the
>clob column is involved.
>
>The platform is NT using 8.1.7, and the queries are all via SQL*PLUS.
>
>Any help is greatly appreciated?
>
>Thanks!
>
>Randy
>
>
>--
>Posted from mrj.mrj.com [192.101.175.2]
>via Mailgate.ORG Server - http://www.Mailgate.ORG

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp