Home » SQL & PL/SQL » Client Tools » how to not display substitutions in a spool file (oracle 8i)
how to not display substitutions in a spool file [message #316849] Mon, 28 April 2008 09:58 Go to next message
melvinRav
Messages: 43
Registered: April 2008
Location: India
Member
I have the following script


SET NEWPAGE 0 
SET SPACE 2
SET LINESIZE 350
SET PAGESIZE 0
SET ECHO Off
SET FEEDBACK off
set heading on 
set verify off 
spool c:\emp.txt
select empno , ename , deptno 

from emp where 

deptno=&department_number ;
spool off 


the problem i am facing is that when i run the script the enter value for department_number also spools to the output , is there anyway to avoid this from coming on the output
secondly is there way to generate an excel file , without import the text files in excel

p
Re: how to not display substitutions in a spool file [message #316853 is a reply to message #316849] Mon, 28 April 2008 10:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
set showmode off

Regards
Michel
Re: how to not display substitutions in a spool file [message #316858 is a reply to message #316853] Mon, 28 April 2008 10:47 Go to previous messageGo to next message
melvinRav
Messages: 43
Registered: April 2008
Location: India
Member
i tried putting

set showmode off

but still got the

Enter value for department_no: 30

EMPNO ENAME JOB MGR HIREDATE
---------- ---------- --------- ---------- ---------
7499 ALLEN SALESMAN 7698 20-FEB-81


i get the above as the output

is there a way to avoid the above output in the spool file ,

Michel , i have one doubt can we spool into excel ,so that we can get in proper excel format

Re: how to not display substitutions in a spool file [message #316861 is a reply to message #316858] Mon, 28 April 2008 11:04 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Oracle documentation is very good. Have a look at it and try it yourself which options fits your needs.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12040.htm#sthref2764

http://tahiti.oracle.com

Last but not least spend some time in reading the forum guidelines how to format your post.

Regards

Raj
Re: how to not display substitutions in a spool file [message #316864 is a reply to message #316861] Mon, 28 April 2008 11:31 Go to previous messageGo to next message
melvinRav
Messages: 43
Registered: April 2008
Location: India
Member
is there a way to spool to an excel file directly ,
because i spooling it as text file ,
then importing the text file in excel

please do help out
Re: how to not display substitutions in a spool file [message #316867 is a reply to message #316864] Mon, 28 April 2008 11:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does this have to do with the original question?

Regards
Michel
Re: how to not display substitutions in a spool file [message #316872 is a reply to message #316867] Mon, 28 April 2008 12:00 Go to previous messageGo to next message
melvinRav
Messages: 43
Registered: April 2008
Location: India
Member
because,ultimately i want to generate the result as an excel sheet(2ndpart to it ) , the substitution part can be deleted ,manually ,because i can t find a way to not show it ,

so i was looking into generating an excel sheet without importing as an text file ,

i don't know if this is possible ?
Re: how to not display substitutions in a spool file [message #316873 is a reply to message #316849] Mon, 28 April 2008 12:08 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
> i don't know if this is possible ?
It is possible using PERL, but not by using SQL*Plus.
Re: how to not display substitutions in a spool file [message #316877 is a reply to message #316873] Mon, 28 April 2008 12:55 Go to previous messageGo to next message
melvinRav
Messages: 43
Registered: April 2008
Location: India
Member
i was wondering whether i can save it as .csv ,
with the select query seperated by ||','||
then opening the spooled test.csv , in excel and saving it as test.xls

i iam just putting the sample code




SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 350
SET PAGESIZE 0
SET ECHO Off
SET FEEDBACK off
set heading off 
spool c:\test1.csv
select 'enumber'||','||'ename'||','||'ejob'||','|| 'edate'
from dual
union all
select eo||','||ena||','||jb||','||hd
from 
(
select
EMPNO  eo,ENAME  ena,JOB   jb,   HIREDATE hd
from emp
where
deptno=&department_no
)
/
spool off 
Re: how to not display substitutions in a spool file [message #317049 is a reply to message #316872] Tue, 29 April 2008 05:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can set the spoolfile to be a .csv, and then use the Sql*Plus option SET COLSEP , which will seperate all the columns with commas, giving you CSV data

SQL> set colsep ,
SQL> select sysdate,sysdate from dual;

SYSDATE  ,SYSDATE
---------,---------
29-APR-08,29-APR-08
Re: how to not display substitutions in a spool file [message #322074 is a reply to message #316849] Thu, 22 May 2008 06:18 Go to previous messageGo to next message
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
Member
You might have already got the solution. If not

accept dept char prompt 'Enter Dept No : '
spool flname;
verify off;
.
.
select .... where detpno=&dept;
.
.
spool off;
verify on;

Quote:
so i was looking into generating an excel sheet without importing as an text file ,


Yes you can do, by defining Oracle ODBC, and
running excel->data->get External Data->New Database Query (type your query over here).

Regards,

MSMallya

[Updated on: Thu, 22 May 2008 06:28]

Report message to a moderator

Re: how to not display substitutions in a spool file [message #324087 is a reply to message #322074] Fri, 30 May 2008 14:36 Go to previous message
melvinRav
Messages: 43
Registered: April 2008
Location: India
Member
Thanks MS mallya,
the
dept char prompt 'Enter Dept No : '
worked , it doesn't show on the csv generated
Previous Topic: how to import data stored in MS-Access to Oracle?
Next Topic: Input truncated coming in the sal generate report
Goto Forum:
  


Current Time: Wed Dec 07 22:22:25 CST 2016

Total time taken to generate the page: 0.10618 seconds