Skip to main content
M
MX_Oracle
Participant
July 12, 2006
Question

Max. Length of varchar2 from Oracle Stored Procedure

  • July 12, 2006
  • 2 replies
  • 6472 views
We are using ColdFusion MX7 and Oracle RAC 10g.

I have an Oracle Stored Procedure which can return strings (varchar2) of more that 4000 bytes

The problem is that ColdFusion is cuting the string at 4000 bytes. I have tested the procedure in SQLPLUS and the stored procedure works ok and can return (OUT parameter) strings of more than 4000 bytes.

This procedure was working fine with version 4.5 where I had the maxlength parameter specified in the CFPROCPARAM type="Out" parameter.

Any help in fixing this will be highly appreciated.

Thanks
This topic has been closed for replies.

2 replies

M
MX_OracleAuthor
Participant
July 12, 2006
Thanks Phil.

The Long Text Buffer (chr) is set to 64000 bytes and I do not have the CLOB option checked.

I did some further research on the data source and found the following:

We have a 3 node Oracle 10g RAC cluster to which ColdFusion is connecting.
I was using the Oracle jdbc drivers in the Datasource, which were restricting the length of string to 4000 bytes.

I then changed the datasource to use the Oracle driver only and I was able to retrieve strings more than 4000 bytes. The problem now is that since I am using Oracle driver as the datasource, so I can only connect to one node of the Oracle cluster, which basically defeats the notion of having a RAC database (no TAF).

Is there a work around to this problem.

Thanks
P
paross1
Participating Frequently
July 12, 2006
I'm not familiar with using Oracle RAC 10g, so I may be off base. Having said that, what version of DataDirect JDBC drivers are you running? I found a tech note that describes a problem with Oracle and LONG datatypes being limited to 2k before version 3.4 Updated DataDirect JDBC drivers (version 3.4)

55308 - 2k limit for a LONG returned from a stored procedure (32k now)

...but

Oracle 10g users
Although these drivers have been designed to work with Oracle 10g Real Application Clusters, Macromedia has not yet tested Oracle 10g RAC with these drivers and ColdFusion MX. Using an Oracle RAC is unsupported.

Also, it looks like version 3.5 of the JDBC drivers has the note specifying that Oracle RAC is unsupported Updated DataDirect JDBC drivers (version 3.5)

Phil
P
paross1
Participating Frequently
July 12, 2006
PL/SQL variables of the VARCHAR2 datatype are good for up to 32,767 bytes, so that obviously isn't the problem. Check the advanced settings for your data source in ColdFusion Administrator and look for the value set for Long Text Buffer (chr). This is "The default buffer size, used if the CLOB option is not selected. The default value is 64000 bytes."

Phil
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices