OraFAQ Forum: Client Tools » format output file by command spool

Home » Other » Client Tools » format output file by command spool

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

format output file by command spool [message #490084]

Thu, 20 January 2011 21:55

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"

Report message to a moderator

Re: format output file by command spool [message #490088 is a reply to message #490084]

Thu, 20 January 2011 23:02

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I am not sure exactly what you desire but try doing as below

sqlplus -s username/password

Report message to a moderator

Re: format output file by command spool [message #490093 is a reply to message #490088]

Thu, 20 January 2011 23:20

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 ???

Report message to a moderator

Re: format output file by command spool [message #490097 is a reply to message #490093]

Thu, 20 January 2011 23:33

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

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

Report message to a moderator

Re: format output file by command spool [message #490101 is a reply to message #490097]

Fri, 21 January 2011 00:01

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 ...

Report message to a moderator

Re: format output file by command spool [message #490104 is a reply to message #490101]

Fri, 21 January 2011 00:47

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: format output file by command spool [message #503680 is a reply to message #490084]

Tue, 19 April 2011 00:46

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

Report message to a moderator

Re: format output file by command spool [message #503697 is a reply to message #503680]

Tue, 19 April 2011 01:17

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Previous Topic:	Toad Code Page Issue
Next Topic:	DBCA

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 22 05:29:27 CST 2024