How to Store a PDF File in an Oracle Table

Written by christopher hanson
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Store a PDF File in an Oracle Table
Oracle databases can store PDF files in tables. (database on paper image by .shock from Fotolia.com)

There are several methods built into Oracle that support PDF storage. They can be stored via APEX or BFILE. They can also be stored directly into a table. To store a PDF file in an Oracle table, write an SQL file that places the file into BLOB column. Then run the SQL file through the Oracle command prompt. The file will be written directly into the database without any external utilities.

Skill level:
Moderate

Other People Are Reading

Instructions

  1. 1

    Launch the text editor or code editor of your choice.

  2. 2

    Copy and paste the following code to a blank document:

    CREATE OR REPLACE PROCEDURE load_lob AS

    id NUMBER;

    image 1 BLOB;

    locator BFILE;

    bfile_len NUMBER;

    bf_desc VARCHAR2(30);

    bf_name VARCHAR2(30);

    bf_dir VARCHAR2(30);

    bf_typ VARCHAR2(4);

    ctr integer;

    CURSOR get_id IS

    SELECT bfile_id,bfile_desc,bfile_type FROM graphics_table;
    

    BEGIN

    OPEN get_id;

    LOOP

    FETCH get_id INTO id, bf_desc, bf_typ;

    EXIT WHEN get_id%notfound;

    dbms_output.put_line('ID: '||to_char(id));

    SELECT bfile_loc INTO locator FROM graphics_table WHERE bfile_id=id;

    dbms_lob.filegetname(

    locator,bf_dir,bf_name);

    dbms_output.put_line('Dir: '||bf_dir);

    dbms_lob.fileopen(locator,dbms_lob.file_readonly);

    bfile_len:=dbms_lob.getlength(locator);

    dbms_output.put_line('ID: '||to_char(id)||' length: '||to_char(bfile_len));

    SELECT temp_blob INTO image 1 FROM temp_blob;

    bfile_len:=dbms_lob.getlength(locator);

    dbms_lob.loadfromfile(image 1,locator,bfile_len,1,1);

    INSERT INTO internal_graphics VALUES (id,bf_desc,image 1,bf_typ);

    dbms_output.put_line(bf_desc||' Length: '||TO_CHAR(bfile_len)||

    ' Name: '||bf_name||' Dir: '||bf_dir||' '||bf_typ);

    dbms_lob.fileclose(locator);

    END LOOP;

    END;

    /

  3. 3

    Save the file with the ".sql" extension. Click "File," "Save As." Place it in any directory. Give it a name and the extension ".sql" then click "Save."

  4. 4

    Run the script. Launch the Oracle command prompt and type "SQL > @" then the directory and filename of the script. Follow the instructions on the screen to load PDF files into the Oracle table.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.