Funny you should, say that. I think I am using varchar2 as the out parameter and not CLOB but only because I was using varchar2 before the CLOB and just never changed it. Thanks for the heads up.
David
-----Original message-----
From: Steven Erat [EMAIL PROTECTED]
Date: Fri, 23 Jul 2004 14:40:18 -0400
To: CF-Talk [EMAIL PROTECTED]
Subject: RE: Oracle PL/SQL and CLOBS
> David,
>
> Assuming this is Oracle, if you're using the Macromedia drivers with a
> stored procedure that has an output parameter type of varchar2, then even
> though the varchar2 datatype can hold up to 32k the Macromedia Oracle driver
> will only return the first 4k. If you use the Oracle Thin Client instead,
> then you can retrieve all 32k.
>
> I just discovered a related bug this week where if the out parameter of a
> stored procedure is clob, and you use the Macromedia Oracle driver, then if
> the CF_SQL_TYPE for the out is set to clob you will get an array of
> characters rather than a contiguous string. Leaving the SP out parameter as
> clob but changing the CF_SQL_TYPE to varchar resolved the problem as the
> clob was returned as a string.
>
> My testing for these issues was done with CFMX 6.1, Macromedia driver
> version 3.3, Oracle 8.1.7.
>
> Hopefully, some of this may help.
>
> Steven Erat
>
>
> _____
>
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Friday, July 23, 2004 11:39 AM
> To: CF-Talk
> Subject: Oracle PL/SQL and CLOBS
>
>
> 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
>
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
