Re: SQL Exception: Bigger type length than Maximum

Thanks for your responses; it was not " a value (text) was too big for the corresponding datatype."

The problems were:
1. Bad data in the tables (cols. defined as NOT NULL had NULLs in them probably because the col. type was changed as an afterthought & the existing nulls were not removed ?). Apparently JDBC (JDK or Oracle) does not like to convert NULLs into string types in the code. 2. Some of the cols.in the select were VARCHAR2 but some of the others were NUMBER(14,4) & NUMBER(10); again it seems converting NULLs from these into VARCHAR in the code breaks JDBC somewhere.

The fix was to use "to_char(....)" in my select clause (for all non-VARCHAR cols.) to ensure all conversion to VARCHAR2 took place at the ORACLE level & not at the JDBC level. So the select clause looks like: select to_char(c1, '999,999')......etc. where c1 is a NUMBER or some other non-VARCHAR data type column.

Thanks
amit

dentaku1_at_my-deja.com wrote:

> I don't have an answer for you, but when I got this message it always
> turned out that a value (text) was too big for the corresponding
> datatype.
>
> From your tests:
> > The query works fine in SQL Plus & shows that for
> > " t1.c1 like 'a%' " number of rows is 43 - works
> > " t1.c1 like 'b%' " number of rows is 363 - NO work
> > " t1.c1 like 'bxy%' " number of rows is 150 - works
> I would say, that there is a row starting with 'b' that can help you
> find the bug. The third line 'bxy%' shows, that this 'error causing' row
> starts with b but the second and third character is not an xy. Try to
> restrict the query step by step til you get a minimal result set. Then
> study the data and try to find different data (lengths) to other rows
> that work.
>
> In article <3796951B.D609D881_at_ix.netcom.com>,
> amitp_at_ix.netcom.com wrote:
> > This is a multi-part message in MIME format.
> > --------------A7F18D4EDA9D2E5B69268461
> > Content-Type: text/plain; charset=us-ascii
> > Content-Transfer-Encoding: 7bit
> >
> > Solaris 2.5, Oracle 7.x, Oracle JDBC Thin (Type 4),
> > Netscape 3.5.1 web server (non-issue)
> > Java servlet (JRunPro 2.2.x) (non-issue)
> >
> > I'm running a single query in the servlet that looks something
> > like:
> >
> > "select c1,c2,...,c22
> > from t1,t2,t3,t4
> > where <a bunch of joins using t1,t2,t3,t4)
> > and t1.c1 like 'a%'
> >
> > I use the OracleStatement class to define types of the
> > columns (they are all VARCHAR2(255)) upfront, to avoid 2
> > roundtrips to the database.
> >
> > Query runs fine & ResultSet object has a complete set of
> > results only if the number of rows is less than 180.
> > I keep a count of the number of rows (rows are appended into a
> > string buffer) & diplay the count value in the catch block.
> >
> > If the number of rows exceeds 180, (ie. if I change the
> > where clause & use " t1.c1 like 'b%' " which has about
> > 363 rows) the SQL Exception message shows up:
> >
> > "Bigger type length than Maximum"
> >
> > Vendor code in the exception is 0.
> >
> > The query works fine in SQL Plus & shows that for
> > " t1.c1 like 'a%' " number of rows is 43 - works
> > " t1.c1 like 'b%' " number of rows is 363 - NO work
> > " t1.c1 like 'bxy%' " number of rows is 150 - works
> >
> > The documentation (or lack thereof) on Oracle's Jdbc classes
> > is pathetically poor. They do'nt even provide source for
> > their classes in the classes111.zip file that I am using .
> >
> > What does that exception mean ? How do I get around it
> > (without displaying results in batches via stored proc.
> > etc.) ? Can I somehow reset the "Maximum" value
> > somewhere ?
> >
> > Any suggestions/help will be appreciated.
> > amit
> >
> > --------------A7F18D4EDA9D2E5B69268461
> > Content-Type: text/x-vcard; charset=us-ascii;
> > name="amitp.vcf"
> > Content-Transfer-Encoding: 7bit
> > Content-Description: Card for Amit Pradhan
> > Content-Disposition: attachment;
> > filename="amitp.vcf"
> >
> > begin:vcard
> > n:Pradhan;Amit
> > x-mozilla-html:FALSE
> > org:Home
> > version:2.1
> > email;internet:amitp_at_ix.netcom.com
> > x-mozilla-cpt:;0
> > fn:Amit Pradhan
> > end:vcard
> >
> > --------------A7F18D4EDA9D2E5B69268461--
> >
> >
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.

Received on Sat Jul 24 1999 - 21:57:57 CDT