Skip to Main Content
  • Questions
  • View pdf file (saved in db server directory)through form or report in oracle ebs

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question.

Asked: May 20, 2020 - 8:27 am UTC

Answered by: Connor McDonald - Last updated: May 22, 2020 - 12:34 am UTC

Category: PL/SQL - Version: 11g

Viewed 100+ times

You Asked

I have this file named for example" contarct1.pdf" that is saved in the database server directory

I can retrieve that directory path an details when i query from "dba_directories" view

I need to view this pdf file to user with any of the two options

1.submit a report in oracle ebs(need to know what should be the query of the report)

Or

2.press a button in oracle ebs custom form

and we said...

Here's an example of downloading/retrieving a file in the blob via a browser

https://asktom.oracle.com/pls/asktom/asktom.search?tag=displaying-pdf-files-stored-in-the-database

To get the file from directory into a blog, some PLSQL should suffice

declare
  l_bf  bfile;
  l_pdf   blob;

  l_dest_offset integer := 1;
  l_src_offset  integer := 1;
begin
  dbms_lob.createtemporary(l_pdf,true);

  l_bf := bfilename('MY_DIR', 'myreport.pdf');
  dbms_lob.fileopen(l_bf, dbms_lob.file_readonly);
  dbms_lob.loadblobfromfile (
    dest_lob    => l_pdf,
    src_bfile   => l_bf,
    amount      => dbms_lob.lobmaxsize,
    dest_offset => l_dest_offset,
    src_offset  => l_src_offset);
  dbms_lob.fileclose(l_bf);

end;
/


More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.