SQL string limitation

pts.
Tags:
Architecture/Design
SQL
Oracle8i SQL I wrote a query below that pulls in the procedure code from application specific metadata. I wrote it to search up to 4 levels deep of the 2000 character string object_text column. When object_seq greater than 1 exist it will concatenate the code. The SQL bums if it finds more than 2 rows worth of code. Is the 4000 characters a magic number for SQL? I think I recall that when I create the views when Oracle can't figure out what length to make the varchar2 field it makes it 4000. Am I kind of SOL with SQL wanting to pull more than 4000 characters? select 'Panel: '||panel||chr(10)|| 'Derivation: '||deriv_name||chr(10)|| 'Programmed by: '|| moduser||chr(10)|| 'Description: '||description||chr(10)|| 'Code: '||chr(10)||chr(10)|| code from( select o.object_seq, d.panel, d.deriv_name, d.moduser, d.description, o.object_text || decode(lead(o.object_seq,1) over (order by d.panel, d.deriv_name, o.object_seq), 2,lead(o.object_text,1) over (order by d.panel, d.deriv_name, o.object_seq), null) || decode(lead(o.object_seq,2) over (order by d.panel, d.deriv_name, o.object_seq), 3,lead(o.object_text,2) over (order by d.panel, d.deriv_name, o.object_seq), null) || decode(lead(o.object_seq,3) over (order by d.panel, d.deriv_name, o.object_seq), 4,lead(o.object_text,3) over (order by d.panel, d.deriv_name, o.object_seq), null) code from ctsdd.derivation d, ctsdd.objindx o where d.object_id = o.object_id and d.protocol = 'MYPROJECT' order by d.protocol, d.panel, d.deriv_name, o.object_seq ) where object_seq = 1;

Answer Wiki

Thanks. We'll let you know when a new response is added.

The limit of the VARCHAR2 datatype (and presumably for CHAR as well) is indeed 4000 characters.

If you need more, you can use CLOB, which is easily converted to/from VARCHAR2 or CHAR. On the other hand, CLOB columns cannot be indexed (AFAIK). Maybe you can define a function-based index on to_char(clob_column)…

Regards,
ActorJack

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following