Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how to spool only results? (minus system output)

learningoracleFeb 15 2006 — edited Dec 15 2006
Hi all, i have 2 problems
my script below in italics
set linesize 140
set pagesize 1000
set feedback off
set echo off
spool anotherresults.txt
select
'Country Code:"'||a.country_id||'"',
'Country Name:"'||a.COUNTRY_NAME||'"',
'Residing in :"'||a.REGION_ID||'"'
from COUNTRIES a;


my results as below:
SQL> select *
2 'Country Code:"'||a.country_id||'"',*
3 'Country Name:"'||a.COUNTRY_NAME||'"',*
4 'Residing in :"'||a.REGION_ID||'"'*
5 from COUNTRIES a;*

'COUNTRYCODE:"'|| 'COUNTRYNAME:"'||A.COUNTRY_NAME||'"' * 'RESIDINGIN:"'||A.REGION_ID||'"' *
----------------- ------------------------------------------------------- *------------------------------------------------------- *
Country Code:"AR" Country Name:"Argentina" Residing in :"2"
Country Code:"AU" Country Name:"Australia" Residing in :"3"


Problem 1
: I want to get rid of lines (marked with an *). i thought setting feedback off and echo off would do the job, but i am wrong. Can anyone help? I am using oracle 10g express edition
My desired output is
Country Code:"AR" Country Name:"Argentina" Residing in :"2"
Country Code:"AU" Country Name:"Australia" Residing in :"3"


Problem 2:
How should i include a spool off command in my script? ( so that i do not need to type spool after the script completes)
i tried

Residing in :"'||a.REGION_ID||'"'
from COUNTRIES a;
spool off

i got a syntax error.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 12 2007
Added on Feb 15 2006
9 comments
4,804 views