Re: Programatically Accessing Column Length?

From: <afz_at_telus.net>
Date: Thu, 28 Aug 2003 00:40:21 GMT
Message-ID: <Vbc3b.66393$K44.26257_at_edtnps84>

[Quoted] [Quoted] Hi, this is a main crazy idea published somewhere in oreacle magazine:

function get_varchar2_length(p in out varchar2) return number is

  l_min number:=1
  l_max number:=4000; -- should be big enough
  l_current number;
  l_old varchar2(4000):=p;

begin
  loop
    l_current:=trunc((l_min+l_max)/2);
    begin

      p:=lpad('x',l_current,'x');
      l_min:=l_current;
    exception when others then
      l_max:=l_current-1;

    end;
    if (l_max-l_min)<=0 then

      exit;
    end if;
  end loop;

  l_current:=trunc((l_min+l_max)/2);
  p:=l_old;
  return l_current;
end get_varchar2_length;

regards,
AZ
"John Puopolo" <jpuopolo_at_bithammer.com> wrote in message news:9dc35188.0308270714.493e10ab_at_posting.google.com...
> Harald Maier <maierh_at_myself.com> wrote in message
news:<m38ypfmo50.fsf_at_ate.maierh>...
> > jpuopolo_at_bithammer.com (John Puopolo) writes:
> >
> > > I have a program that scans stored procedure names and parameters -
> > > this is working fine. My last problem to solve is being able to
> > > retrieve the max length of a given parameter. For example, given the
> > > following SP declaration:
> > >
> > > PROCEDURE PTest (p_foo IN mytable.mycol%TYPE);
> > >
> > > let's suppose that mytable.mycol is a VARCHAR2(255).
> > >
> > > When I query the SP parameters via all_arguments, I get back the fact
> > > that I'm dealing with a VARCHAR, but the length is never filled in.
> > >
> > > So, the question is... Is there a way to PROGRAMATICALLY retrieve the
> > > length of a VARCHAR2 column given only a stored procedure parameter
> > > whose type is anchored to that column?
> >
> > You cannot specify the _length_ in procedure or function arguments, so
> > I do not understand why you want to extract this information.
> >
> > Harald
>
> Harald,
>
> The thing that makes this interesting is the use of the anchored type,
> e.g., mytable.mycol%TYPE - the mycol%TYPE maps to a real column with a
> real length. What I am trying to do is to programatically generate a
> call to a stored proc. - therefore, when I generate a statement like
> cmd.Parameters.Add(...), let's suppose the SP specified an OUT param
> of VARCHAR that maps to table column type whose length is 128. I
> would like my generated .Add(...) call to include the proper length.
> My experiments have shown that if the output buffer is not allocated
> to sufficient length, the db driver throws a "buffer too small"
> exception. The only way that I can solve this problem w/o having
> direct access to the length is to assume the max length by column
> type, e.g., VARCAHR2 is 4000 characters long. This will work, but is
> suboptimal.
>
> Ideas?
>
> Thanks,
> John
Received on Thu Aug 28 2003 - 02:40:21 CEST