Oracle LONG RAW

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