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. Please help if any body now Thanks in advance Rao

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

Hello, 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?? Thanks in advance Rao

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.

Hello WilliamRobertson, 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? Can you help me? Very much thanks in advance Rao

Hi folks, 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. Thanks. Mohammed

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.