I have a string of data that can be more that 40,000 chars long and I am using a CLOB while building this string. I will be building thousands of these strings per fixed width export. I know that I can avoid excessive use of CLOBS by storing the data in multiple varchars and adding them to a CLOB at the end. I just don’t know if this will be any faster. I cannot avoid the CLOB completely but what I can do is avoid concatenating the CLOB over and over again by spreading the data across many varchars. Basically, if you or anyone else had to build a string that was 40,000 chars long how would you all do it? Right now I build the entire sting in a CLOB then insert it into a table. Perhaps, it would be better to concatenate the data in the actual table and just keep adding to the field instead of maintaining a clob variable in PL/SQL?
Thanks,
David
-----Original message-----
From: I-Lin Kuo [EMAIL PROTECTED]
Date: Fri, 23 Jul 2004 12:53:26 -0400
To: CF-Talk [EMAIL PROTECTED]
Subject: Re: Oracle PL/SQL and CLOBS
> Hi David,
>
> You've explained what you're trying to implement but
> not enough of why you've chosen to implement things
> this way.
>
> CLOBS are slow so you should try to avoid them unless
> necessary, but I don't have enough of an understanding
> of what problem you're trying to solve to say whether
> or not you CAN avoid them. Could you give an
> explanation of what problem you're trying to solve?
>
> --- [EMAIL PROTECTED] wrote:
> > This is somewhat off topic I guess... But if
> > someone can help me out, I would be grateful.
> >
> > I have a stored procedure that exports data from a
> > series of tables to a specific format. The file is
> > fixed with and there is data layout for the mapping.
> > Anyway. My question is...
> >
> > Each row contains about 40,000 columns and there are
> > thousands of rows. I am building this row in a
> > clob, I just keep concatenating it until the next
> > row starts. Basic stuff. This is very slow, I am
> > guessing because I am using clobs. In my testing
> > with a varchar2(4000) the procedure is very fast.
> > Obviously, when I test with the varchar, I have to
> > limit the data that I retrieve to under 4000 chars.
> > What I need to know is, is there a better way to
> > handle this?
> >
> > My first thought is to create 10 varchar variables
> > and spread the data across them and bring it
> > together in the end. Or I could create a table with
> > a varchar field and spread it across multiple rows
> > and bring that together in the end.
> >
> > The clobs work fine when exporting just a few rows
> > but when I try to export a few hundred; The program
> > takes about an hour and temp table space grows by
> > 768 megs.
> >
> > Thanks,
> >
> > David
>
> =====
> I-Lin Kuo
> Macromedia CF5 Advanced Developer
> Sun Certified Java 2 Programmer
>
>
>
> __________________________________
> Do you Yahoo!?
> New and Improved Yahoo! Mail - 100MB free storage!
> http://promotions.yahoo.com/new_mail
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
