Re: Programatically Accessing Column Length?

From: <afz_at_telus.net>
Date: Thu, 28 Aug 2003 01:05:33 GMT
Message-ID: <xzc3b.66467$K44.42817_at_edtnps84>

sorry again -to lasy to test, but you will find out: 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;

    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_min;
  p:=l_old;
  RETURN l_current;
END Get_Varchar2_Length;
/

<afz_at_telus.net> wrote in message news:_tc3b.66450$K44.29660_at_edtnps84...
> !!! sorry
> should be :
> 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);
> 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 - 03:05:33 CEST