CLOB Text - Convert to String and remove carriage returns/line feeds

[Kate]

Hello -

(I will try to provide all of the relevant pieces of information, I
apologize if this is too detailed.)

I am working with Oracle + Jasper Ireports to develop reports.

The parent level report only returns a parameter of a project name
from the user.
The second level (first subreport) report returns data for any/all
relevant artifacts to that project. There are six 'types' of data, so
6 slightly varied reports.
The third level (second nested subreport) retrieves any/all associated
comments for each of the artifacts.

In the third query, I am trying to retrieve data from a CLOB and
convert it to a string, this works with no issues. However, when I try
to replace carriage returns/line feeds within the string text, I
receive a java heap memory issue. Does anyone have any suggestions on
how to change the query to allow it to retrieve data?

Note: I can run this third sub query on all projects and it works,
it's only when it's being run based on parameters from the parent
queries that it hangs.

select DISTINCT com.id, com.date_created, sfeeuser.FULL_NAME,
replace(dbms_lob.substr(com.description,1000,1),chr(13)||chr(10),' ')
as "Description"
from sfee.SFCOMMENT com, sfee.artifact art, sfee.sfuser sfeeuser
where com.OBJECT_ID = art.id AND
com.CREATED_BY_ID = sfeeuser.id
ORDER BY com.date_created DESC;

I want to thank you if you read the whole thing or if you have any
suggestions, thank you very much for your time.

Kate

[Michael Moore]

I don't exactly understand your problem, but this query might make you think about some things you can try. "blurb" is datatype NCLOB, but it would work the same for CLOB.

select cast (substr(blurb,1,4000) as varchar2(4000)) 

from qsn_app.tx_blurb_new where rownum < 4;

Mike

--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle...@googlegroups.com
To unsubscribe from this group, send email to
Oracle-PLSQL...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en

[ddf]

> >http://groups.google.com/group/Oracle-PLSQL?hl=en- Hide quoted text -
>
> - Show quoted text -

Here is another possibility (it converts the first 32k of a clob to a
varchar2 in a PL/SQL block):

SQL> create table lobstuff(
2 flurm number,
3 schmelzo clob,
4 guack varchar2(20));

Table created.

SQL>
SQL> insert into lobstuff
2 (flurm, schmelzo, guack)
3 values
4 (1, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
5 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
6 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
8 aaaaaaaaaaa', 'Ain''t it??');

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> set long 50000
SQL>
SQL> select schmelzo from lobstuff;

SCHMELZO
--------------------------------------------------------------------------------
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaa


SQL>
SQL> set serveroutput on size 1000000
SQL> declare
2 longtxt varchar2(32767);
3
4 begin
5 select schmelzo
6 into longtxt
7 from lobstuff
8 where flurm = 1;
9
10 longtxt:=replace(longtxt,chr(10),null);
11
12 dbms_output.put_line(longtxt);
13
14 end;
15 /
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

PL/SQL procedure successfully completed.

SQL>

You can, of course, use the dbms_lob.substr() function to keep
'walking' the clob in roughly 32k chunks and let PL/SQL do some of the
work for you.


David Fitzjarrell