Re: Why is sort segment growing extremely big on simple query?

From: Alberto Dell'Era <alberto.dellera_at_gmail.com>
Date: 9 Nov 2004 16:02:20 -0800
Message-ID: <4ef2fbf5.0411091602.4e36f633@posting.google.com>

Don Vaillancourt <donv_at_webimpact.com> wrote ...
> Are you serious?
>
> I can't retrieve from any CLOBs larger than 32KB? That's not good. I
> remember reading the docs and well this is what I found:
>
> FUNCTION DBMS_LOB.SUBSTR
> (lob_loc IN BLOB,
> amount IN INTEGER := 32767,
> offset IN INTEGER := 1)
> RETURN RAW;
BLOBs or CLOBs ?

For CLOBs, the 4000 max size is a limitation of sql, not pl/sql, and it's the maximum you can retrieve, not the maximum size of the CLOB retrieved from.

Using the table i set up in my other post for you (on 9.2.0.5) :

dellera_at_ORACLE9I> select dbms_lob.getlength (x) from don where rownum = 1;

DBMS_LOB.GETLENGTH(X)

---

               400000

dellera_at_ORACLE9I> select dbms_lob.substr (x, 4000, 1) from don where rownum = 1;

DBMS_LOB.SUBSTR(X,4000,1)

---

---

(snip :)

dellera_at_ORACLE9I> select dbms_lob.substr (x, 4001, 1) from don where rownum = 1;
select dbms_lob.substr (x, 4001, 1) from don where rownum = 1

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

But in PL/SQL:

dellera_at_ORACLE9I> declare
  2 l_buffer varchar2 (32767);
  3 l_clob clob;
  4 begin
  5 select x into l_clob from don where rownum = 1;   6 l_buffer := dbms_lob.substr (l_clob, 15000, 1);   7 dbms_output.put_line ('length=' || length(l_buffer));   8 end;
  9 /
length=15000

PL/SQL procedure successfully completed.

I think that "DA Morgan" was referring to a phrase in the docs i pointed you to that says that your original clob-returning SUBSTR (*not* dbms_lob.substr() ) is inefficient for large lobs, whatever that means - large subclob retrieved or large on disk (ask Oracle what they mean).

hth
Alberto Dell'Era Received on Tue Nov 09 2004 - 18:02:20 CST