Re: Sizeof in PL/SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 13 Dec 1999 13:28:47 -0500
Message-ID: <ciea5sc035ofklsdv9n11tbb6m2mits20r@4ax.com>

A copy of this was sent to Andrew Protasov <a_protasov_at_hotmail.com> (if that email address didn't require changing) On Mon, 13 Dec 1999 16:08:36 GMT, you wrote:

>
>
>I know about this solution, but it is too slow for
>runtime. I need something faster.
>

that'll be the fastest way to get that information. one way that does not use the DD but is slower is:

tkyte_at_8i> create or replace function sizeof( p_str in out varchar2 ) return number
2 as

  3          l_size number default 0;
  4          l_str  long default p_str;
  5  begin
  6          p_str := null;
  7          for i in 1 .. 32765 loop
  8                  p_str := p_str || '*';
  9                  l_size := i;
 10          end loop;
 11  exception
 12          when value_error then
 13                  p_str := l_str;
 14                  return l_size;

15 end;
16 /

Function created.

Other then that -- thats the only way to do it.

>Andrew
>
>In article <38538064.6873_at_yahoo.com>,
> connor_mcdonald_at_yahoo.com wrote:
>> Andrew Protasov wrote:
>> >
>> > Hi,
>> >
>> > Is there anything similar to C sizeof function
>> > in PL/SQL? For example, if I have something
>> > like
>> >
>> > declare
>> > x varchar2(20);
>> > i integer;
>> > begin
>> > i:=sizeof(x);
>> > end;
>> >
>> > value of i must be always 20.
>> >
>> > There is vsize function, but it returns only
>> > current size, not maximum.
>> >
>> > I know how to get column length from all_tab_columns,
>> > but it is too slow.
>> >
>> > Andrew Protasov
>> >
>> > Sent via Deja.com http://www.deja.com/
>> > Before you buy.
>>
>> You could always have a look at the definition of all_tab_columsn in
>> dba_views and just pick out the bits you want to make it faster...
>> --
>> ===========================================
>> Connor McDonald
>> "These views mine, no-one elses etc etc"
>> connor_mcdonald_at_yahoo.com
>>
>> "Some days you're the pigeon, and some days you're the statue."
>>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Dec 13 1999 - 12:28:47 CST