Re: quicker way to find the length of a record

From: William Robertson <>
Date: Sun, 24 Feb 2008 08:37:25 +0000
Message-ID: <>

I was a bit confused by c, the various c2s and the BULK COLLECTs in your example, since the question is about records. "rec" doesn't seem to be populated anywhere.

A PL/SQL record has nothing like a "length" attribute, and the LENGTH function expects character strings, so yes you have to add the pieces manually. You could have a function to do this and overload it for other types, or you could use an object type in place of a PL/SQL record type, and give it a method that returned the length of all its attributes.

-----Original message-----
From: Oxnard Montalvo
Date: 23/2/08 16:14
> version 10r2
> declare
> type rec_type is record(
> c1 varchar2(1) not null := 'D'
> ,c2 number not null := 12345
> );
> rec rec_type;
> type t is table of number index by pls_integer;
> c t;
> type rec2 is record(
> c1 number
> ,c2 varchar2(10));
> type rec2_type is table of rec2 index by pls_integer;
> c2 rec2_type;
> begin
> select col bulk collect into c from
> (select 121212121212121212129999 as col from dual
> union all
> select 221212121212121212121212121212 as col from dual);
> dbms_output.put_line(length(c(1)));
> select c1,c2 bulk collect into c2 from
> (select 1 as c1,'bbbb' as c2 from dual
> union all
> select 2 as c1,'dkkdk' as c2 from dual);
> -- dbms_output.put_line(length(c2(1))); -- fails
> -- dbms_output.put_line(length(rec)); -- fails
> dbms_output.put_line((length(rec.c1) + length(rec.c2))); -- works but a pain
> end;
> /
> seems like the only way to obtain the length is to add all the pieces. The adding is not bad on small records but large one is a bit of a pain. Any way which would use less code to do this?

Received on Sun Feb 24 2008 - 02:37:25 CST

Original text of this message