Convert LONG RAW to VARCHAR2 in Oracle
Here is the script to convert LONG RAW data to VARCHAR2.
DECLARE
v_text VARCHAR2 (32000) := '';
BEGIN
FOR i IN (SELECT long_raw_column FROM long_raw_table)
LOOP
v_text := UTL_RAW.CAST_TO_VARCHAR2 (i.MESSAGE);
DBMS_OUTPUT.put_line (v_text);
END LOOP;
END;
Convert BLOB to CLOB using Java
In one of my testing I need CLOB data, but it was in BLOB, so I have used Java to convert but if any used in Oracle?.
package com;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.Reader;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.util.JDBCUtil;
/**
* The class is used to convert BLOB column values to CLOB column.
*
* @author Manivel
* @see java.sql.Blob
*/
public class ConvertBlobToClob {
public static void main(String[] args) {
Statement stmt;
PreparedStatement pstmt;
ResultSet rs;
Connection conn;
int messageId;
Blob message;
try {
conn = JDBCUtil.getJDBCConnection();
stmt = conn.createStatement();
/*
* messages : message_id is clob column
*/
pstmt = conn.prepareStatement("INSERT INTO messages (message_id, message) VALUES (?,?)");
/*
* messages_blob : message is blob column
*/
rs = stmt.executeQuery("select message_id, message from messages_blob");
while (rs.next()) {
messageId = rs.getInt(1);
message = rs.getBlob(2);
int offset = -1;
int chunkSize = 1024;
long blobLength = message.length();
if (chunkSize > blobLength) {
chunkSize = (int) blobLength;
}
char buffer[] = new char[chunkSize];
StringBuilder stringBuffer = new StringBuilder();
Reader reader = new InputStreamReader(message.getBinaryStream());
while ((offset = reader.read(buffer)) != -1) {
stringBuffer.append(buffer, 0, offset);
}
pstmt.setInt(1, messageId);
pstmt.setString(2, stringBuffer.toString());
pstmt.executeUpdate();
System.out.println("Success :" + messageId);
}
} catch (SQLException e) {
System.err.println(e.getMessage());
e.printStackTrace();
} catch (IOException e) {
System.err.println(e.getMessage());
e.printStackTrace();
}
}
}
JDBCUtil.java
package com.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
*
* @author manivel
*
*/
public class JDBCUtil {
final static String jdbcDriver = "oracle.jdbc.driver.OracleDriver";
/*
private static String strHostName = "localhost";
private static String strDBSID = "orcl";
private static String strPortNo = "1521";
*/
private static String strUserName = "scott";
private static String strPassword = "tiger";
private static String tnsName = "ORCL";
private static Connection connection = null;
/**
*
* @return
*/
public static Connection getJDBCConnection() {
try {
if (connection == null || connection.isClosed()) {
/*
String strURL = (new StringBuilder())
.append("jdbc:oracle:thin:@//").append(strHostName)
.append(":").append(strPortNo).append("/")
.append(strDBSID).toString();
*/
String strURL = (new StringBuilder())
.append("jdbc:oracle:oci8:@").append(tnsName).toString();
Class.forName(jdbcDriver);
connection = DriverManager.getConnection(strURL, strUserName,
strPassword);
}
} catch (ClassNotFoundException e) {
System.err.println(e.getMessage());
e.printStackTrace();
} catch (SQLException e) {
System.err.println(e.getMessage());
e.printStackTrace();
}
return connection;
}
}
Copyright - There is no copyright on the code. You can copy, change and distribute it freely. Just mentioning this site should be fair
(C) August 2008, manivelcode
