Re: 10046/10079 Tracing understanding - SOLVED
- From: Jared Still <jkstill@xxxxxxxxx>
- To: brian_wisniewski@xxxxxxxxx
- Date: Thu, 4 Aug 2005 09:49:55 -0700
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@xxxxxxxxx> 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
Other related posts:
