Inserting media files into tables

[alphaaa]

I am trying to create a table for keep tracking the pdf documents. In order to do this i want to create a table which holds the thousands of PDF documents or the link to each of these documents.
My question is how do create such a table and how to insert media files into that table?

Thanks

 

[AOLEURO]

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