Send sql query output to file or send by mail [message #408442] |
Tue, 16 June 2009 04:29  |
prachij593
Messages: 266 Registered: May 2009
|
Senior Member |
|
|
I want to run a weekly query and store the output in a flat/csv file.
select * from emp where emp_join_dte between start_dte and end_dte;
(start_dte 01/01/2009
end_dte 07/01/2009 )
when executed on 8th jan 00:00 the date should be
(start_dte 08/01/2009
end_dte 14/01/2009 ) and so on..
My oracle server is in unix.
1. Can I store the output in my local machine or it will always get stored in server.
If its in server we may need a directory to store the file. Would we have access to the directory?
2. for the weekly sql we want to create a procedure, store the output by execute it using cron weekly at 00:00 hrs.Could u help with a simple demonstration please?
3. can we send the output by mail? how?
Thanks
|
|
|
|
|
Re: Send sql query output to file or send by mail [message #408461 is a reply to message #408442] |
Tue, 16 June 2009 05:35   |
prachij593
Messages: 266 Registered: May 2009
|
Senior Member |
|
|
I did try like this. Right now I am unable to create the directory in server.
Want to know if the code approach I am following is correct.or there is anything that I can do in better way.
Create or replace procedure weekly_qry_1
as
f utl_file.file_type;
var1 varchar2(200) ;
var2 varchar2(200) ;
var3 varchar2(200) ;
begin
select empid, empnm, sal into var1,var2,var3 from emp where emp_join_dte between sysdate-7 and sysdate;
f := utl_file.fopen('SAMPLEDATA','sample2.txt','W');
utl_file.put_line(f,var1,var2,var3);
utl_file.fclose(f);
Exception
When NO_DATA_FOUND then
null;
When Others then
RAISE_APPLICATION_ERROR(-20001,'Error '||SQLERRM);
end;
/
After this I want to send the output file as attachement
Please advice
Thanks.
[Updated on: Tue, 16 June 2009 05:43] Report message to a moderator
|
|
|
|
|
|
|
Re: Send sql query output to file or send by mail [message #408480 is a reply to message #408477] |
Tue, 16 June 2009 06:51   |
prachij593
Messages: 266 Registered: May 2009
|
Senior Member |
|
|
> 1/ Select works only if you have 1 row.
We need to usecurosr and fetch
> 2/ utl_file does not take so much parameters
Cant we use utl_file for more than one parameter? Then what is the alternative for this?
> 3/
" When Others then
RAISE_APPLICATION_ERROR(-20001,'Error '||SQLERRM);"
is just silly.
why?
Thanks for the advice
|
|
|
|
Re: Send sql query output to file or send by mail [message #408484 is a reply to message #408481] |
Tue, 16 June 2009 07:09   |
prachij593
Messages: 266 Registered: May 2009
|
Senior Member |
|
|
Concatenate the values
something like this way
CREATE OR REPLACE DIRECTORY MY_DIR AS 'C:\csv';
Create or replace procedure weekly_qry_1
as
f utl_file.file_type;
var1 varchar2(200) ;
var2 varchar2(200) ;
var3 varchar2(200) ;
--rc sys_refcursor;
s varchar2(4000);
fname varchar2(30);
begin
fname:= Output_||TO_CHAR(SYSDATE, 'ddmmyyyy');
for rec in ( select empid, empnm, sal from emp where emp_join_dte between sysdate-8 and sysdate-1);
f := utl_file.fopen('MY_DIR',fname,'W');
loop
s:= rec.empid || rec.empnm || rec.sal;
utl_file.put_line(f,s);
end loop;
utl_file.fclose(f);
Exception
When NO_DATA_FOUND then
null;
end;
/
Update: Formated
[Updated on: Tue, 16 June 2009 07:11] Report message to a moderator
|
|
|
|
Re: Send sql query output to file or send by mail [message #408489 is a reply to message #408488] |
Tue, 16 June 2009 07:20   |
prachij593
Messages: 266 Registered: May 2009
|
Senior Member |
|
|
Many thanks for the valuable advice.
CREATE OR REPLACE DIRECTORY MY_DIR AS C:\csv;
Create or replace procedure weekly_qry_1
as
f utl_file.file_type;
var1 varchar2(200) ;
var2 varchar2(200) ;
var3 varchar2(200) ;
s varchar2(4000);
fname varchar2(30);
begin
fname:= Output_||TO_CHAR(SYSDATE, 'ddmmyyyy');
for rec in ( select empid, empnm, sal from emp where emp_join_dte between sysdate-8 and sysdate-1);
f := utl_file.fopen('MY_DIR',fname,'W');
loop
s:= rec.empid || ' ' || rec.empnm || ' ' || rec.sal;
utl_file.put_line(f,s);
end loop;
utl_file.fclose(f);
end;
/
Dont we require any exception here?
|
|
|
|
Re: Send sql query output to file or send by mail [message #408497 is a reply to message #408490] |
Tue, 16 June 2009 07:40   |
prachij593
Messages: 266 Registered: May 2009
|
Senior Member |
|
|
Thanks
CREATE OR REPLACE DIRECTORY MY_DIR AS C:\csv;
Create or replace procedure weekly_qry_1
as
f utl_file.file_type;
var1 varchar2(200) ;
var2 varchar2(200) ;
var3 varchar2(200) ;
s varchar2(4000);
fname varchar2(30);
begin
fname:= Output_||TO_CHAR(SYSDATE, 'ddmmyyyy');
for rec in ( select empid, empnm, sal from emp where emp_join_dte between trunc(sysdate-8) and trunc(sysdate-1));
f := utl_file.fopen('MY_DIR',fname,'W');
loop
s:= rec.empid || ' ' || rec.empnm || ' ' || rec.sal;
utl_file.put_line(f,s);
end loop;
utl_file.fclose(f);
end;
/
After the file is created I need to send it as attachment.
What are the constraints if any?
Thanks for the help!
|
|
|
|
Re: Send sql query output to file or send by mail [message #408508 is a reply to message #408500] |
Tue, 16 June 2009 08:16   |
prachij593
Messages: 266 Registered: May 2009
|
Senior Member |
|
|
Correction:
Create or replace procedure weekly_qry_1
as
f utl_file.file_type;
var1 varchar2(200) ;
var2 varchar2(200) ;
var3 varchar2(200) ;
s varchar2(4000);
fname varchar2(30);
begin
fname:= 'Output_'||TO_CHAR(SYSDATE, 'ddmmyyyy');
for rec in ( select empid, empnm, sal from emp where emp_join_dte between TRUNC(sysdate-8) and TRUNC(sysdate-1));
f := utl_file.fopen('MY_DIR',fname,'W');
.....
|
|
|
|
|
Re: Send sql query output to file or send by mail [message #408551 is a reply to message #408523] |
Tue, 16 June 2009 13:29   |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
Personally, I'd put this all in an external script that can run on any host and avoids using util_file becuase it requires your DBA to set up an Oracle directory for you to write to. Also - these output files are Oracle owned but they really represent an "application" and have nothing to do with the DBA.
On disadvantage of an external sript is you'd generally need a user/pass in the script to log into Oracle. A stored proc called from dbms_job/dbms_scheduler avoids that.
#!/usr/bin/ksh
MAILTO=abc@xyz.com
ERRTO=abc@xyz.com
CSV=/tmp/report_`date '+%y%m%d_%H%M'`.csv
LOG=/tmp/report.log
USR=scott
PAS=tiger
DB=oradev.world
export PATH=$PATH:/usr/bin:.
. oraclient.sh
sqlplus -s /nolog <<EOF >$LOG
whenever sqlerror exit sql.sqlcode
connect $USR/$PAS@$DB
spool $CSV
set linesize 200 trimspool on pagesize 0
propmt DEPT,D-NAME,LOCATION
select deptno||','||dname||','||loc from dept;
spool off
exit sql.sqlcode
EOF
RV=$?
if [ $RV -eq 0 ]; then
SUBJ="my report"
BODY="Here is your report\nSee attachment"
mailx -s "$SUBJ" $MAILTO 1>/dev/null <<-EOF
$(echo $BODY;cat $CSV | ux2dos - | uuencode $CSV)
EOF
# EOF indented with tab not spaces
echo "Sent success email..."
else
SUBJ="ERROR: my report"
mailx -s "$SUBJ" $ERRTO <$LOG
echo "Sent failure email..."
fi
rm $CSV
exit $RV
[Updated on: Tue, 16 June 2009 14:47] Report message to a moderator
|
|
|
|
Re: Send sql query output to file or send by mail [message #409008 is a reply to message #408907] |
Thu, 18 June 2009 13:15  |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
no, it shouldn't be a big issue. You'd set permissions on the file only to be visible to the owner (chmod 700 script-name), or preferably externalize the connection details. Two easy ways are:
1) create a profile that your source in the script. It export USR,PAS,DB, ORACLE_HOME etc etc. Then just reference the variables in your script (after ensuring they are set). Something like:
if [ -z "$USR" -o -z "$PAS" -o -z "$DB" ]; then
echo "ERROR. One or more of USR, PAS, DB not set"
exit 1
fi
2) create a ora_connect.sql having "connect scott/tiger@dbname" in it then just call it:
...
sqlplus -s /nolog <<EOF >$LOG
whenever sqlerror exit sql.sqlcode
@/my-path/ora_connect.sql
spool $CSV
...
|
|
|