SQL string limitation

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

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)…

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

Processing your response...

Discuss This Question: &nbsp

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

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

Processing your reply...

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

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

Processing your reply...

Ask a Question

Free Guide: Managing storage for virtual environments

Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!

To follow this tag...

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