| how to not display substitutions in a spool file [message #316849] |
Mon, 28 April 2008 09:58  |
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 #316858 is a reply to message #316853] |
Mon, 28 April 2008 10:47  |
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 #316872 is a reply to message #316867] |
Mon, 28 April 2008 12:00 |
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 #316877 is a reply to message #316873] |
Mon, 28 April 2008 12:55 |
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 |
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 |
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
