RE: 10046/10079 Tracing understanding - SOLVED
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: <Oracle-L@xxxxxxxxxxxxx>
- Date: Thu, 4 Aug 2005 13:18:32 -0400
Jared, think parameter list ( p_in varchar2, p_in2 char)
The lengths are undefined.
-- Mark D Powell --
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jared Still
Sent: Thursday, August 04, 2005 12:50 PM
To: brian_wisniewski@xxxxxxxxx
Cc: Oracle-L@xxxxxxxxxxxxx
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@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:
