ORA-01704错误，插入长度超过4000个字符的字符串

发布于 2024-12-03 05:46:08 字数 443 浏览 1 评论 0原文

我正在使用 PHP 将数据插入 Oracle 数据库。有一个数据类型为 CLOB 的表字段，但它允许插入最多 4000 个字符。我在 Google 上搜索了一下，发现 PL/SQL 可以向 CLOB 字段插入超过 4000 个字符。然后我打算使用Oracle Trigger来解决我的问题。我的计划是用 PLSQL 插入替换 PHP 插入查询。

create or replace
TRIGGER EXTEND_CLOB 
BEFORE INSERT ON T_SESSIONS
for each row
BEGIN
  insert into t_sessions (id,data,expires) values ( :new.id, :new.data, :new.expires );
END;

这个触发器可以工作，但它会插入2条记录（触发一次，PHP一次）。有没有办法忽略 PHP 的查询插入？

原文

I am using PHP to insert data to Oracle db. There is a table field with datatype CLOB but it allows to insert upto 4000 characters. I did a bit of searching on Google and found that PL/SQL can insert more than 4000 chars to CLOB field. Then I plan to use Oracle Trigger to solve my problem. My plan is replace the PHP insert query with PLSQL insert.

create or replace
TRIGGER EXTEND_CLOB 
BEFORE INSERT ON T_SESSIONS
for each row
BEGIN
  insert into t_sessions (id,data,expires) values ( :new.id, :new.data, :new.expires );
END;

This trigger can work but it will insert 2 records (trigger once, PHP once). Is there anyway to ignore the query insert by PHP?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

岛徒 2024-12-10 05:46:08

要插入一个大的 clob，一般需要执行两个步骤。

进行插入操作，将空 clob 插入表中，并将 clob 描述符返回到代码，然后写入该描述符。

我不是 PHP 编码员，但我以前在其他语言中使用过这种模式。

我做了一些搜索，你需要的代码大致类似于我下面给出的代码（我没有对此进行测试，因为我没有 PHP 环境设置）。

$conn = OCILogon('myusername', 'mypassword', 'mydatabase'); 


// Assumes a file has been uploaded you want to insert into a CLOB column

$lob = OCINewDescriptor($conn, OCI_D_LOB); 
$stmt = OCIParse($conn, 'INSERT INTO MYCLOBTAB (C1, C2) VALUES('.$myid . ', EMPTY_CLOB()) RETURNING C2 INTO :C2'); 
OCIBindByName($stmt, ':C2', &$lob, -1, OCI_B_CLOB); 
OCIExecute($stmt, OCI_DEFAULT); 

// The function $lob->savefile(...) reads from the uploaded file. 
// If the data was already in a PHP variable $myv, the 
// $lob->save($myv) function could be used instead. 
if ($lob->savefile($_FILES['lob_upload']['tmp_name'])) { 
  OCICommit($conn); 
}

To insert a large clob, you need to do two steps in general.

Have an insert that inserts an empty clob into the table, and returns the clob descriptor to your code, and then write to that descriptor.

I am not a PHP coder, but I have used this pattern before in other languages.

I did some searches, and the code you need is roughly like what I have given below (I have not tested this, as I don't have a PHP environment setup).

$conn = OCILogon('myusername', 'mypassword', 'mydatabase'); 


// Assumes a file has been uploaded you want to insert into a CLOB column

$lob = OCINewDescriptor($conn, OCI_D_LOB); 
$stmt = OCIParse($conn, 'INSERT INTO MYCLOBTAB (C1, C2) VALUES('.$myid . ', EMPTY_CLOB()) RETURNING C2 INTO :C2'); 
OCIBindByName($stmt, ':C2', &$lob, -1, OCI_B_CLOB); 
OCIExecute($stmt, OCI_DEFAULT); 

// The function $lob->savefile(...) reads from the uploaded file. 
// If the data was already in a PHP variable $myv, the 
// $lob->save($myv) function could be used instead. 
if ($lob->savefile($_FILES['lob_upload']['tmp_name'])) { 
  OCICommit($conn); 
}

回复收藏 0 原文