ORA-01461: can bind a LONG value only for insert into a LONG column

本文主要是介绍ORA-01461: can bind a LONG value only for insert into a LONG column，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

oracle clob字段在用常规新增或修改数据库时如果字符超过4000(一个中文两个字符) 就会报这个错,解决办法是采用预处理的方式,单独对clob类型字段进行预处理存储,如下

调用方式:

$result = doClob( 'table_name',$clob_data,'update',"id=123");

封装公共函数:

function doClob( $table, $data, $type = 'update', $wherestr = null ,$seqname = null){import('Org.Util.Ociclob');$clog_obj= new \Think\Ociclob($table, $data);$clog_obj->table = $table;$clog_obj->data= $data;if( strtolower( $type ) == 'update'){$clog_obj->where= $wherestr;$flag = $clog_obj->update();}else if(strtolower( $type ) == 'insert' ){$clog_obj->seqname= strtoupper($seqname);$flag = $clog_obj->insert();}return $flag;
}

引入类的方式:

Ociclob类代码

<?php
namespace Think;
class Ociclob{public $conn = false ;public $table = '' ;public $seqname = '' ;public $where = '' ;public $lob = '' ;public $data = '' ;function  __construct(){$this->conn = $this->db_connect();if(!empty($table)) $this->table=$table;if(!empty($table)&&!empty($data)){$this->checkfield($table,$data);}}//检测字段属性public function checkfield($table,$data){if(!empty($table)&&!empty($data)){$fields=$this->getFields($table);foreach ($data as $key=>$value){//检测lob字段if(strtolower($fields[strtolower($key)]['type'])=='clob')  $lob[]=$key;//检测PK字段并获取SEQif(strtolower($fields[strtolower($key)]['primary'])==1){$this->seqname=$value;$this->data[$key]=$this->getseq();//根据自动填充主键值$pk=$key;//主键被设置标志
                }}$this->lob=$lob;//如果没有在DATA中的设置主键值，则根据SEQNAME自动填充if(!isset($pk)&&!empty($this->seqname)) {$this->data[$fields['pk']]=$this->getseq();}unset($lob);unset($pk);}}/**+----------------------------------------------------------* 连接ORACLE+----------------------------------------------------------*/public function db_connect(){$db_pwd = C('ENCRYPT_KEY')?_decrypt(C('DB_PWD'), C('ENCRYPT_KEY')):C('DB_PWD');$this->conn = oci_connect(C('DB_USER'), $db_pwd, C('DB_HOST').':'.C('DB_PORT').'/'.C('DB_NAME'), C('DB_CHARSET'));return $this->conn;}/**+----------------------------------------------------------* 设置ORACLE字符集+----------------------------------------------------------*/public function charset($code='UTF8'){$sql="ALTER DATABASE CHARACTER SET $code";$stmt = oci_parse($this->conn, $sql);oci_execute($stmt);oci_commit($this->conn);// Free resourcesoci_free_statement($stmt);}/**+----------------------------------------------------------* 添加包含有CLOB字段的记录+----------------------------------------------------------*/public function insert (){//检测字段属性if(empty($this->lob)) $this->checkfield($this->table,$this->data);//字段整理$f=strtoupper(join(',',array_keys($this->data)));//数据整理foreach ($this->data as $key=>$val){$f_v_arr[]=!in_array($key,$this->lob)?"'".$val."'":"EMPTY_CLOB()";}$f_v=join(',',$f_v_arr);//lob字段清理并赋值LOB数据到绑定变量for ($i=0;$i<count($this->lob);$i++){$lob_str.=":".$this->lob[$i]."_loc,";}$returning_str.="  RETURNING ".join(',',$this->lob)." INTO ".rtrim($lob_str,',');//组装SQL$sql = "INSERT INTO  $this->table ($f) VALUES(".$f_v.")".$returning_str ;$stmt = oci_parse($this->conn, $sql);for ($i=0;$i<count($this->lob);$i++){// 创建一个“空”的OCI LOB对象绑定到定位器$$this->lob[$i] = oci_new_descriptor($this->conn, OCI_D_LOB);$lob_str=":".$this->lob[$i]."_loc";// 将Oracle LOB定位器绑定到PHP LOB对象oci_bind_by_name($stmt, $lob_str, $$this->lob[$i], -1, OCI_B_CLOB);}// 执行该语句的使用，oci_default -作为一个事务oci_execute($stmt, OCI_DEFAULT)    or die ("Unable to execute query\n");// 保存LOB对象数据for ($i=0;$i<count($this->lob);$i++){if(!$$this->lob[$i]->save($this->data[$this->lob[$i]])){$result=false;break;}}if ( isset($result)&&$result==false ) {// 如果错误，则回滚事务oci_rollback($this->conn);$ret=false;} else {// 如果成功，则提交oci_commit($this->conn);$ret=true;}// 释放资源oci_free_statement($stmt);for ($i=0;$i<count($this->lob);$i++){$$this->lob[$i]->free();}return $ret;}/**+----------------------------------------------------------* 更新CLOB字段的内容+----------------------------------------------------------*/public function update(){//检测字段属性if(empty($this->lob)) $this->checkfield($this->table,$this->data);//数据整理foreach ($this->data as $key=>$val){$set_arr[]=!in_array($key,$this->lob)?strtoupper($key)."='".$val."'":$key."=EMPTY_CLOB()";}$set_str=join(',',$set_arr);//lob字段清理并赋值LOB数据到绑定变量for ($i=0;$i<count($this->lob);$i++){$lob_str.=":".$this->lob[$i]."_loc,";}$returning_str.=" RETURNING ".join(',',$this->lob)." INTO ".rtrim($lob_str,',');$where_str=strtoupper($this->where);//组装SQL$sql = "UPDATE $this->table SET $set_str WHERE $where_str ".$returning_str;$stmt = oci_parse($this->conn, $sql);for ($i=0;$i<count($this->lob);$i++){// 创建一个“空”的OCI LOB对象绑定到定位器$$this->lob[$i] = oci_new_descriptor($this->conn, OCI_D_LOB);$lob_str=":".$this->lob[$i]."_loc";// 将Oracle LOB定位器绑定到PHP LOB对象oci_bind_by_name($stmt, $lob_str, $$this->lob[$i], -1, OCI_B_CLOB);}// 执行该语句的使用，oci_default -作为一个事务oci_execute($stmt, OCI_DEFAULT)  or die("Unable to execute query\n");// 保存LOB对象数据for ($i=0;$i<count($this->lob);$i++){if(!$$this->lob[$i]->save($this->data[$this->lob[$i]])){$result=false;break;}}if ( isset($result)&&$result==false ) {OCIRollback($this->conn);$ret=false;}else $ret=true;// 提交事务oci_commit($this->conn);//释放资源for ($i=0;$i<count($this->lob);$i++){$$this->lob[$i]->free();}OCIFreeStatement($stmt);return $ret;}public function getseq(){$sql="select $this->seqname.nextval from dual";$stmt = oci_parse($this->conn, strtoupper($sql));oci_execute($stmt);while ( $row = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_LOBS) ) {$data[]=$row;}// 释放资源oci_free_statement($stmt);return $data['0']['NEXTVAL'];}/**+----------------------------------------------------------* 查询包含有CLOB字段的记录+----------------------------------------------------------*/public function select ($sql=''){$sql = empty($sql)?"SELECT * FROM  $this->table  WHERE $this->where ":$sql;$stmt = oci_parse($this->conn, strtoupper($sql));oci_execute($stmt);while ( $row = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_LOBS) ) {$data[]=$row;}// 释放资源oci_free_statement($stmt);return $data;}/**+----------------------------------------------------------* 查询包含有CLOB字段的记录+----------------------------------------------------------*/public function row ($sql=''){$sql = empty($sql)?"SELECT * FROM  $this->table  WHERE $this->where ":$sql;$stmt = oci_parse($this->conn, strtoupper($sql));oci_execute($stmt);$row = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_LOBS);// 释放资源oci_free_statement($stmt);return $row;}/*** 取得数据表的字段信息* @access public*/public function getFields($tableName) {$sql="select a.column_name,data_type,decode(nullable,'Y',0,1) notnull,data_default,decode(a.column_name,b.column_name,1,0) pk "."from user_tab_columns a,(select column_name from user_constraints c,user_cons_columns col "."where c.constraint_name=col.constraint_name and c.constraint_type='P'and c.table_name='".strtoupper($tableName)."') b where table_name='".strtoupper($tableName)."' and a.column_name=b.column_name(+)";$result=  $this->select($sql);$info   =   array();if($result) {foreach ($result as $key => $val) {$info[strtolower($val['COLUMN_NAME'])] = array('name'    => strtolower($val['COLUMN_NAME']),'type'    => strtolower($val['DATA_TYPE']),'notnull' => $val['NOTNULL'],'default' => $val['DATA_DEFAULT'],'primary' => $val['PK'],'autoinc' => $val['PK'],);if($val['PK']==1) $info['pk']=$val['COLUMN_NAME'];}}return $info;}}