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

[santosh kaushik <santoshkaushik@xxxxxxxxx>]

You have to insert a CLOB in three steps :

 

First you have to insert a empty clob and then write your actual data.

1) Insert a empty clob using CLOB.empty_lob() method.

2) Then do a select on this column and get the CLOB field, then get a character output stream using getCharacterOutputStream. This returns you a java.io.Writer.

3) Now write your char[] or string to the writer.

 Note: A) You have to set the con.setAutoCommit(false) before the transaction starts

          B) After writing to the Writer do a commit on the connection.

This should surely work.

 

 

 

Creighton Kirkendall <ckirkendall@xxxxxxxxxxxxxx> wrote:

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.

---

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