writing content of CLOB to local file

[Toe]

hi,

i'm trying to copy the content of a clob to a *local* file.

I can do the getting the text out of the clob and writing to the screen (then using a spool file to write to a file).........providing the clob is small enough.

of course, if the clob is too large then it blows the buffer.

I tried using UTL_FILE to write directly to file but, as far as i can tell, that can only be used to spool a file to the server.

so... short of writing a java program.... can anyone suggest how to do this?

is is possible to send the output to the file without sendign it to the screen for example?

 

[hoinz]

Not sure if this will help, but did you try to redirect screen output to a file?
i.e. call sqlplus like this:
sqlplus user/password@SID >C:\temp\output.txt

 

[vandana2509]

+=========================================================================+
| FILENAME |
| BlobToFile.java |
| DESCRIPTION |
| Class that is used to write the content from blob to file. |
| |
| HISTORY |
| 25-July-07 Vandana Gangisetty Created. |
+========================================================================*/
package project1;


import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.Connection;

public class BlobToFile
{
public BlobToFile() {
}

//public String startService(String userName,String password,String service,String fileId)
public static void write(String destDirName,int fileId)
{
int length=-1;
InputStream is=null;
String fileName="";
String fileExt="";
ResultSet rs=null;
Blob orBLOB=null;
//int fileId=123448;
fileId=123448;
byte[] buffer=null;
int blobLen=0;

try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection connection =DriverManager.getConnection("jdbcracle:thin192.168.137.40:1523:bjtest","apps","apps");// dataSource.getConnection();

/*InitialContext initContext = new InitialContext();
DataSource dataSource = (DataSource)initContext.lookup("jdbc/ApexDbDS"); //jdbc/ApexDbDS"
Connection connection=dataSource.getConnection();*/

System.out.println("created connection");
Statement stmt=connection.createStatement();
rs=stmt.executeQuery("select file_name,file_data from fnd_lobs where file_id="+fileId);//file_name='ItemPriceVand.zip'");
System.out.println("executed resultset");

if(rs.next())
{
System.out.println("entered into result set");
orBLOB = rs.getBlob("file_data");
fileName=rs.getString("file_name");

if(orBLOB!=null)
{
blobLen=Integer.parseInt(String.valueOf(orBLOB.length()));
buffer=new byte[blobLen];
System.out.println("blob length:"+blobLen);
}

/*int dotIndex=fileName.indexOf(".");
if(dotIndex!=-1)
{
fileExt=fileName.substring(dotIndex+1);
fileExt=fileExt.toUpperCase();
}*/

System.out.println("InitiateService class fileName:"+fileName+"\tfileExt:"+fileExt+"\tfileId:"+fileId);
is =orBLOB.getBinaryStream();
length=-1;
is =orBLOB.getBinaryStream();

while((length=is.read(buffer)) != -1)
{
}
}//end of while resultset********************


System.out.println("wrote file into blob");

System.out.println("Writing byte array into a file");
//FileOutputStream fout=new FileOutputStream("D:\\testFileApr1666.pdf");
FileOutputStream fout=new FileOutputStream(destDirName);

fout.write(buffer);
fout.close();
rs.close();
stmt.close();
connection.close();
}
catch(Exception exception)
{
System.out.println(exception.toString());
exception.printStackTrace();
}
//return response;
}//end of method
public static void main(String args[])
{
//BlobToFile blobToFile=new BlobToFile();
BlobToFile.write("D:\\testFileApr1666.pdf",123448);
}
}//end of the class