Home » SQL & PL/SQL » SQL & PL/SQL » Send sql query output to file or send by mail (Oracle 9.2.0.3 Unix, Client: windows)
Send sql query output to file or send by mail [message #408442] Tue, 16 June 2009 04:29 Go to next message
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 #408446 is a reply to message #408442] Tue, 16 June 2009 04:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1. It depends on how you do it. With SQL*Plus on client, with PL/SQL on server

2. See dbms_job

3. Yes. Search there are many examples, here and on the web.

Regards
Michel

Re: Send sql query output to file or send by mail [message #408449 is a reply to message #408446] Tue, 16 June 2009 04:46 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
yes, with pl/sql
I want to store the weekly query output in the file and send the output as mail attachment.


Do i need to configure smtp in server? Do I have no privilgege to do that from my side. Do I have access to directory?

[Updated on: Tue, 16 June 2009 04:58]

Report message to a moderator

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 Go to previous messageGo to next message
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 #408462 is a reply to message #408461] Tue, 16 June 2009 05:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In PL/SQL, use UTL_FILE

Regards
Michel
Re: Send sql query output to file or send by mail [message #408468 is a reply to message #408442] Tue, 16 June 2009 05:55 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Is the code above ok or there would have been a better way to do it?
Re: Send sql query output to file or send by mail [message #408470 is a reply to message #408468] Tue, 16 June 2009 06:00 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
Is the code above ok or there would have been a better way to do it?


Search for DUMP_CSV function at asktom

Regards
Sriram
Re: Send sql query output to file or send by mail [message #408477 is a reply to message #408468] Tue, 16 June 2009 06:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
prachij593 wrote on Tue, 16 June 2009 12:55
Is the code above ok or there would have been a better way to do it?

No it is not.
1/ Select works only if you have 1 row.
2/ utl_file does not take so much parameters
3/
" When Others then
RAISE_APPLICATION_ERROR(-20001,'Error '||SQLERRM);"
is just silly. Tell me what it adds to the original Oracle exception.
4/ It is badly indented

Regards
Michel
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 Go to previous messageGo to next message
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 #408481 is a reply to message #408480] Tue, 16 June 2009 06:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
prachij593 wrote on Tue, 16 June 2009 13:51
> 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

2/ Concatenate the values

3/ It is useless and hides where the exception comes from

Regards
Michel

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 Go to previous messageGo to next message
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 #408488 is a reply to message #408484] Tue, 16 June 2009 07:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It should be better to separate the values by a... separator character, if you want to read them one day.

And the code is still badly indented. After "loop" you should indent to the right; "exception" is at the same level than "begin" and "end".

In addition, you can't have a "no_data_found" exception with a cursor loop.

Regards
Michel
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 Go to previous messageGo to next message
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 #408490 is a reply to message #408442] Tue, 16 June 2009 07:20 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
You may also want to trunc the two sysdate calls.
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 Go to previous messageGo to next message
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 #408500 is a reply to message #408497] Tue, 16 June 2009 07:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You wrongly put your utl_file.open.
Please ONLY post a code that compiles.

Regards
Michel
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 Go to previous messageGo to next message
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 #408511 is a reply to message #408442] Tue, 16 June 2009 08:26 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
It might be an idea since you a creating a csv to use the standard csv seperator - a comma.
Re: Send sql query output to file or send by mail [message #408523 is a reply to message #408511] Tue, 16 June 2009 09:49 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Many Thanks for the suggesions...
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 Go to previous messageGo to next message
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 #408907 is a reply to message #408551] Thu, 18 June 2009 04:52 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Thanks Andrew for the alternative solution provided. But won't it be a security issue if the script is in production?

Many thanks...
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 Go to previous message
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
...


Previous Topic: Date SQL Query Issue
Next Topic: Left Outer join, eliminate extra records
Goto Forum:
  


Current Time: Sat Dec 03 18:14:56 CST 2016

Total time taken to generate the page: 0.11313 seconds