CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsqlpdo
hsuk
hsuk

Reputation: 6860

More than 4000 chars gives string literal too long error on oracle

I am currently using oci8 driver on Codeigniter. While updating a field that will have more than 4000 chars, I was given a error :

 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 ?

Even tried using PDO by binding parameters, works only when the string is of size less than 4000 chars :(

    $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');

Upvotes: 1

Views: 18359

Answers (3)

user1917764
user1917764

Reputation: 131

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 ?

Upvotes: 8

wolφi
wolφi

Reputation: 8361

In PL/SQL, a VARCHAR2 can 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.

Upvotes: 5

maialithar
maialithar

Reputation: 3123

varchar2 has a limit of 4000 chars. Use CLOB instead.

Upvotes: 4

Related Questions