Home » SQL & PL/SQL » Client Tools » format output file by command spool
format output file by command spool [message #490084] Thu, 20 January 2011 21:55 Go to next message
chuikingman
Messages: 90
Registered: August 2009
Member
Hi,
I use sqplus in oracle to output the command output to text file .

I use below set environment varialble.

SQL> set echo off;
SQL> set linesize 3999;
SQL> set feedback off;
SQL> set feedback off;
SQL> set termout off;
SQL> set pagesize 0;
SQL> spool mapping.txt
select C_SIM_MSISDN,C_SIM_IMSI from RCA_SMART_CARD order by C_SIM_MSISDN;


In ouput file , it look like

SQL> select C_SIM_MSISDN,C_SIM_IMSI from RCA_SMART_CARD order by C_SIM_MSISDN;
060010007 10007
:
:
:
SQL> spool off;


any setting or command that allow me to remove
the first sql command line" SQL>select XXXX"
and the last command "SQL>spool off" after start up the "spool mapping.txt"
Re: format output file by command spool [message #490088 is a reply to message #490084] Thu, 20 January 2011 23:02 Go to previous messageGo to next message
BlackSwan
Messages: 26187
Registered: January 2009
Location: SoCal
Senior Member
I am not sure exactly what you desire but try doing as below

sqlplus -s username/password
Re: format output file by command spool [message #490093 is a reply to message #490088] Thu, 20 January 2011 23:20 Go to previous messageGo to next message
chuikingman
Messages: 90
Registered: August 2009
Member
I only want the output file mapping.txt .
The fjrst line " SQL>select XXX " and the last line " SQL>spool off"
is removed in ths output file mapping.txt .
I do not want to use editor to edit the file as the file is hugh in size .
Any command and setting can do this in sql ???
Re: format output file by command spool [message #490097 is a reply to message #490093] Thu, 20 January 2011 23:33 Go to previous messageGo to next message
aha613
Messages: 6
Registered: January 2011
Location: Sydney
Junior Member
Hi there,

Try this way.
1.save your staff in c:\script.sql.
2. run sqlplus
3. File->open->c:\script.sql
it will show the source code of your script
4. at prompt, type in @script<enter>

c: user/db >@script<enter>

5. check your txt file. it should be exactly what you want.
Fingers cross.

Phil

[Updated on: Thu, 20 January 2011 23:34]

Report message to a moderator

Re: format output file by command spool [message #490098 is a reply to message #490097] Thu, 20 January 2011 23:40 Go to previous messageGo to next message
aha613
Messages: 6
Registered: January 2011
Location: Sydney
Junior Member
Another way.

1.Save

set echo off;
set linesize 3999;
set feedback off;
set feedback off;
set termout off;
set pagesize 0;
spool mapping.txt
select C_SIM_MSISDN,C_SIM_IMSI from RCA_SMART_CARD order by C_SIM_MSISDN;
spool off
ed mapping.txt

into c:\script.sql
2. run sqlplus
3. at prompt, type in @c:\script.sql<enter>
4. you will see the content opened by notepad in windows
Re: format output file by command spool [message #490101 is a reply to message #490097] Fri, 21 January 2011 00:01 Go to previous messageGo to next message
chuikingman
Messages: 90
Registered: August 2009
Member
aha613 wrote on Fri, 21 January 2011 13:33
Hi there,

Try this way.
1.save your staff in c:\script.sql.
2. run sqlplus
3. File->open->c:\script.sql
it will show the source code of your script
4. at prompt, type in @script<enter>

c: user/db >@script<enter>

5. check your txt file. it should be exactly what you want.
Fingers cross.

Phil

I use linux /Unix environment only , no window .
Please advice other way ...
Re: format output file by command spool [message #490104 is a reply to message #490101] Fri, 21 January 2011 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 65840
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do it the same way but with Unix shell script and file names.
The principle is: save the script in a file, call the scrit file from SQL*Plus using @ command.

Regards
Michel
Re: format output file by command spool [message #503680 is a reply to message #490084] Tue, 19 April 2011 00:46 Go to previous messageGo to next message
ashokvardhan
Messages: 1
Registered: April 2011
Junior Member
Try this it will give only SQL output but not the SQL query and Spool off....

sqlplus -s /nolog <<-EOF
whenever oserror exit failure
whenever sqlerror exit failure rollback
set heading off pagesize 0 feedback off linesize 400
set trimout on trimspool on termout off echo off sqlprompt ''

connect sys/manager as sysdba
spool file_name_tospool

YOUR QUERY;
spool off

exit

EOF
Re: format output file by command spool [message #503697 is a reply to message #503680] Tue, 19 April 2011 01:17 Go to previous message
Michel Cadot
Messages: 65840
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
connect sys/manager as sysdba

This is VERY VERY bad.

- Never ever use SYS (or SYSDBA) but for maintenance purpose (startup, shutdown, backup, recover)
- SYS/SYSDBA is special
- SYS/SYSDBA is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS/SYSDBA" and you'll see the immediate answer)
- SYS/SYSDBA does not act like any other user
- When you use SYS/SYSDBA Oracle deactivates some code path and activates others
- Whatever you do with SYS/SYSDBA will neither validate nor invalidate the same thing with any other user.

NEVER EVER use SYS/SYSDBA for anything that can be done by another user.
Use SYS/SYSDBA ONLY for something that can't be done by someone else.


Regards
Michel
Previous Topic: Toad Code Page Issue
Next Topic: DBCA
Goto Forum:
  


Current Time: Tue Oct 16 13:18:45 CDT 2018