The following articles mainly describe how OraclePL and SQL read BLOB fields from the database. If you are interested in the actual operations, the following articles will provide you with relevant knowledge. I hope you will gain some benefits.
1. confirm that the object exists
- SQL> col fdesc for a30
- SQL> select fid,fname,fdesc from eygle_blob;
FID FNAME FDESC
1 ShaoLin.jpg Shaolin Temple-Yijing
2 ing DaoYing.jpg
2. Create a storage Directory
- SQL> connect / as sysdba
- Connected.
- SQL> create or replace directory BLOBDIR as 'D:\oradata\Pic';
- Directory created.
- SQL>
- SQL> grant read,write on directory BLOBDIR to eygle;
- Grant succeeded.
- SQL>
-
3. Create a stored procedure
- SQL> connect eygle/eygle
- Connected.
- SQL>
- SQL> CREATE OR REPLACE PROCEDURE eygle_dump_blob (piname varchar2,poname varchar2) IS
- 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
- SELECT FPIC
- INTO l_blob
- FROM eygle_blob
- WHERE FNAME = piname;
- l_blob_len := DBMS_LOB.GETLENGTH(l_blob);
- l_file := UTL_FILE.FOPEN('BLOBDIR',poname,'wb', 32767);
- 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;
- UTL_FILE.FCLOSE(l_file);
- EXCEPTION
- WHEN OTHERS THEN
- IF UTL_FILE.IS_OPEN(l_file) THEN
- UTL_FILE.FCLOSE(l_file);
- END IF;
- RAISE;
- END;
- /
- Procedure created.
4. Retrieve field data
- SQL> host ls -l d:\oradata\Pic
- total 7618
- -rwxrwxrwa 1 gqgai None 2131553 Apr 19 10:12 DaoYing.jpg
- -rwxrwxrwa 1 gqgai None 1768198 Apr 19 10:12 ShaoLin.jpg
- SQL> exec eygle_dump_blob('ShaoLin.jpg','01.jpg')
- PL/SQL procedure successfully completed.
- SQL> host ls -l d:\oradata\Pic
- total 11072
- -rwxrwxrwa 1 Administrators SYSTEM 1768198 Apr 26 07:16 01.jpg
- -rwxrwxrwa 1 gqgai None 2131553 Apr 19 10:12 DaoYing.jpg
- -rwxrwxrwa 1 gqgai None 1768198 Apr 19 10:12 ShaoLin.jpg
- SQL>
- SQL> exec eygle_dump_blob('DaoYing.jpg','02.jpg')
- PL/SQL procedure successfully completed.
- SQL> host ls -l d:\oradata\Pic
- total 15236
- -rwxrwxrwa 1 Administrators SYSTEM 1768198 Apr 26 07:16 01.jpg
- -rwxrwxrwa 1 Administrators SYSTEM 2131553 Apr 26 07:19 02.jpg
- -rwxrwxrwa 1 gqgai None 2131553 Apr 19 10:12 DaoYing.jpg
- -rwxrwxrwa 1 gqgai None 1768198 Apr 19 10:12 ShaoLin.jpg
The above content is an introduction to reading BLOB fields from the database by OraclePL and SQL. I hope you will gain some benefits.
Article by: http://www.programbbs.com/doc/4255.htm