exporting clobs to files on database level

[lonewolf]

اريد ان اكتب اجراءprocedure علي مستوي
الdatabase engine
ياخد حقل clobمن جدول
و يكتبة في ملف خارجي و لكني لا اجد الpackage التي تقوم بذلك
هل هي موجودة بالفعل؟؟

[orayyan]

أخي الكريم استخدم
DBMS_LOB package

اليك هذه البكج

PACKAGE BODY LOBS_PKG
AS
vexists BOOLEAN;
vfile_length NUMBER;
vblocksize NUMBER;

-----------------------------------< Create Directory>
PROCEDURE doc_dir_setup
IS
BEGIN
EXECUTE IMMEDIATE 'CREATE DIRECTORY CLOB_DIR AS' || '''"\opt\Oracle"''';
END doc_dir_setup;

----------------------------------------<Retrieve File From DB>
PROCEDURE LIST (in_doc IN VARCHAR2)
IS
BEGIN
UTL_FILE.fgetattr ('CLOB_DIR',
in_doc,
vexists,
vfile_length,
vblocksize
);

IF vexists
THEN
DBMS_OUTPUT.put_line (in_doc || ' ' || vfile_length);
END IF;
END LIST;

-----------------------------------------<Insert Picture into DB>
PROCEDURE LOAD (in_id IN VARCHAR2)
IS
temp_blob BLOB := EMPTY_BLOB ();
bfile_loc BFILE;
bytes_to_load INTEGER := 4294967295;
BEGIN
bfile_loc := BFILENAME ('CLOB_DIR', in_id);

INSERT INTO mas_dicision_files
(dicinf_no
)
VALUES (in_id
);

SELECT dicfil_file
INTO temp_blob
FROM mas_dicision_files
WHERE dicinf_no = in_id
FOR UPDATE;

DBMS_LOB.OPEN (bfile_loc, DBMS_LOB.lob_readonly);
DBMS_LOB.OPEN (temp_blob, DBMS_LOB.lob_readwrite);
DBMS_LOB.loadfromfile (temp_blob, bfile_loc, bytes_to_load);
DBMS_LOB.CLOSE (temp_blob);
DBMS_LOB.CLOSE (bfile_loc);
COMMIT;
END LOAD;

---------------------------------<Search in the lobs>
PROCEDURE search (in_search VARCHAR2, in_id VARCHAR2)
IS
lob_doc BLOB;
pattern VARCHAR2 (30);
POSITION INTEGER := 0;
offset INTEGER := 1;
occurrence INTEGER := 1;
BEGIN
pattern := UTL_RAW.cast_to_raw (in_search);

SELECT dicfil_file
INTO lob_doc
FROM mas_dicision_files
WHERE dicinf_no = in_id
FOR UPDATE;

DBMS_LOB.OPEN (lob_doc, DBMS_LOB.lob_readonly);
POSITION := DBMS_LOB.INSTR (lob_doc, pattern, offset, occurrence);

IF POSITION = 0
THEN
DBMS_OUTPUT.put_line ('Pattern not found');
ELSE
DBMS_OUTPUT.put_line ('The pattern occurs at ' || POSITION);
END IF;

DBMS_LOB.CLOSE (lob_doc);
END search;
BEGIN
DBMS_OUTPUT.ENABLE (1000000);
END lobs_pkg;

[lonewolf]

مشكور يا سيدي