Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Sizeof in PL/SQL

Re: Sizeof in PL/SQL

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Wed, 15 Dec 1999 17:51:22 +0800
Message-ID: <3857649A.7BD1@yahoo.com>


Thomas Kyte wrote:
>
> 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

Apologies for not reading the code more thoroughly..

CHeers
--



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." Received on Wed Dec 15 1999 - 03:51:22 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US