关于clob的读取 在一个表中,有一个字段是 clob 类型,现在我要用一个sql语句查询并显示到页面中,该如何写。使用select dbms_lob.substr(a.desc, 2000,1) as desc from table a 只能查询截取4000字符的内容,要查询出全部内容怎么办呢?最好能用一个sql写出来谢谢各位了。 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 你用的是JAVA语言吗?如果是JAVA,可以试试以下解决方法可以Statement stmt = null;ResultSet rs = null;List rList = new ArrayList();stmt = con.createStatement();rs = stmt.executeQuery(query.toString());ResultSetMetaData rsmd = rs.getMetaData();int numberOfColumns = rsmd.getColumnCount();String columnLabel = "";while(rs.next()){ rMap = new MapBEAN(); for(int i=1 ; i <= numberOfColumns ; i++){ columnLabel = rsmd.getColumnLabel(i); if(columnLabel.equals("desc")){ oracle.sql.CLOB clob=(CLOB) rs.getClob(i); if(clob != null && (int)clob.length()>0) rMap.set(columnLabel, clob.getSubString((long)1, (int)clob.length())); else rMap.set(columnLabel,""); }else{ rMap.set(columnLabel, rs.getString(i)); } } rList.add(rMap);} return rList; OracleConnection myCn = new OracleConnection(strConn); OracleCommand myCmd = new OracleCommand(); try { myCn.Open(); } catch(System.Data.OracleClient.OracleException e) { throw new Exception(e.Message); } OracleTransaction myTrans = myCn.BeginTransaction(); try { myCmd.Connection = myCn; myCmd.Transaction = myTrans; myCmd.CommandText = "SELECT * FROM "+ table_name +" where "+ table_id +" = "+ id +" FOR UPDATE"; OracleDataReader reader = myCmd.ExecuteReader(); using(reader) { reader.Read(); OracleLob CLOB = reader.GetOracleLob(column); int[] content = new int[(int)CLOB.Length]; byte[] buffer = new byte[(int)CLOB.Length]; for(int i = 0;i<(int)CLOB.Length;i++) { content[i] = CLOB.ReadByte(); buffer[i] = (byte)content[i]; } Encoding utf8 = Encoding.UTF8; string final = utf8.GetString(buffer); myTrans.Commit(); return final; } } catch(System.Data.OracleClient.OracleException e) { myTrans.Rollback(); throw new Exception(e.Message); } finally { myCmd.Dispose(); myCn.Close(); } 菜鸟求解如下语句的意思 关于oracle数据问题? asp.net查询远程ORACLE中文字符乱码 有没有用jdeveloper11的大侠,里面的treetable的选中问题! 怎样在存储过程中返回多个“临时”的记录集? 貌似简单的SQL查询,急切等待解决!!! 请教对于NUMBER类型的字段怎样才可以不使用科学计数法存放大于15位的整数? 安装问题,在线等待 在VB中,如何对ORACLE中的BLOB字段进行存取操作 Oracle 11G alert_orcl.log中的有以下报警信息 有啥影响? 如何解决? 统计表数量,SQL 怎么写呢? 关于clob的读取
如果是JAVA,可以试试以下解决方法可以
Statement stmt = null;
ResultSet rs = null;
List rList = new ArrayList();stmt = con.createStatement();
rs = stmt.executeQuery(query.toString());
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
String columnLabel = "";
while(rs.next()){
rMap = new MapBEAN();
for(int i=1 ; i <= numberOfColumns ; i++){
columnLabel = rsmd.getColumnLabel(i);
if(columnLabel.equals("desc")){
oracle.sql.CLOB clob=(CLOB) rs.getClob(i);
if(clob != null && (int)clob.length()>0)
rMap.set(columnLabel, clob.getSubString((long)1, (int)clob.length()));
else
rMap.set(columnLabel,"");
}else{
rMap.set(columnLabel, rs.getString(i));
}
}
rList.add(rMap);
}
return rList;
OracleConnection myCn = new OracleConnection(strConn);
OracleCommand myCmd = new OracleCommand();
try
{
myCn.Open();
}
catch(System.Data.OracleClient.OracleException e)
{
throw new Exception(e.Message);
}
OracleTransaction myTrans = myCn.BeginTransaction();
try
{
myCmd.Connection = myCn;
myCmd.Transaction = myTrans;
myCmd.CommandText = "SELECT * FROM "+ table_name +" where "+ table_id +" = "+ id +" FOR UPDATE";
OracleDataReader reader = myCmd.ExecuteReader();
using(reader)
{
reader.Read();
OracleLob CLOB = reader.GetOracleLob(column);
int[] content = new int[(int)CLOB.Length];
byte[] buffer = new byte[(int)CLOB.Length];
for(int i = 0;i<(int)CLOB.Length;i++)
{
content[i] = CLOB.ReadByte();
buffer[i] = (byte)content[i];
}
Encoding utf8 = Encoding.UTF8;
string final = utf8.GetString(buffer);
myTrans.Commit();
return final;
}
}
catch(System.Data.OracleClient.OracleException e)
{
myTrans.Rollback();
throw new Exception(e.Message);
}
finally
{
myCmd.Dispose();
myCn.Close();
}