Re: Programatically Accessing Column Length?

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 Wed Aug 27 2003 - 17:14:42 CEST