RE: [cinjug-users] Returning 4K+ from a Java Stored Procedure

["Prakash, Anu" <APrakash@xxxxxxx>]

You can call your stored procedure this way...

 

CallableStatement cstmt = conn.prepareCall( "{ ? = call your_function (?) }"  ); 
 cstmt.registerOutParameter(1, Types.CLOB); 
 cstmt.setString(2, param1);

    ......
 cstmt.execute();

    ......

 cstmt.getClob(1).getAsciiStream(); // This will give the input stream to read your data out.

 

 

 

Anu

 

-----Original Message-----
From: Creighton Kirkendall [mailto:ckirkendall@xxxxxxxxxxxxxx]
Sent: Friday, September 23, 2005 2:40 PM
To: Abdul Habra
Cc: users@xxxxxxxxxx
Subject: Re: [cinjug-users] Returning 4K+ from a Java Stored Procedure

Yes, I tried that but I still get the same error.  I not sure where to go from here.

Here is the code in question:

JAVA:
public static CLOB getSqlStats(String SQL, String columns){
Statement st=null;
ResultSet rs=null;
try {
columns=columns.toUpperCase();
         Connection conn = DriverManager.getConnection("jdbc:default:connection:");
         st=conn.createStatement();
         rs=st.executeQuery(SQL);
         String xml=columnCountStats(rs, split(columns,","));
         rs.close();
         st.close();
         rs=null;
         st=null;
         CLOB lob=new CLOB((OracleConnection)conn, xml.getBytes());
         return lob;
}catch(Exception e){
e.printStackTrace();
return null;
}finally{
if(rs!=null){ try{rs.close();}catch(Exception s){}}
if(st!=null){ try{st.close();}catch(Exception s){}}
}
}

PLSQL:

CREATE OR REPLACE
FUNCTION HOBSQLSTATS (SQL1 VARCHAR2, COLUMN1 VARCHAR2) RETURN CLOB
AUTHID CURRENT_USER
AS LANGUAGE JAVA
NAME 'com.hobsons.reporting.util.ResultSetStats.getSqlStats(java.lang.String,
java.lang.String) return oracle.sql.CLOB';


Creighton

On Fri, 2005-09-23 at 12:13, Abdul Habra wrote:

Did you consider using a CLOB type?

Creighton Kirkendall <ckirkendall@xxxxxxxxxxxxxx> wrote:

I am trying to return an xml document from a java stored procedure.
This document is over the 4K limit for VARCHAR2 (my original return
type) so I am trying to figure out how to return this object. Right now
it seems not matter what data type I choose I get the following error.

ORA-24345: A Truncation or null fetch error occurred

Does anyone out there have experience with returning more than 4K from a
java stored procedure.


Creighton




---------
You may unsubscribe from this mailing list
by sending a blank email addressed to:
users-unsubscribe@xxxxxxxxxx

--
Find additional help by sending a blank email
addressed to:
users-help@xxxxxxxxxx

---

Yahoo! for Good
Click here to donate to the Hurricane Katrina relief effort.