Discussion :

[tipon]

bonjour,

dans une table il y a un champ blob qui contient pour chaque ligne des fichier excel

pouvez vous me donner une méthode pour extraire ces fichier dans un répertoire

[JahPil78]

Dépendant de ta version Oracle...

Avec 9i il existe une fonction du package UTL_FILE:
DECLARE
l_file UTL_FILE.FILE_TYPE;
l_buffer RAW(32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob BLOB;
l_blob_len INTEGER;
BEGIN
-- Get LOB locator
SELECT col1
INTO l_blob
FROM tab1
WHERE rownum = 1;

l_blob_len := DBMS_LOB.getlength(l_blob);

-- Open the destination file.
l_file := UTL_FILE.fopen('BLOBS','MyImage.gif','w', 32767);

-- Read chunks of the BLOB and write them to the file
-- until complete.
WHILE l_pos < l_blob_len LOOP
DBMS_LOB.read(l_blob, l_amount, l_pos, l_buffer);
UTL_FILE.put_raw(l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
END LOOP;

-- Close the file.
UTL_FILE.fclose(l_file);

EXCEPTION
WHEN OTHERS THEN
-- Close the file if something goes wrong.
IF UTL_FILE.is_open(l_file) THEN
UTL_FILE.fclose(l_file);
END IF;
RAISE;
END;
/
(voir article http://www.oracle-base.com/articles/9i/ExportBlob9i.php)

Autrement, possibilité avec une procédure Java:
CREATE OR REPLACE JAVA SOURCE NAMED "BlobHandler" AS
import java.lang.*;
import java.sql.*;
import oracle.sql.*;
import java.io.*;

public class BlobHandler
{

public static void ExportBlob(String myFile, BLOB myBlob) throws Exception
{
// Bind the image object to the database object
// Open streams for the output file and the blob
File binaryFile = new File(myFile);
FileOutputStream outStream = new FileOutputStream(binaryFile);
InputStream inStream = myBlob.getBinaryStream();

// Get the optimum buffer size and use this to create the read/write buffer
int size = myBlob.getBufferSize();
byte[] buffer = new byte[size];
int length = -1;

// Transfer the data
while ((length = inStream.read(buffer)) != -1)
{
outStream.write(buffer, 0, length);
outStream.flush();
}

// Close everything down
inStream.close();
outStream.close();
}

};
/

ALTER java source "BlobHandler" compile;
show errors java source "BlobHandler"

Voir article: http://www.oracle-base.com/articles/8i/ExportBlob.php

Hope it helps!