(Oracle8i): How to re-use a PHP-allocated CLOB in PL/SQL

From: Date: Thu, 11 Oct 2001 08:26:14 +0000
Subject: (Oracle8i): How to re-use a PHP-allocated CLOB in PL/SQL
Groups: php.db 
Request: Send a blank email to php-db+get-13236@lists.php.net to get a copy of this message
Hi all, This is a tricky one: it has to do with the way PHP uses CLOB fields. I want to pass a CLOB to a stored procedure, insert it into a row, and finally, inside the PL/SQL code of the procedure, if a condition is met, insert the same data again in another table. It's something like this: - Allocate a new descriptor. - Bind it to the statement. - Execute de procedure. - Within the procedure: PROCEDURE MY_PROC(VID IN MY_TABLE.ID%TYPE, VCLOBFIELD IN OUT MY_TABLE.CLOBFIELD%TYPE) IS (...) INSERT INTO MY_TABLE (ID, CLOBFIELD) VALUES (VID, EMPTY_CLOB()) RETURNING CLOBFIELD INTO VCLOBFIELD; This works fine alone. But now I a condition is met, I want to insert the same CLOB data into another table: (...) IF VSAVE=1 THEN INSERT INTO MY_TABLE2 (ID2, CLOBFIELD2) VALUES (VID2, EMPTY_CLOB()) RETURNING CLOBFIELD INTO VCLOBFIELD; If I try to 're-use' the CLOB field created from PHP with OciNewDescriptor, then the second INSERT puts the data into MY_TABLE2, but it dissapears from MY_TABLE. If the second INSERT never takes place, the data appears at MY_TABLE. So I can't use the same descriptor twice. But I don't want to call two stored procedures or create two different descriptors from PHP, I'd like to pass one descriptor, copy it using PL/SQL, and insert the same data into two tables. I've tried unsuccessfully with: - SELECT CLOBFIELD INTO TEMPCLOB FROM MY_TABLE2 WHERE (ID2 = VID2) FOR UPDATE; DBMS_LOB.COPY(TEMPCLOB, CLOBFIELD, len); after trying the second insert without the RETURNING clause, but no way. - DBMS_LOB.CREATETEMPORARY and then DBMS_LOB.COPY but I'm not such a PL/SQL expert to tell what's happening. It seems that the PHP allocated CLOB cannot be copied into a PL/SQL CLOB or something. Any ideas? Thank you very much. F.J. Ortiz

« previous php.db (#13236) next »