Re: 10046/10079 Tracing understanding - SOLVED

From: Brian Wisniewski <brian_wisniewski_at_yahoo.com>
Date: Thu, 4 Aug 2005 10:12:34 -0700 (PDT)
Message-ID: <20050804171234.47710.qmail@web32215.mail.mud.yahoo.com>

It's all about the OUT variables and the way they're defined. I've included my test case in case you want to test it yourself.  

Here's the pkg I built (field names were from the developer pkg that I tore down to the basics - I particurally like ...mystery_field but I digress).  

create or replace package test_pkg_bsw is

        procedure test_proc 
                (o_spapi_mystery_field OUT pls_integer,
                CrspndMsgId IN VARCHAR2 DEFAULT ' ', 
                MsgIDExists OUT CHAR

);
end test_pkg_bsw;
/

create or replace package body test_pkg_bsw is

        procedure test_proc
        (
                o_spapi_mystery_field OUT pls_integer,
                CrspndMsgId IN VARCHAR2 DEFAULT ' ',
                MsgIDExists OUT CHAR
        ) IS
                -- local vars
                m_CrspndMsgIdCnt VARCHAR2(26);
                tempString VARCHAR2(100);
                BEGIN
                                o_spapi_mystery_field :=1;
                                MsgIDExists := 'TEST FIELD';
                END test_proc;

end test_pkg_bsw;
/

Here's the java program I built to call the pkg and return the length of the return variable it is retrieving:  

import java.sql.*;

import oracle.jdbc.*;
import oracle.sql.*;
import oracle.jdbc.pool.OracleDataSource;

class bsw
{
public static void main (String args[])
throws SQLException
{
OracleDataSource ods = new OracleDataSource(); ods.setURL("jdbc:oracle:thin:USERNAME/PASSWORD@(description=(address_list=(address=(protocol=tcp)(host=HOSTNAME)(port=PORT)))(connect_data=(server=dedicated)(service_name=SERVICE_NAME)))"); Connection conn = ods.getConnection();
// Create Oracle DatabaseMetaData object
DatabaseMetaData meta = conn.getMetaData();
// gets driver info:

System.out.println("JDBC driver version is " + meta.getDriverVersion());
// execute stored procedure

CallableStatement stmt = conn.prepareCall ("begin test_pkg_bsw.test_proc(?,?,?); end;");

stmt.setString(2,"9999-08-02-17.34.35.712005");
stmt.registerOutParameter(1,Types.NUMERIC);
stmt.registerOutParameter(3,Types.VARCHAR);
stmt.executeUpdate();

String test= new String(stmt.getString (3));

System.out.println ("Return length is: " + test.length());
System.out.println ("Trimmed string is: " + test.trim());
System.out.println ("Trimmed string length is: " + (test.trim()).length());

//close the result set, statement, and the connection
stmt.close();
conn.close();
}
}

And the results of the java call when the OUT in the pkg is CHAR:  

JDBC driver version is 10.1.0.3.0
Return length is: 32512
Trimmed string is: TEST FIELD
Trimmed string length is: 10  

and when the OUT is VARCHAR2:  

JDBC driver version is 10.1.0.3.0
Return length is: 10
Trimmed string is: TEST FIELD
Trimmed string length is: 10

• Brian

Jared Still <jkstill_at_gmail.com> wrote:
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


		
---------------------------------
 Start your day with Yahoo! - make it your home page 
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Aug 04 2005 - 12:14:44 CDT