Re: quicker way to find the length of a record

  • From: William Robertson <william@xxxxxxxxxxxxxxxxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Sun, 24 Feb 2008 08:37:25 +0000

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?

--
//www.freelists.org/webpage/oracle-l


Other related posts: