(Oracle8i): How to re-use a PHP-allocated CLOB in PL/SQL
| From: | Paco Ortiz | 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