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]
