| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: 10046/10079 Tracing understanding - SOLVED
I tried the OCI driver and also changed the types and it still returns all 32K, with associated more data waits when the OUT is a CHAR field.
DriverManager.registerDriver (new oracle.jdbc.OracleDriver()); Connection conn = DriverManager.getConnection
("jdbc:oracle:oci:USERNAME/PASSWORD_at_tmpltca1");
//stmt.registerOutParameter(1,Types.NUMERIC);
//stmt.registerOutParameter(3,Types.VARCHAR);
stmt.registerOutParameter(1,OracleTypes.NUMBER);
stmt.registerOutParameter(3,OracleTypes.VARCHAR);
JDBC driver version is 10.1.0.3.0
Return length is: 32512
Trimmed string is: TEST FIELD
Trimmed string length is: 10
However, I ran the tests again from sqlplus calling PL/SQL - and it is passing the entire CHAR out variable back to the calling program, but it is NOT causing the "..more data to client" waits - that I see when this is called from Java. I've traced these repeatedly using the 10046 & 10079 together for every connection and I always have seen the waits from Java but not once from Sql*Plus.
cat a.a
set serveroutput on;
declare
x pls_integer;
y varchar2(20):='TEST';
z varchar2(32767);
rtrn pls_integer;
begin
test_pkg_bsw.test_proc(x,y,z);
dbms_output.enable(1000000);
dbms_output.put_line(length(z));
end;
When the OUT is CHAR in the pkg the length is 32767, when it is VARCHAR2 it is 10.
Jared Still <jkstill_at_gmail.com> wrote:
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));
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.
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.
-- Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist -- Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist --------------------------------- Start your day with Yahoo! - make it your home page -- http://www.freelists.org/webpage/oracle-lReceived on Thu Aug 04 2005 - 13:42:31 CDT
 |
 |