超过 4000 个字符在 oracle 上给出字符串文字太长错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13948613/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
More than 4000 chars gives string literal too long error on oracle
提问by hsuk
I am currently using oci8 driver on Codeigniter. While updating a field that will have more than 4000 chars, I was given a error :
我目前在 Codeigniter 上使用 oci8 驱动程序。在更新一个超过 4000 个字符的字段时，我收到了一个错误：
ORA-01704: string literal too long
So, going through few blogs, I got this:
所以，通过几个博客，我得到了这个：
declare
vClobVal varchar2(32767) := 'long text'
begin
update FMS_K_OFFICEWISE_LETTER set FKOL_LETTER_BODY=vClobVal
where FKOL_OFFICEWISE_LETTER_ID=240;
end;
This worked for me when fired at Toad. Now, I created a stored procedure and compiled as :
当我向蟾蜍开火时，这对我有用。现在，我创建了一个存储过程并编译为：
CREATE OR REPLACE PROCEDURE FMIS3.UPDATE_LETTER_BODY ( body_text IN FMS_K_OFFICEWISE_LETTER.FKOL_LETTER_BODY%type,condition_id in FMS_K_OFFICEWISE_LETTER.FKOL_OFFICEWISE_LETTER_ID%type)IS
begin
update FMS_K_OFFICEWISE_LETTER set FKOL_LETTER_BODY=body_text
end;
and this does not work for more than 4000 chars again. Can't I define the size of varchar2 as it gave me error. Any suggestions ?
这对超过 4000 个字符再次不起作用。我不能定义 varchar2 的大小，因为它给了我错误。有什么建议 ？
Even tried using PDO by binding parameters, works only when the string is of size less than 4000 chars :(
即使尝试通过绑定参数使用 PDO，也仅当字符串的大小小于 4000 个字符时才有效:(
$conn = new PDO("oci:dbname=".$this->db->hostname,$this->db->username,$this->db->password);
$params = array(
':body_text' => "Long String"
);
$sth = $conn->prepare("update FMS_K_OFFICEWISE_LETTER set FKOL_LETTER_BODY = :body_text
where FKOL_OFFICEWISE_LETTER_ID=241");
$sth->execute($params) or die('error occured');
回答by user1917764
Check this out :
看一下这个 ：
declare
vClobVal varchar2(32767) := 'long text'
begin
update FMS_K_OFFICEWISE_LETTER set FKOL_LETTER_BODY=vClobVal
where FKOL_OFFICEWISE_LETTER_ID=240;
end;
Are you sure this is not supported ?
你确定这不受支持吗？
回答by wolφi
In PL/SQL, a VARCHAR2can have 32767 bytes, but in SQL only 4000 bytes. Therefore the BEGIN... END;block worked, as it is PL/SQL, and the procedure didn't, as it is SQL.
在 PL/SQL 中，aVARCHAR2可以有 32767 个字节，但在 SQL 中只有 4000 个字节。因此BEGIN...END;块起作用了，因为它是 PL/SQL，而过程没有起作用，因为它是 SQL。
