ORA-01704 error, insert string longer than 4000 chars

Question

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?

The trigger is not the solution... please show your php source code and insert... — Yahia, Sep 06 '11 at 03:25

score 0 · Answer 1 · answered Sep 06 '11 at 13:24

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); 
}

score 0 · Answer 2 · answered Sep 22 '11 at 20:30

CLOB in oracle supports 2GB of character data.

Other columns , for example varchar2 , only support 4000.

I think in your case, its not the CLOB that gets filled and has a problem, its another column that you are trying to populate that reaches the limit.

In the insert comand above you are inserting in 3 columns, and i don't think that all are CLOB.

Hope this helps, Alex

ORA-01704 error, insert string longer than 4000 chars

2 Answers2