This post explains how to insert a blob object into database table programmatically.
- Create a folder/directory (ex:- C:\test )manually in the file system.
- Create a directory object in the database by connecting as SYSDBA and grant the access to custom schema(ex:- HR)
CONN / AS
SYSDBA
; CREATE OR REPLACE DIRECTORY
images
AS '
C:\test
'; GRANT READ, WRITE ON DIRECTORY
images
TO
HR
;
- Create the table as below in the custom schema (ex:-HR)
CREATE TABLE IMAGE_STORE ( IMAGE_ID NUMBER , FILE_NAME VARCHAR2(200 BYTE) , IMAGE BLOB, MIME_TYPE VARCHAR2(100 BYTE), CONSTRAINT IMAGE_STORE_PK PRIMARY KEY (IMAGE_ID) );
- Copy a test file (Test_File.pdf) into the directory ('C:\test') mentioned in the above script.
- Now connect to the custom schema (HR) and execute the below code
Feel free to point out if anything is missing/wrong in this blog.DECLARE src_lob BFILE := BFILENAME('
IMAGES
', '
Test_File.pdf
'); dest_lob BLOB; BEGIN INSERT INTO image_store VALUES (
1
,'
Test_File.pdf
', EMPTY_BLOB(), '
application/pdf
') RETURNING image INTO dest_lob; DBMS_LOB.OPEN(src_lob, DBMS_LOB.LOB_READONLY); DBMS_LOB.LoadFromFile( DEST_LOB => dest_lob, SRC_LOB => src_lob, AMOUNT => DBMS_LOB.GETLENGTH(src_lob) ); DBMS_LOB.CLOSE(src_lob); COMMIT; END; /
No comments:
Post a Comment