Home » SQL & PL/SQL » SQL & PL/SQL » mail out from Stored Procedure
mail out from Stored Procedure [message #266686] Tue, 11 September 2007 07:19 Go to next message
Imran_Chennai
Messages: 22
Registered: June 2007
Location: CHN,INDIA
Junior Member

Hi,

My PLSQL procedure generates and mail across a .xls format file, this file has one worksheet.

my qn is, Is it possible to genarate multiple worksheets in single spreadsheet , I want to split my report into 3 category in 3 diff sheets of single excel file

below is the code

r_backorder_report_lines c_backorder_report_lines%ROWTYPE;

lv_output_location VARCHAR2(20) := '/usr/tmp';
lv_file_name VARCHAR2(30) := 'Backorder_report';
lv_xls_file VARCHAR2(30) := lv_file_name||'.xls';
lv_temp_file VARCHAR2(30) := lv_file_name||'.txt';
lv_output_file VARCHAR2(30) := lv_file_name||'.dat';
lv_full_file_name VARCHAR(50) := lv_output_location||'/'||lv_output_file;
lc_write CHAR(01) := 'W';
lf_output_file UTL_FILE.FILE_TYPE;
lv_output_line VARCHAR2(2000) := NULL;
lv_email_from VARCHAR2(200) := 'abc@abc.com';
lv_email_to VARCHAR2(200) := 'abc123@abc.com kjsdfh23@abc.com';
lv_subject VARCHAR2(80) := '''BACKORDER REPORT''';
lv_proc_running VARCHAR2(80) := 'PROC1';
lv_return NUMBER;
lv_command VARCHAR2(200) := NULL;

BEGIN

lf_output_file := UTL_FILE.FOPEN(lv_output_location,lv_output_file,lc_write);

FOR r_backorder_report_lines IN c_backorder_report_lines
LOOP
.....
UTL_FILE.PUT_LINE(lf_output_file,lv_output_line);
lv_output_line := NULL;
END LOOP;
UTL_FILE.PUT_LINE(lf_output_file,lv_output_line);
dbms_output.put_line('close_file');
UTL_FILE.FCLOSE(lf_output_file);


lv_command := 'uuencode '||lv_full_file_name||' '||lv_xls_file
||' > '||lv_output_location||'/'||lv_temp_file;

lv_return := CAD$FILE_UTIL.Execute_System_Command_NOACK(lv_command); -- HOSTCOMMAND..DBMS PIPE PACKAGE

lv_command := '/usr/bin/mailx -s '||lv_subject||' '
||lv_email_to||' < '||lv_output_location||'/'||lv_temp_file;

lv_return := CAD$FILE_UTIL.Execute_System_Command_NOACK(lv_command); -- HOSTCOMMAND..DBMS PIPE PACKAGE

Please give your idea

Thanks in advance
Imran
Re: mail out from Stored Procedure [message #266693 is a reply to message #266686] Tue, 11 September 2007 07:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: mail out from Stored Procedure [message #266706 is a reply to message #266693] Tue, 11 September 2007 08:13 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I would use UTL_SMTP or UTL_MAIL, depending on my Oracle version.
Previous Topic: how to display a query
Next Topic: inconsistent datatypes
Goto Forum:
  


Current Time: Wed Dec 07 02:54:25 CST 2016

Total time taken to generate the page: 0.12743 seconds