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;
Discuss This Question: