Copying BLOBs in Oracle

Copying BLOBs in Oracle

Post by Ron Gordo » Thu, 18 Mar 1999 04:00:00

Our application uses a stored procedure to copy BLOBS. The procedure,
pCopyReps, is provided below. When the BLOB contains image data that exceeds
perhaps 32kbytes, the copy is made but only about the first 2kbyes of data
are actually copied. When we manually invoke the procedure from Oracle's SQL
Worksheet, no error message is given when Oracle V7 is used; with Oracle V8,
the following error is given:

SQLWKS> Execute pCopyReps(261,462)
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "WEBPDM.PCOPYREPS", line 13
ORA-06512: at line 2
The line 13 points to the SELECT clause that includes the ImageBlob field.
This could be a hint.
Here below is the procedure itself. Do you have any idea how to solve this

p_LngNewImageId INTEGER) AS


   v_ImgSize      INTEGER;
   v_ImgBLOB      LONG RAW;


   SELECT  ImageSize, ImageBLOB
   INTO  v_ImgSize, v_ImgBLOB
   FROM PPFPrints
   WHERE ImageSerialNumber = p_LngOldImageId;

   INSERT INTO PPFPrints (ImageSerialNumber, ImageSize, ImageBLOB)
   VALUES (p_LngNewImageId, v_ImgSize, v_ImgBLOB);

END pCopyPrints;


1. Copy blob (image) portion to another blob


How could I copy a portion of a blob to another blob?
I want to do it inside sp without downloading blob
to the client, trimming it there and uploading it back.
"updatetext" procedure doesn't allow me to specify
the source offset and size, only the destination offset.

Mike Kluev.

PS. Remove "-delete-." part of my e-mail address to reply.

2. Access <--ODBC--> Informix woes

3. Copy blob portion to another blob

4. How could I get Informix page size?

5. Copy BLOB's out of Oracle into a file using VBA in Access 97

6. non-constant comparisons in where clauses

7. Oracle: Copying a blob field ?

8. Query Problem

9. Oracle 8i : Copying Blobs To Os-FileSystem???

10. Inserting in a BLOB using oracle BLOB

11. Inserting a BLOB into an Oracle BLOB field.

12. DTS copy Blob data fails

13. Copy BLOB within TSQL ?