Loading Documents into Oracle

A short demonstration of loading documents into an Oracle Database.

SQL> @create_docs_table
SQL> spool create_docs_table
SQL>
SQL> drop table my_documents purge;

Table dropped.

SQL> create table my_documents
  2  (doc_number number generated always as identity,
  3  doc_title varchar2(100),
  4  doc_description varchar2(256),
  5  os_file_name varchar2(256),
  6  file_content blob,
  7  file_type varchar2(16))
  8  lob(file_content) store as securefile (compress high keep_duplicates)
  9  /

Table created.

SQL>
SQL> spool off
SQL>

I have created table with a SecureFile LOB to hold the documents.

Next, I use sqlloader to load the documents, based on a "list file".

C:\load_docs_to_oracle>type List_of_Files_to_Upload.csv
Oracle 12c Availability,High Availability Options in Oracle 12c,12c_Availability.pdf,PDF
Oracle 12c Security,Security Mechanisms in Oracle 12c,12c_Security.pdf,PDF
Monitoring and Diagnostics without OEM,Presentation at Oracle SOS Conference,Monitoring and Diagnostics without OEM.pdf,PDF
Setting up Data Guard in 11gR2,My document on DG setup in 11gR2 ,Setting up DataGuard in 11gR2.docx,DOCX
Flashback Database and DG DR Testing,How to use Flasback Database in DataGuard for DR Testing,Using FLASHBACK DATABASE for Destructive DR Testing.docx,DOCX
Create Docs Table,Source Code for Script to create DOCS Table,create_docs_table.sql,SQL

C:\load_docs_to_oracle>


C:\load_docs_to_oracle>type my_documents.ctl
load data
        infile 'List_of_Files_to_Upload.csv'
        into table my_documents
        fields terminated by ','
(
doc_title ,
doc_description ,
os_file_name,
file_content lobfile(os_file_name) terminated by EOF,
file_type
)

C:\load_docs_to_oracle>


C:\load_docs_to_oracle>sqlldr mydocdb/mydocdb@pdb1 control=my_documents.ctl

SQL*Loader: Release 19.0.0.0.0 - Production on Sun Nov 7 17:01:33 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 6

Table MY_DOCUMENTS:
  6 Rows successfully loaded.

Check the log file:
  my_documents.log
for more information about the load.

C:\load_docs_to_oracle>


C:\load_docs_to_oracle>type my_documents.log

SQL*Loader: Release 19.0.0.0.0 - Production on Sun Nov 7 17:01:33 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Control File:   my_documents.ctl
Data File:      List_of_Files_to_Upload.csv
  Bad File:     List_of_Files_to_Upload.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     250 rows, maximum of 1048576 bytes
Continuation:    none specified
Path used:      Conventional

Table MY_DOCUMENTS, loaded from every logical record.
Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DOC_TITLE                           FIRST     *   ,       CHARACTER
DOC_DESCRIPTION                      NEXT     *   ,       CHARACTER
OS_FILE_NAME                         NEXT     *   ,       CHARACTER
FILE_CONTENT                      DERIVED     *  EOF      CHARACTER
    Dynamic LOBFILE.  Filename in field OS_FILE_NAME
FILE_TYPE                            NEXT     *   ,       CHARACTER


Table MY_DOCUMENTS:
  6 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                 258000 bytes(250 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             6
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Sun Nov 07 17:01:33 2021
Run ended on Sun Nov 07 17:01:35 2021

Elapsed time was:     00:00:01.17
CPU time was:         00:00:00.06

C:\load_docs_to_oracle>

Note : In "List_of_Files_to_Upload.csv" the incoming file names are *not* enclosed in quotes and there is no space-character before the filename.

Once you have loaded the documents, SQLDeveloper gives you the option of either opening the file (e.g. PDF files opened by Acrobat Reader) or downloading the file that you query.  (In my installation, DOCX and SQL files have to be downloaded for viewing as the Exernal Editor is not configured for these file-extensions -- the DOCX is identified as a "jar" file).

[click on the images below to view them better]