Export and Create Excel File from Oracle SQL Query Spool Output File

Most of the time, it is a common requirement to export the SQL*Plus spool output to the excel spreadsheet. Earlier I used to spool the SQL output to a text file and I use convert text data to Excel columns. It requires some additional effort. Please see the demo for simple method to create Excel file from sql spool output.


sqlplus scott/tiger
set feedback off
set markup html on spool on
alter session set nls_date_format='mm-dd-yyyy';
spool objects.xls
select object_name,object_type,created from user_objects ;
spool off
set markup html off spool off

SQL> set feedback off
SQL> set markup html on spool on
SQL> alter session set nls_date_format='mm-dd-yyyy'
<br>
  2  ;
<br>
SQL&gt; spool objects.xls
<br>
SQL&gt; select object_name,object_type,created from user_objects ;
<br>
<p>
<table border='1' width='90%' align='center' summary='Script output'>
<tr>
<th scope="col">
OBJECT_NAME
</th>
<th scope="col">
OBJECT_TYPE
</th>
<th scope="col">
CREATED
</th>
</tr>
<tr>
<td>
PROCEMP1
.. .. ..
<p>
SQL&gt; spool off
<br>
SQL&gt; set markup html off spool off
<br>
<br>
SQL>
Open the objects.xls file in excel. The output will be as below.


Is it simple? J
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google