Home » SQL & PL/SQL » SQL & PL/SQL » Email the output spool file name having date as filename
Email the output spool file name having date as filename [message #407935] Fri, 12 June 2009 07:38 Go to next message
mskumar
Messages: 2
Registered: June 2009
Junior Member
Hi All,

I am trying to send a output spool file name having dates that means the spool file name is not constant
and want to email the file as and attachment to recipients. I am trying to use utl_smtp package to send
the attachment but I don't know how to call the file name. Here is what I am trying:

column spool_text_source new_value spool_text

select 'Audit_Reports_'||
(case
when to_char(sysdate,'MM') between '01' and '03' then to_date('01-JAN' || '-' || to_char(sysdate,'YYYY'))
when to_char(sysdate,'MM') between '04' and '06' then to_date('01-APR' || '-' || to_char(sysdate,'YYYY'))
when to_char(sysdate,'MM') between '07' and '09' then to_date('01-JUL' || '-' || to_char(sysdate,'YYYY'))
when to_char(sysdate,'MM') between '10' and '12' then to_date('01-OCT' || '-' || to_char(sysdate,'YYYY'))
else to_date(null)
end ) ||'_'||
(case
when to_char(sysdate,'MM') between '01' and '03' then to_date('31-MAR' || '-' || to_char(sysdate,'YYYY'))
when to_char(sysdate,'MM') between '04' and '06' then to_date('30-JUN' || '-' || to_char(sysdate,'YYYY'))
when to_char(sysdate,'MM') between '07' and '09' then to_date('30-SEP' || '-' || to_char(sysdate,'YYYY'))
when to_char(sysdate,'MM') between '10' and '12' then to_date('31-DEC' || '-' || to_char(sysdate,'YYYY'))
else to_date(null)
end ) ||'.html' spool_text_source from dual;

spool &spool_text

<queries>

spool off

below is the email procedue with filename input:

execute email(file_name);

TIA

Kumar
Re: Email the output spool file name having date as filename [message #407937 is a reply to message #407935] Fri, 12 June 2009 07:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you don't know the name of the OS file that you want to send as an attachment, then you've got a problem.

Might I suggest creating your spool files with names that you know, or storing the name of the spool file in a table inside the database.
Re: Email the output spool file name having date as filename [message #407944 is a reply to message #407935] Fri, 12 June 2009 08:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, you should study the datetime format elements.
"Q" one gives you the quarter.

Regards
Michel
Re: Email the output spool file name having date as filename [message #407967 is a reply to message #407944] Fri, 12 June 2009 11:13 Go to previous messageGo to next message
mskumar
Messages: 2
Registered: June 2009
Junior Member
Hi,

Sorry if I missed the point, I want to make it automated process and call the email procedure from the same script and email the spool file to recipients. Here I am able to generate the spool file but same I can not call to the email procedure as a variable.

Thanks

Kumar
Re: Email the output spool file name having date as filename [message #407973 is a reply to message #407935] Fri, 12 June 2009 11:23 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>I can not call to the email procedure as a variable.

What does above statement mean?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Previous Topic: PLS-00304: cannot compile body of 'ETL_VALIDATION_PCK' without its specification
Next Topic: EXECUTE IMMEDIATE STORED proc having IN and OUT parameters - 10g
Goto Forum:
  


Current Time: Fri Dec 09 08:02:20 CST 2016

Total time taken to generate the page: 0.13999 seconds