Saving Files to Oracle database

2:20 AM database, oracle No Comments

In SQL * Plus Oracle 11g, we can perform storage and retrieval operations from and to file an Oracle database, which previously could not do. In previous versions, if we try to query the type of blob field or bfile, SQL * Plus will display the message "SP2-0678: Column or attribute type can not be displayed by SQL * Plus". Here's my experiment in Oracle 11g to perform operations with the type of file storage BFILE in Oracle 11g using SQL * Plus:

SQL> create table album(id number(5) primary key, keterangan varchar(1000), foto bfile);

SQL> select * from album;

no rows selected

SQL> create or replace directory ALBUMDIR as ‘/home/oracle/Pictures’;

(jika ada pesan ORA-00990: missing or invalid privilege, berikan hak CREATE ANY DIRECTORY kepada userAnda).

SQL> create sequence albumseq;

SQL> insert into album values (albumseq.nextval, ‘komik koran spiderman’, bfilename(‘ALBUMDIR’, ‘2.png’));

SQL> col keterangan format a30;

SQL> select * from album;

ID KETERANGAN
———- ——————————
FOTO

1 komik koran spiderman
bfilename(‘ALBUMDIR’, ‘2.png’)

Thus for example the use BFILE. For BLOB data type, we can try to enter the EMPTY_BLOB function () to see that the SQL * Plus in Oracle 11g can also be displayed.

For example the Java programming of reading the contents of files stored in a BFILE column, please refer kehttp: / / www.oracle.com / technology / sample_code / tech / java / sqlj_jdbc / files / advanced / BFILESample / Readme.html. Thanks.