Skip to Main Content
  • Questions
  • PLsql code to search and find pdf from folder and send in email as attachment

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, sampath.

Asked: April 11, 2017 - 2:12 pm UTC

Last updated: April 14, 2017 - 3:12 am UTC

Version: 11g

Viewed 1000+ times

You Asked

HI Oracle,

i Have a requirement - search and find pdf from folder/directory and send via email to the particular receipient.

here is more info about scenario:

we have pdf files generated already in one folder/directory
we need to send pdf files to respective members of our business as applicable.ex: 1 member can have 1 pdf or 2 or 3.
we already have list of members and their order numbers , their email id's in tables, we can pass these values as input to anmy pl/sql procedure.

when i pass a date parameter to my plsql script , it finds all memebrs and their order numbers , email id's from database.this much we can do .

we expect to find the pdf from folder/directory searching by order number whcih we provide and find those pdf's and attach them in email and send to member email id.

please advice , this is very urgent request .


Regards
Sam

and Connor said...

You can do something like this to get access to the directory

sql> create directory temp as 'c:\temp';

directory created.

sql> create directory bin as 'c:\bin';

Directory created.

SQL> host touch c:\temp\dummy_file.dat

SQL> host cat c:\temp\dir_list.cmd
@echo off
dir /s /b C:\oracle\doc\*.pdf

SQL> create table pdf_files
  2  ( file_name varchar2(255)
  3  )
  4  organization external
  5  (
  6    type oracle_loader
  7    default directory temp
  8    access parameters
  9    (
 10       records delimited by newline
 11       preprocessor bin: 'dir_list.cmd'
 12    )
 13    location ('dummy_file.dat')
 14  )
 15  reject limit unlimited;

Table created.


So we have an external table, that will run the "dir_list.cmd" file, which in turn, shows all pdf's under a given directory.

Hence it becomes something you can query

SQL> select * from  pdf_files where rownum < 10;

FILE_NAME
---------------------------------------------------------
C:\oracle\doc\10.2\B19306_01\appdev.102\a58231.pdf
C:\oracle\doc\10.2\B19306_01\appdev.102\a96109.pdf
C:\oracle\doc\10.2\B19306_01\appdev.102\b14249.pdf
C:\oracle\doc\10.2\B19306_01\appdev.102\b14250.pdf
C:\oracle\doc\10.2\B19306_01\appdev.102\b14251.pdf
C:\oracle\doc\10.2\B19306_01\appdev.102\b14252.pdf
C:\oracle\doc\10.2\B19306_01\appdev.102\b14253.pdf
C:\oracle\doc\10.2\B19306_01\appdev.102\b14254.pdf
C:\oracle\doc\10.2\B19306_01\appdev.102\b14255.pdf

9 rows selected.


Once you have the pdf you need, you can either download via browser, eg

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:232814159006

or send it as an email attachment, search this site for UTL_MAIL or UTL_SMTP



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

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

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library