On May 3, 1:27 pm, Michael Moore <michaeljmo...@gmail.com> wrote: > 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 > > > > On Tue, May 3, 2011 at 12:26 PM, Kate <katepass...@gmail.com> wrote: > > 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 > > > -- > > 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-PLSQL@googlegroups.com > > To unsubscribe from this group, send email to > > oracle-plsql-unsubscr...@googlegroups.com > > For more options, visit this group at > >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 -- 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-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
