Re: Programatically Accessing Column Length?

From: <afz_at_telus.net>
Date: Thu, 28 Aug 2003 00:59:38 GMT
Message-ID: <_tc3b.66450$K44.29660_at_edtnps84>

!!! sorry
should be :
[Quoted] CREATE OR REPLACE FUNCTION Get_Varchar2_Length(p IN OUT VARCHAR2) RETURN NUMBER
IS

  l_min NUMBER:=1;
  l_max NUMBER:=4000;
  l_current NUMBER;
  l_old VARCHAR2(4000):=p;

BEGIN
  FOR i IN 1..100 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;
    dbms_output.put_line(l_min||','||l_max||','||l_current);     IF (l_max-l_min)<=1 THEN

      EXIT;
    END IF;
  END LOOP;

  l_current:=l_max;
  p:=l_old;
  RETURN l_current;
END Get_Varchar2_Length;
/
-- and test:

DECLARE
  l VARCHAR2(237);
  l_n NUMBER;
BEGIN
  l_n:=Get_Varchar2_Length(l);
[Quoted]   dbms_output.put_line('ln='||l_n);
END; regards, az

<afz_at_telus.net> wrote in message news:Vbc3b.66393$K44.26257_at_edtnps84...
> 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:59:38 CEST