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

2023-10-15 05:59

本文主要是介绍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;}}

 

转载于:https://www.cnblogs.com/jiafeimao-dabai/p/11556912.html

这篇关于ORA-01461: can bind a LONG value only for insert into a LONG column的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/215808

相关文章

解读@ConfigurationProperties和@value的区别

《解读@ConfigurationProperties和@value的区别》:本文主要介绍@ConfigurationProperties和@value的区别及说明,具有很好的参考价值,希望对大家... 目录1. 功能对比2. 使用场景对比@ConfigurationProperties@Value3. 核

Java枚举类实现Key-Value映射的多种实现方式

《Java枚举类实现Key-Value映射的多种实现方式》在Java开发中,枚举(Enum)是一种特殊的类,本文将详细介绍Java枚举类实现key-value映射的多种方式,有需要的小伙伴可以根据需要... 目录前言一、基础实现方式1.1 为枚举添加属性和构造方法二、http://www.cppcns.co

MySQL INSERT语句实现当记录不存在时插入的几种方法

《MySQLINSERT语句实现当记录不存在时插入的几种方法》MySQL的INSERT语句是用于向数据库表中插入新记录的关键命令,下面:本文主要介绍MySQLINSERT语句实现当记录不存在时... 目录使用 INSERT IGNORE使用 ON DUPLICATE KEY UPDATE使用 REPLACE

如何解决mysql出现Incorrect string value for column ‘表项‘ at row 1错误问题

《如何解决mysql出现Incorrectstringvalueforcolumn‘表项‘atrow1错误问题》:本文主要介绍如何解决mysql出现Incorrectstringv... 目录mysql出现Incorrect string value for column ‘表项‘ at row 1错误报错

ora-01017 ora-02063 database link,oracle11.2g通过dblink连接oracle11.2g

错误图示: 问题解决 All database links, whether public or private, need username/password of the remote/target database. Public db links are accessible by all accounts on the local database, while private

Hibernate插入数据时,报错:org.springframework.dao.DataIntegrityViolationException: could not insert: [cn.itc

在用junit测试:插入数据时,报一下错误: 错误原因: package junit;import org.junit.Test;import cn.itcast.crm.container.ServiceProvinder;import cn.itcast.crm.dao.ISysUserDao;import cn.itcast.crm.domain.SysRole;

ORA-25150:不允许对区参数执行ALTERING

在用PL/SQL工具修改表存储报错: 百度一下找到原因: 表空间使用本地管理,其中的表不能修改NEXT MAXEXTENTS和PCTINCREASE参数 使用数据自动管理的表空间,其中的表可以修改NEXT MAXEXTENTS和PCTINCREASE参数

ORA-01861:文字与格式字符串不匹配

select t.*, t.rowid from log_jk_dtl t; insert into log_jk_dtl (rq,zy,kssj,jssj,memo)  values (to_date(sysdate,'yyyy-mm-dd'),'插入供应商', to_char(sysdate,'hh24:mi:ss'),to_char(sysdate,'hh24:mi:ss'),'备注'

利用PL/SQL工具连接Oracle数据库的时候,报错:ORA-12638: 身份证明检索失败的解决办法

找到相对应的安装目录:比如:E:\oracle\product\10.2.0\client_1\NETWORK\ADMIN 在里面找到:SQLNET.AUTHENTICATION_SERVICES= (NTS) 将其更改为:SQLNET.AUTHENTICATION_SERVICES= (BEQ,NONE) 或者注释掉:#SQLNET.AUTHENTICATION_SERVICES= (N

long long,_int64使用小结

前言:   在16位环境下,int/unsigned int 占16位,long/unsigned long占32位   在32位环境下,int占32位,unsigned int占16位,long/unsigned long占32位 何时需要使用:   long 和 int 范围是[-2^31,2^31),即-2147483648~2147483647,而unsigned范围是[0,2^32),