I am running a shell scipt which connects to oracle and runs a select query and display data in above format (screenshot attached). I am redirecting this data to a file using spool command. How can I have data in a tabular format in my file like excel with a headers.
I have tried below options but its not working properly
set feedback off
set pages 0
set verify off
set pagesize 0 embedded on
SET COLSEP ","
SET LINESIZE 200
SET UNDERLINE =
set markup on
set termout off
BREAK ON ROW SKIP PAGE
I usually spool it into a file with a .TXT extension. In MS Excel, while opening that file, the Text Import Wizard opens and asks how to do that (which line is the 1st, which delimiter I used, ...) and everything fits into its own column.
If you save it as a .CSV, Excel will open it, but put all contents into column "A" (which is what you don't want).
This is my SET:
set termout off
set trimspool on
set echo off
set verify off
set autoprint off
set serveroutput off
set arraysize 1000
set pagesize 0
set linesize 100
set long 10000
set numwidth 10
set feedback off
set colsep ';'
If you are using version 12.2, just do
Set markup CSV
You can grab SQLcl which will run all normal sqlplus things but has added in lots more above that.
Here's a quick example for what you are looking for.
sql klrice/klrice
SQLcl: Release 18.1 Production on Wed Feb 07 17:28:32 2018
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Last Successful login time: Wed Feb 07 2018 17:28:56 -05:00
Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
SQL> set feed off
SQL> set sqlformat csv
SQL> spool users.csv
SQL> select * from user_objects;
...rows.....
SQL> spool off
set markup ondoesn't do it for you? Please specify "it's not working properly". How is not working? Remember, you can see your screen and we can't. We only know what you tell us.