I think the snippit of code I sent may have been a bit confusing. The
java snippit is from the java stored procedure and the PLSQL code is how
you create PLSQL stub for it. I understand how to get a CLOB from the
PLSQL statement but I not even getting that far. When I try to call the
function from the PLSQL promt I get the error.
Creighton
On Fri, 2005-09-23 at 15:16, Prakash, Anu wrote:
> 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.
|