Oracle Insert BLOB

How to Insert Blob Data(image, video) into OracleBlob Sizein This post it's shown how I can insert Blob data link image video to Oracle database and also how I can dete Rmine the size of the BLOB data from Oracle.

1) Create Directory Where BLOB resides.
Create or Replace directory temp as '/oradata2 ';

2) Grant Read permission to the user who work with this directory.
Grant Read on directory temp to Arju;

3) Create the Tablewhich holds LOB object.
--the storage table for the image file
CREATE TABLE PDM (
Dname VARCHAR2 (+),--Directory name
Sname VARCHAR2 (+),--subdirectory name
FName VARCHAR2 (+),--File name
Iblob BLOB); --Image file

4) Create The procedure that insert BLOB objects.

--Create the procedure to load the file
CREATE OR REPLACE PROCEDURE load_file (
Pdname VARCHAR2,
Psname VARCHAR2,
Pfname VARCHAR2) is

Src_file BFILE;
Dst_file BLOB;
Lgh_file Binary_integer;
BEGIN
Src_file: = Bfilename (' TEMP ', pfname);

--Insert a NULL record to lock
INSERT into PDM
(Dname, Sname, fname, Iblob)
VALUES
(Pdname, Psname, Pfname, Empty_blob ())
Returning Iblob into Dst_file;

--Lock record
SELECT Iblob
Into Dst_file
From PDM
WHERE dname = Pdname
and sname = Psname
and fname = Pfname
For UPDATE;

--Open the file
Dbms_lob.fileopen (Src_file, dbms_lob.file_readonly);

--Determine length
Lgh_file: = Dbms_lob.getlength (Src_file);

--Read the file
Dbms_lob.loadfromfile (Dst_file, Src_file, lgh_file);

--Update the BLOB field
UPDATE PDM
SET Iblob = Dst_file
WHERE dname = Pdname
and sname = Psname
and fname = Pfname;

--Close File
Dbms_lob.fileclose (Src_file);
END Load_file;
/

5) Execute the Procedure.

sql> exec load_file (' TEMP ', ' This is Image ', ' tritha7.png ');
PL/SQL procedure successfully completed.

6) from OS see the BLOB size.

Sql>!ls-l/oradata2/tritha7.png
-rwxr-xr-x 1 Oracle Oinstall 21150 June 9 01:55/oradata2/tritha7.png

7) from Oracle determine Blob size.
1 declare
2 a blob;
3 begin
4 Select Iblob to a from PDM;
5 Dbms_output.put_line (Dbms_lob.getlength (a));
6* end;
Sql>/

PL/SQL procedure successfully completed.

Sql> set Serverout on
Sql>/
21150来 Source: http://space.itpub.net/16860121/viewspace-717065

Recently encountered a lot of need to store Oracle BLOB requirements. It is well known that blobs are used to store images, PDFs, and other big Data objects. All of our database operations are operated using stored procedures because of the company's needs. So first paste a blob stored stored procedure for your reference:

SQL code

2. Create or replace
4. Procedure Ndssp_backup_fund (fund_id_in in varchar2, selector_in in varchar2,
6. Time_in in timestamp,blob_in in blob)
8. is
10. Key number;
12. Blob_tmp blob;
14. begin
16. Delete from fund_backup bf where bf.selector = selector_in and bf.fund_id = fund_id_in;
18. Select Fund_backup_seq.nextval into key from dual;
20. INSERT INTO fund_backup values (key,selector_in,fund_id_in,empty_blob (), time_in);
22. Select content into blob_tmp from fund_backup where id = key for update;
24. Dbms_lob.copy (Blob_tmp, blob_in, Dbms_lob.getlength (blob_in));
26. End Ndssp_backup_fund;

All you need to do is focus on the BLOB storage section, and the rest of the section can be ignored. As you can see, we want to invoke this stored procedure and have to pass a BLOB object. Source: http://lei6744.iteye.com/blog/1127608

Oracle Insert BLOB