Hi
You need to know where you want the documents insight Oracle or outside and use BFILE.
Consider this:
1. If a lot of the PDF files are below 4K – then store files insight Oracle and ‘inline’ is a possibility within the table.
2. If a lot of the PDF files are updated regular (ex. version) - then BFILE is best because documents can be updated from source without going through Oracle.
3. Use only BFILE if files are saved on/controlled by the Oracle server. Documents then also have some level of security.
Storing documents insight Oracle:
--------------------------------
CREATE TABLE PDF_Docs ( pdf_document BLOB) TABLESPACE table_data
LOB ( pdf_document)
STORE AS ( TABLESPACE lob_space ENABLE STORAGE IN ROW
STORAGE( INITIAL 128K NEXT 128K PCTINCREASE 0 )
CHUNK 32768 PCTVERSION 20 NOCACHE NOLOGGING
INDEX ( tablespace index_data STORAGE ( INITIAL 128K NEXT 128K ) )
);
LOBS are stored in a LOB data segment. The data segment is made of CHUNKS. CHUNKS are contiguous set of blocks, number of bytes for I/O and LOB index points to CHUNK using Internal LOB ID and CHUNK starting address. CHUNK also used for versioning.
CHUNK must be multiple of database block size – and must be a value there you do not waste to much space. A document always takes at least one CHUNK.
In my example I used 32786 = database block size 8K x 4 = maximum Oracle.
Use small CHUNKS if many users – and to avoid network traffic (CHUNK=DB size=Network traffic)
PCTVERSION is important – because it is the maximum % of all LOB data space that can be occupied by old versions of LOB data CHUNKS (read consistency), otherwise you get a “snapshot too old”, because no rollback created on LOB data segments – but it does for LOB index.
Use DISABLE STORAGE IN ROW to avoid documents below 4K to bee storage in-line in table as a normal column data.
Storing documents outsight Oracle:
---------------------------------
The table creating is simple, but you have to make some other stuff.
1. Make a directory where Oracle server controls access. (ex. /oracle/oradata/pdf)
2. Grant access to directory and files from OS so Oracle can read the files (ex. chmod)
3. Login as SYSTEM (sysdba, sysoper)
4. CREATE DIRECTORY pdf_path as '/oracle/oradata/pdf';
5. GRANT READ ON DIRECTORY pdf_path to USERNAME;
Now login as USERNAME and:
CREATE TABLE pdf_docs ( pdf_document BFILE) TABLESPACE table_data;
Please note:
Do not create DIRECTORY items on paths with database files
You can grant “CREATE ANY DIRECTORY” and “DROP ANY DIRECTORY” to users.
Remember to pre-create directory paths and properly set permissions before using the DIRECTORY item so Oracle can read the file.
Then inserting rows, you use the pdf_path so no direct path in applications. You can change path without re-writing your code.
INSERT INTO pdf_docs VALUES( BFILENAME('pdf_path', 'test.pdf') );
Uploading data to LOBS:
----------------------
Use the package DBMS_LOB.
Regards
Allan
