Home » SQL & PL/SQL » Client Tools » Script to generate Flat file + assign sysdate to a variable in sql script
Script to generate Flat file + assign sysdate to a variable in sql script [message #403817] Mon, 18 May 2009 23:30 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,

I have table called EMPLOYEE. It contains Empno, Ename,Sal,JOB columns.
I want to write a shell script which generates flat file.( with two column names)
Create a flat file with the following name :
Employee_YYYYMMDD.csv
Field separator to use : " ; "

e.g. EMPNO ; ENAME

can any please help me to write a script to generate ".csv" file.

Thank you
Re: Script to generate Flat file [message #403820 is a reply to message #403817] Mon, 18 May 2009 23:57 Go to previous messageGo to next message
BlackSwan
Messages: 22846
Registered: January 2009
Senior Member
>can any please help me to write a script to generate ".csv" file.
write a script to generate ".csv" file.

Might a script be Operating System dependent?
But wait, You decided we did not need to know what OS.

I suggest you withdraw from both computers & Oracle, because you seem to be untrainable.
Re: Script to generate Flat file [message #403823 is a reply to message #403820] Tue, 19 May 2009 00:32 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi ,

I have written the script as follows.
spool a.dat
select * from emp
spool off
spool b.csv
select empno||';'||ename from emp;
spool off
exit

But I need the O/P as "a_YYYYDDMM.dat" and "b_YYYYYYDDMM.csv".
But I am not getting how to append the date to the O/P file name.Can you please let me know if any mistakes in the above coding. and let me now how to add the date to the o/p file names.

Thank you
Re: Script to generate Flat file [message #403825 is a reply to message #403817] Tue, 19 May 2009 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 59299
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Employee_YYYYMMDD.csv
Field separator to use : " ; "

e.g. EMPNO ; ENAME

This has been asked and answered many times.
Please search.

Regards
Michel
Re: Script to generate Flat file [message #403826 is a reply to message #403823] Tue, 19 May 2009 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 59299
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But I need the O/P as "a_YYYYDDMM.dat" and "b_YYYYYYDDMM.csv".

This has been asked and answered many times.
Please search BEFORE posting.

Regards
Michel
Re: Script to generate Flat file [message #403832 is a reply to message #403826] Tue, 19 May 2009 01:43 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Michel,

I can't find any where .. so please le t me know the related thread ....

Thank you
Re: Script to generate Flat file [message #403834 is a reply to message #403832] Tue, 19 May 2009 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 59299
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post how you search.

Regards
Michel
Re: Script to generate Flat file [message #403836 is a reply to message #403834] Tue, 19 May 2009 02:28 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Searched by the string "Create Flat file using Shell Script"
Re: Script to generate Flat file [message #403840 is a reply to message #403836] Tue, 19 May 2009 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 59299
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have 2 questions:
"generate a csv file"
"date in spool name"

Regards
Michel
Re: Script to generate Flat file [message #403843 is a reply to message #403840] Tue, 19 May 2009 03:02 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

I am working on Oracle 10g :
I am running this .sql :
set term off
set feedback off
set HEADING off 
set pagesize 0
set linesize 120
set verify off
SET echo off
spool /data/dev/ins/dbi/backup/AGENT.dat
SELECT
AGENTKEY || '|' || PERSONID || '|' || ADDRESSID || '|' || AGENCYKEY || '|' || '<END_OF_RECORD>' 
FROM
AGENT;
spool off
/
spool /data/dev/ins/dbi/backup/AGENCY.txt
SELECT
AGENCYKEY || '|' || SNAME || '|' || LNAME || '|' || ADDRESSID || '|' || '<END_OF_RECORD>' 
FROM
AGENCY;
spool off

I have now an AGENT.dat and AGENCY.txt. How can I include the sysdate as part of the spool file ? Something like AGENT20090519.dat and AGENCY20090519.csv


Re: Script to generate Flat file [message #403844 is a reply to message #403843] Tue, 19 May 2009 03:15 Go to previous messageGo to next message
ThomasG
Messages: 3100
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
One option : copy / move the file after it is created.

tmp # touch agent.dat
tmp # ls agent*
agent.dat
tmp # mv agent.dat agent`date '+%Y%m%d'`.dat
tmp # ls agent*
agent20090519.dat
tmp #

Re: Script to generate Flat file [message #403848 is a reply to message #403843] Tue, 19 May 2009 03:26 Go to previous messageGo to next message
Michel Cadot
Messages: 59299
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How can I include the sysdate as part of the spool file ?

Quote:
This has been asked and answered many times.

Regards
Michel
Re: Script to generate Flat file [message #403856 is a reply to message #403844] Tue, 19 May 2009 04:17 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Thomas,

Thank you very much. But where we need to mention in the above statement ( what I have wriiten )
please let me know....

Thank you
Re: Script to generate Flat file [message #403857 is a reply to message #403856] Tue, 19 May 2009 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 59299
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In front of "tmp #"

Regards
Michel
Re: Script to generate Flat file [message #403860 is a reply to message #403857] Tue, 19 May 2009 04:37 Go to previous messageGo to next message
ThomasG
Messages: 3100
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Nowhere in the mentioned statement. In the shell script that calls that actual statement, after the file is created.

I guess that is headed into another user71408 classic. Something like :

"How do I get to the train station?"
"You walk down this street here, and ..."

"What is a street?"
"A street is this thing here, where the cars drive along, and ..."

"What is a car? What is driving? I just want to get to the train station!!"
"OK, then you walk in THIS DIRECTION, and ..."

"What is walking? What is a direction? I just want to get to the train station!!"
"Walking is when you put one foot before the other, and ..."

"What is a foot? I just want to get to the train station!!"
Re: Script to generate Flat file [message #403881 is a reply to message #403860] Tue, 19 May 2009 05:59 Go to previous messageGo to next message
Littlefoot
Messages: 19653
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
./fa/1693/0/
assign sysdate to a variable in sql script [message #403887 is a reply to message #403817] Tue, 19 May 2009 06:22 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,

I have written the following query. This query will return o/p of EMP table.The O/P shuld be stored in EMP_YYYYMMDD.dat
and the same SQL query will generate .CSV file that should be created as EMP_DET_YYYYMMDD.csv.
I need to write SQL Script for this.

select * from emp;
spool off;

select * from emp_det;
spool off;


Can any one please let me know how to assign System date to the SQL script. (Eg.. Spool EMP_(SYSTEMdate as pe rteh mentioned Format)

Thank you,
Re: assign sysdate to a variable in sql script [message #403891 is a reply to message #403887] Tue, 19 May 2009 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 59299
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the answer is the same as your previous and same question: this has been asked and answered many times, so search.

By the way, it is useless to start a new topic with the same question as both will be merged.

Regards
Michel
Re: Script to generate Flat file [message #403896 is a reply to message #403860] Tue, 19 May 2009 07:30 Go to previous messageGo to next message
pablolee
Messages: 2634
Registered: May 2007
Location: Scotland
Senior Member
@ThomasG ./fa/449/0/
very, very funny.
Re: Script to generate Flat file + assign sysdate to a variable in sql script [message #403990 is a reply to message #403817] Tue, 19 May 2009 23:43 Go to previous messageGo to next message
BlackSwan
Messages: 22846
Registered: January 2009
Senior Member
http://forums.oracle.com/forums/message.jspa?messageID=3479526#3479526
Re: Script to generate Flat file + assign sysdate to a variable in sql script [message #404008 is a reply to message #403990] Wed, 20 May 2009 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 59299
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Same lack of understanding (from both parts) in this forum. Grin

Regards
Michel
Re: Script to generate Flat file [message #404017 is a reply to message #403840] Wed, 20 May 2009 01:32 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
SQL> col todaydate new_value todaydate
SQL> select to_char(sysdate,'YYYYMMDD') as todaydate from dual;
 
TODAYDAT
--------
20090519
 
1 row selected.
 
SQL> spool EMP_&todaydate..dat
SQL> spool
currently spooling to EMP_20090519.dat


Note the '.' as terminator character.

For Google searches, try putting "Oracle" together with some relevant keywords, e.g.

oracle How can I include the sysdate as part of the spool file -burleson
Re: Script to generate Flat file [message #404025 is a reply to message #404017] Wed, 20 May 2009 02:30 Go to previous messageGo to next message
Michel Cadot
Messages: 59299
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
For Google searches, try putting "Oracle" together with some relevant keywords, e.g.

You know, this has already been told to him several times.
And the answer you posted has been given to him in the other topic and he didn't understand it. Do you think he will for yours? Evil or Very Mad

Regards
Michel
Re: Script to generate Flat file [message #404042 is a reply to message #404017] Wed, 20 May 2009 03:24 Go to previous messageGo to next message
Frank
Messages: 7877
Registered: March 2000
Senior Member
William Robertson wrote on Wed, 20 May 2009 08:32


For Google searches, try putting "Oracle" together with some relevant keywords, e.g.

oracle How can I include the sysdate as part of the spool file -burleson


LOL
Adding -burleson is even more important than adding Oracle if you want useful hits!
Re: Script to generate Flat file [message #404066 is a reply to message #404042] Wed, 20 May 2009 04:32 Go to previous messageGo to next message
Michel Cadot
Messages: 59299
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Adding -burleson is even more important than adding Oracle if you want useful hits!


Laughing

Re: Script to generate Flat file [message #404071 is a reply to message #404025] Wed, 20 May 2009 04:42 Go to previous messageGo to next message
Frank
Messages: 7877
Registered: March 2000
Senior Member
Michel Cadot wrote on Wed, 20 May 2009 09:30

And the answer you posted has been given to him in the other topic and he didn't understand it. Do you think he will for yours? Evil or Very Mad

Regards
Michel


hehehe... Have you noticed who gave him this answer in the OTN-forum topic?
Wink
Re: Script to generate Flat file [message #404208 is a reply to message #404066] Wed, 20 May 2009 17:27 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Without that filter the top hit (as usual) was a dba-oracle.com page entitled "Adding SYSDATE to a SQL*Plus spool file name" that almost gave the answer but forgot to include the "column ... new_value" line and therefore made no sense and didn't work.
Re: Script to generate Flat file [message #404255 is a reply to message #404208] Thu, 21 May 2009 02:26 Go to previous message
Michel Cadot
Messages: 59299
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To prevent from this use Oracle Safe Search.

Regards
Michel
Previous Topic: SQL Navigator not working
Next Topic: SQL * Plus report output in vertical allignment
Goto Forum:
  


Current Time: Thu Oct 02 13:22:36 CDT 2014

Total time taken to generate the page: 0.12386 seconds