Re: 10046/10079 Tracing understanding - SOLVED

From: Jared Still <jkstill_at_gmail.com>
Date: Thu, 4 Aug 2005 10:38:31 -0700
Message-ID: <bf463805080410381e33f5a@mail.gmail.com>

Yes, but even so, PL/SQL does not pad the CHAR input parameter out to 32k. It will be the length of the string without padding.

Same with CHAR return value from a function.

IN OUT/OUT CHAR variables are padded to the length defined by the caller.

As Brian mentioned, possibly a driver problem.

Jared

create or replace function mychar(
p_in char
, p2_in in out char
, p3_in out char
) return char
is
begin
dbms_output.put_line('p_in:' || length(p_in)); p2_in := 'this is a test';
p3_in := 'also a test';
return 'testing';
end;
/

declare
v1 char(20);
v2 char(20);
begin

dbms_output.put_line(length(mychar('this is a test',v1,v2)));
dbms_output.put_line(length(v1));
dbms_output.put_line(length(v2));

end;
/

On 8/4/05, Powell, Mark D <mark.powell_at_eds.com> wrote:
>
> Jared, think parameter list ( p_in varchar2, p_in2 char)
> The lengths are undefined.
> -- Mark D Powell --
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Jared Still
> *Sent:* Thursday, August 04, 2005 12:50 PM
> *To:* brian_wisniewski_at_yahoo.com
> *Cc:* Oracle-L_at_freelists.org
> *Subject:* Re: 10046/10079 Tracing understanding - SOLVED
>
> Nice piece of work Brian, congratulations.
>
> Can you explain a bit more about the 'fixed length' of a char?
>
> CHAR in PL/SQL defaults to 1 character.
> eg.
>
> declare
> x char;
> begin
> x := 'AB';
> dbms_output.put_line(length(x));
> end;
> /
>
> This will fail with
> ORA-06502: PL/SQL: numeric or value error: character string buffer too
> small
> ORA-06512: at line 4
>
> If it is declared like this then I understand the problem:
>
> declare
> x char(32767);
> begin
> x := 'AB';
> dbms_output.put_line(length(x));
> end;
> /
>
> Thanks,
>
> Jared
>
>
> On 8/4/05, Brian Wisniewski <brian_wisniewski_at_yahoo.com> wrote:
> >
> > I finally figured out the problem with the SQL*Net more data to client
> > problem. The developer defined output variables as CHAR since he was only
> > passing back a single character.
> > Well the max size of a CHAR field in a procedure is 32K and it's fixed
> > length so it was returning the value back to the calling program along with
> > another 32000+ spaces to fill it out to the max possible size. And he was
> > doing this with 10 fields so that's a mere 320K of spaces sent back to the
> > java pgm each and every time this pkg was called! Hence the need for Oracle
> > to break that down into manageable pieces to send across the network.
> > A quick change to VARCHAR2 fixed the issue.
> > Initial testing showed this to only be an issue when the package was
> > called by java - I didn't see this ...more data.. when I called it via
> > sqlplus from the same client.
> > - Brian
> >
> > **
> >
>
>
> --
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
>
>

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Aug 04 2005 - 12:42:01 CDT