Re: Sizeof in PL/SQL

A copy of this was sent to Connor McDonald <connor_mcdonald_at_yahoo.com> (if that email address didn't require changing) On Tue, 14 Dec 1999 22:00:34 +0800, you wrote:

>Andrew Protasov wrote:
>>
>> Thank you, Thomas
>>
>> But your solution modifies variable value also.
>> In many cases it is not good. I need pure function.
>>

well it does not permanently modify the variable -- I reset it on the way out. It is true that since I use an IN OUT you cannot call sizeof directly from SQL but it is pure enough to be called from a function that IS called from sql:

tkyte_at_8i> create or replace function foo return number   2 as
  3 x varchar2(25);
  4 begin
  5 return sizeof(x);
  6 end;
  7 /

Function created.

tkyte_at_8i>
tkyte_at_8i> select foo from dual;

       FOO

        25

[snip]

>> >
>> > 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.
>> >

[snip]

>
>I think Thomas has a typo in the code - use l_str instead of p_str and
>you'll find it does not modify the variable...

No, the use of P_STR and L_STR was purposeful.

We need to find the max length of p_str. the only way to do that is fill it up (destroy it) until it blows up on an exception. At the very least, p_str *must* be an OUT parameter. I made it IN OUT so I could copy it and restore it at the end (in the event p_str had some useful data in it, p_str will retain its value under all conditions with this procedure).

If we measured l_str -- it would be the max length we could make l_str -- NOT p_str.

--
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 Tue Dec 14 1999 - 16:22:09 CST