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

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