Oracle Stored Procedure Access Image

Use a stored procedure (PL/SQL) to access blob objects in the database-image new article: Add a custom tool to the web mapping application note: only store and read data on the server
The client can execute but also access data on the server.
Perform the following operations on the server:
1. Use stored procedures (PL/SQL) to store blob objects in the database
The following stored procedure is used to load blob objects to a database:
1. create directory and authorize
For more information about directory, see using create directory & utl_file in Oracle.
C:/> sqlplus "/As sysdba"
 
SQL * Plus: Release 10.1.0.3.0-production on TUE Apr 26 07:11:51 2005
 
Copyright (c) 1982,200 4, Oracle. All rights reserved.
 
 
Connected:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0-Production
With the partitioning, Oracle Label Security, OLAP and Data Mining options
 
SQL> create user eygle identified by eygle default tablespace users;
 
User Created.
 
SQL> grant connect, resource, DBA to eygle;
 
Grant succeeded.
 
SQL> connect/As sysdba
Connected.
SQL> Create or replace directory blobdir as 'd:/oradata/PIC ';
 
Directory created.
 
SQL> grant read on directory blobdir to eygle;
 
Grant succeeded.
 
SQL>
 

2. Create a test table
SQL> connect eygle/eygle
Connected.
SQL> Create Table eygle_blob (
2 FID number,
3 fname varchar2 (50 ),
4 fdesc varchar2 (200 ),
5 fpic blob)
6/
 
Table created.
 
SQL>
SQL> Create sequence s_eygle_seq
2 start with 1
3 increment by 1
4/
 
Sequence created.
 
SQL>
 
 
3. Create a stored procedure
SQL> Create or replace procedure eygle_load_blob (pfname varchar2, pdesc varchar2)
2 is
3 src_file bfile;
4 dst_file blob;
5 lgh_file binary_integer;
6 begin
7 src_file: = bfilename ('blobdir', pfname );
8
9 insert into eygle_blob (FID, fname, fdesc, FPIC)
10 values (s_eygle_seq.nextval, pfname, pdesc, empty_blob ())
11 returning FPIC into dst_file;
12
13 select FPIC into dst_file
14 from eygle_blob where fname = pfname for update;
15
16 dbms_lob.fileopen (src_file, dbms_lob.file_readonly );
17 lgh_file: = dbms_lob.getlength (src_file );
18 dbms_lob.loadfromfile (dst_file, src_file, lgh_file );
19
20 update eygle_blob set FPIC = dst_file
21 where fname = pfname;
22
23 dbms_lob.fileclose (src_file );
24 commit;
25 end eygle_load_blob;
26/
 
Procedure created.
 
SQL> Col segment_name for A30
SQL> select segment_name, segment_type, Bytes/1024/1024 from dba_segments where owner = 'eygl ';
 
Segment_name segment_type Bytes/1024/1024
---------------------------------------------------------------
Sys_il0000050545c00004 $ lobindex. 0625
Sys_lob0000050545c00004 $ lobsegment. 0625
Eygle_blob table. 0625
 
 

4. Load blob objects
SQL> exec eygle_load_blob('shaolin.jpg ', 'shaolin Temple-Kangxi shoushu ');
 
PL/SQL procedure successfully completed.
 
SQL> select segment_name, segment_type, Bytes/1024/1024 from dba_segments where owner = 'eygl ';
 
Segment_name segment_type Bytes/1024/1024
---------------------------------------------------------------
Sys_il0000050545c00004 $ lobindex. 0625
Sys_lob0000050545c00004 $ lobsegment 4
Eygle_blob table. 0625
 
SQL> exec eygle_load_blob('daoying.jpg ', '');
 
PL/SQL procedure successfully completed.
 
SQL> select segment_name, segment_type, Bytes/1024/1024 from dba_segments where owner = 'eygl ';
 
Segment_name segment_type Bytes/1024/1024
---------------------------------------------------------------
Sys_il0000050545c00004 $ lobindex. 0625
Sys_lob0000050545c00004 $ lobsegment 7
Eygle_blob table. 0625
 
 
SQL> Col fname for A20
SQL> Col fdesc for A30
SQL> select FID, fname, fdesc, dbms_lob.getlength (FPIC) siz from eygle_blob;
 
FID fname fdesc siz
----------------------------------------------------------------------
1 shaolin.jpg Shaolin Temple-Kangxi shoushu 1768198
2 daoying.jpg returns 2131553
 
 
D:/oradata/PIC> ls-l
-Rwxrwxrwa 1 gqgai none 2131553 Apr 19 daoying.jpg
-Rwxrwxrwa 1 gqgai none 1768198 Apr 19 shaolin.jpg
 

Through the above method, we can easily store large objects in the database.
Ii. Reading blob objects from the database using PL/SQL
1. confirm existing objects
SQL> Col fdesc for A30
SQL> select FID, fname, fdesc from eygle_blob;
 
FID fname fdesc
------------------------------------------------------------------------------------------
1 shaolin.jpg Shaolin Temple-Kangxi shoushu
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
2 l_file utl_file.file_type;
3 l_buffer raw (32767 );
4 MAID: = 32767;
5 Rochelle pos integer: = 1;
6 l_blob;
7 l_blob_len integer;
8 begin
9 select FPIC
10 into l_blob
11 from eygle_blob
12 where fname = piname;
13
14 l_blob_len: = dbms_lob.getlength (l_blob );
15 l_file: = utl_file.fopen ('blobdir', poname, 'wb', 32767 );
16
17 while l_pos <l_blob_len Loop
18 dbms_lob.read (l_blob, l_amount, l_pos, l_buffer );
19 utl_file.put_raw (l_file, l_buffer, true );
20 l_pos: = l_pos + l_amount;
21 End loop;
22
23 utl_file.fclose (l_file );
24
25 exception
26 when others then
27 if utl_file.is_open (l_file) then
28 utl_file.fclose (l_file );
29 end if;
30 raise;
31 end;
32/
 
Procedure created.
 

4. Retrieve Data
SQL> host LS-l D:/oradata/PIC
Total 7618
-Rwxrwxrwa 1 gqgai none 2131553 Apr 19 daoying.jpg
-Rwxrwxrwa 1 gqgai none 1768198 Apr 19 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 01.jpg
-Rwxrwxrwa 1 gqgai none 2131553 Apr 19 daoying.jpg
-Rwxrwxrwa 1 gqgai none 1768198 Apr 19 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 01.jpg
-Rwxrwxrwa 1 Administrators system 2131553 Apr 26 02.jpg
-Rwxrwxrwa 1 gqgai none 2131553 Apr 19 daoying.jpg
-Rwxrwxrwa 1 gqgai none 1768198 Apr 19 shaolin.jpg