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?

Answer 1

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

Answer 2

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