Home » Developer & Programmer » Reports & Discoverer » MS Excel format mail from Stored Procedure  () 1 Vote
MS Excel format mail from Stored Procedure [message #266518] Tue, 11 September 2007 01:36
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.

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





Previous Topic: Printing reports in the required sequence (merged by LF)
Next Topic: display special records first in a report
Goto Forum:
  


Current Time: Mon Dec 05 03:07:00 CST 2016

Total time taken to generate the page: 0.25615 seconds