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

Breadcrumb

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.