I am trying to fetch the records from oracle database and write on to xls file for that I did the following.
I am trying to run the sql script from hockney:/opt/AMS/eINcent/live=>
File name is salesforceupload.sql
File is in the directory hockney:/opt/AMS/eINcent/live/in=>

My sql script contains

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDD';
select s.OBJECTID, s.SALESFORCEID, c.name AS CHANNELNAME, sfp.name AS SALESFORCEPROFILE, s.status, s.ORGANISATIONNAME, s.ORGANISATIONCONTACTNAME, s.PHONENUMBER, s.EMAILADDRESS, s.STARTDATE, s.RECORDSTARTDATE from salesforce s, channel c, SALESFORCEPROFILE sfp where s.STATUS='active' and s.RECORDSTARTDATE>'20040407' and s.CHANNELID=c.OBJECTID and s.PROFILEID=sfp.OBJECTID;
commit;

to run this sql script I wrote the following shell script

sqlplus eincent/eincent@COMMT01 @ in/salesforceupload.sql > in/salesforce.xls
exit

It is generating the salesforce.xls file but the file contains some error instead of required data.

SQL*Plus: Release 8.1.7.0.0 - Production on Tue Apr 13 19:44:54 2004

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production

SP2-0310: unable to open file ""
SQL>

Query is correct when I am running TOAD it is working fine.

What is wrong I am doing in shell script??
How can I fetch the records and write on to the xls file.

Try:
sqlplus 'eincent/eincent@COMMT01' '@ in/salesforceupload.sql' > in/salesforce.xls

-----------------------------------------------

Thanks for your help
It is working but not as i expect.
The file is containing all the below statements which are not needed in the report
SQL*Plus: Release 8.1.7.0.0 - Production on Wed Apr 14 20:10:02 2004

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production

Session altered.

The data is not in table format.

Column names are repeating again and again.

All the columns are not displaying in the single row and in different column.

And all the data is not displaying in the table format.

OBJECTID SALESFORCEID CHANNELNAM SALESFORCEPROFILE columns are displaying in single column

The following is the sample for one time displayed column heading and data.
This is not in proper table format.

OBJECTID SALESFORCEID CHANNELNAM SALESFORCEPROFILE
---------------
STATUS ORGANISATIONNAME
-------------
ORGANISATIONCONTACTNAME
--
PHONENUMBER EMAILADDRESS STARTDAT
------------- --------
RECORDST
--------
6200 TRT01 SpecRetail Stockist
active Thorpe Supplies (Chatterbox)

I need the table and data in the following format
OBJECTID SALESFORCEID CHANNELNAME SALESFORCEPROFILE STATUS ORGANISATIONNAME ORGANISATIONCONTACTNAME PHONENUMBER EMAILADDRESS STARTDATE RECORDSTARTDATE
6200 TRT01 SpecRetail Stockist active Thorpe Supplies (Chatterbox) 06/23/2003 04/08/2004

Could you please help??

-----------------------------------------------

Column headings will be repeated each page. The number of lines per page is defined by SET PAGESIZE.
The maximum length of a line before it is wrapped is defined by SET LINESIZE.

You could try SET MARKUP HTML to generate HTML-formatted output, which Excel should be able to read.

-----------------------------------------------

Thanks for your advise and help.

I am almost getting the report with your help.

But first line contains the following information with is not needed

SQL*Plus: Release 8.1.7.0.0 - Production on Thu Apr 15 11:17:07 2004 (c) Copyright 2000 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production With the Partitioning option JServer Release 8.1.7.4.0 - Production Session altered.

And second line is blank whihich is not needed.

and last line contains the following information with is not needed

168 rows selected.
Commit complete.
Input truncated to 5 characters
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production With the Partitioning option JServer Release 8.1.7.4.0 - Production

How can I avoid to display them on to report?

-----------------------------------------------

Copy the following script into test.ksh file in UNIX. Then run it.

sqlplus -S dbuser/dbpass@dbbase << EOF
SPOOL /tmp/output.lst
SET HEADING OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET RECSEP OFF
SELECT TABLE_NAME FROM USER_TABLES;
exit;
EOF

This will eleminate un-wanted output and solve your problems.

-----------------------------------------------

Invoke sqlplus with the -s (silent) option to suppress the startup banner and line prompts. SET FEEDBACK OFF to suppress "n rows selected". "Commit complete" etc messages (actually you don't need to commit anyway since you have not changed any data). Ensure the SQL*Plus script ends with a blank line to avoid the "Input truncated" message.
If you do those, do you still get a blank first line?

Btw the .xls extension might be considered misleading since the file is not in MS Excel format. Excel will open .csv files, or even .htm/.html.

-----------------------------------------------

Thanks it works fine.

How can i truncate file extention?

Or

How can i truncate last 4 charectors of a string?

-----------------------------------------------

n ksh:
$ filename=somefile.xls
$ echo $filename

somefile.xls

$ filename=${filename%.*}
$ echo $filename

somefile

Have a Unix Problem
Unix Forum - Do you have a UNIX Question?

Unix Books :-
UNIX Programming, Certification, System Administration, Performance Tuning Reference Books

Return to : - Unix System Administration Hints and Tips