| mail out from Stored Procedure [message #266686] |
Tue, 11 September 2007 07:19  |
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
