How to use sqlplus command in UNIX

Eclipse Eclipse ecl-sc1
March 8, 2010 at 05:46:55
Specs: Linux/Ubuntu
Friends,
I need your help!
I am using the command below to connect to sql, run the queries and get the o/p in a file( InventoryReport.xls) with .xls extension.
I use the uuencode filename file name | mailx -s "Subject" email ID.I get the mail.
Now the problem is I don't get the excel in the desired format.It looks overlapped and the queries,words come along. I want the heading separately and one column /heading
I want to have 16 columns in the excel sheet neatly.
I used the below query.

sqlplus << PLUS_END $DBUSERNAME/$DBPASWD@DBNAME > InventoryReport.xls
SET NEWPAGE 0
SET SPACE 1
SET LINESIZE 150
SET PAGESIZE 0
SET ECHO ON
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SELECT h.ap_vend_id, V.NAME1, h.ap_inv_id, h.ap_inv_dt, h.INV_ent_dt, h.src_sys_cd, a.gl_acct_nbr, a.gl_dept_nbr, a.gl_prod_nbr,

a.gl_loc_nbr, a.gl_dept_nbr, a.tot_acct_amt, a.tot_prc_amt, (((a.tot_prc_amt-tot_acct_amt)/tot_prc_amt)*100),

h.AP_POST_DT, h.INV_POST_STAT_CD

from ap_inv_hdr h, ap_acct a, PS_VENDOR V

WHERE H.AP_INV_SEQ_ID = A.AP_INV_SEQ_ID
AND H.LST_ACCTG_DT = A.LST_ACCTG_DT

AND H.INV_ENT_DT ${Lookup_Dt}

AND H.AP_INV_TYP_CD = 'DSD'

AND A.GL_LOC_NBR in ${strnum}
AND tot_prc_amt <> 0

AND VENDOR_ID = AP_VEND_ID;

I have also tried with SET HEADING on but still i get the same overlapped ugly excel.

When i run in ORACLE DB i get in a perfect tabular format in the excel.I want to get the same here as well
I would highly appreciate if you could help me with this.

Thanks for your time and help in advance.


See More: How to use sqlplus command in UNIX

Report •

#1
March 8, 2010 at 06:50:02
Have you tried one of the methods to save it as a .csv file?

Report •

#2
March 8, 2010 at 07:04:23
No i have not tried. I am not sure how to do it and again convert it to Excel and send it from command line.
All in UNIX.I am pretty new to this.

Report •

#3
March 8, 2010 at 08:47:55
You just can't call something an .xls file and expect it to be an .xls file. That'd be like me getting a free ride in Airforce One because I declared myself the President of the United States of America. You need to have a standard, text baised format that Ecel reconizes. CSV is one of those standards. Here's a working example from stackoverflow.com

Report •

Related Solutions

#4
March 9, 2010 at 02:17:18
I did use csv. The oputput sent across mail is better but yet not as desired.
When i run the sqlplus in my machine and spool it as .csv and open it from the location(where it was stored) it looks good.
But when I try to save as .csv extension in the UNIX and send it from command line it does not look the same.

Report •

#5
April 5, 2010 at 04:19:49
Hi,

Can you try if this helps. The idea is to output to a .csv file, and also to format the output of your SELECT statement to have proper commas.

sqlplus << PLUS_END $DBUSERNAME/$DBPASWD@DBNAME > InventoryReport.csv
SET NEWPAGE 0
SET SPACE 1
SET LINESIZE 150
SET PAGESIZE 0
SET ECHO ON
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SELECT h.ap_vend_id||','||V.NAME1||','||h.ap_inv_id||','|| h.ap_inv_dt||','||h.INV_ent_dt||','||h.src_sys_cd||','||a.gl_acct_nbr||','||a.gl_dept_nbr||','||a.gl_prod_nbr||','||a.gl_loc_nbr||','||a.gl_dept_nbr||','||a.tot_acct_amt||','||a.tot_prc_amt||','||(((a.tot_prc_amt-tot_acct_amt)/tot_prc_amt)*100)||','||h.AP_POST_DT||','||h.INV_POST_STAT_CD
FROM ap_inv_hdr h, ap_acct a, PS_VENDOR V
WHERE H.AP_INV_SEQ_ID = A.AP_INV_SEQ_ID
AND H.LST_ACCTG_DT = A.LST_ACCTG_DT
AND H.INV_ENT_DT ${Lookup_Dt}
AND H.AP_INV_TYP_CD = 'DSD'
AND A.GL_LOC_NBR in ${strnum}
AND tot_prc_amt <> 0
AND VENDOR_ID = AP_VEND_ID;

PLUS_END


Report •

#6
April 5, 2010 at 05:57:05
Thnks,

I will try and get back.


Report •

#7
April 20, 2010 at 08:55:29
Hi virgil,
Sorry for the delay.
When i tried to run the sql query it says
ORA-00911: invalid character
00911. 00000 - "invalid character"

Report •

Ask Question