Q
Problem solve Get help with specific problems with your technologies, process and projects.

SELECT statement showing up incorrectly at the beginning of the flat file

I want to create a flat file from an Oracle table using SQL*Plus. The output to the flat file works fine, except that at the beginning of the flat file I get the select statement. How can I prevent this from happening? Here is my script:

$ORACLE_HOME/bin/sqlplus xx/xx@xxxx <<EOF > /dev/null
whenever sqlerror exit 1 
whenever oserror exit 2 
set 	NEWPAGE	0
set 	SPACE	0
set 	LINESIZE 1397
set     PAGESIZE 0		
set     ECHO OFF		
set     FEEDBACK OFF	
set     HEADING OFF
set	MARKUP HTML	SPOOL OFF
set	SQLPROMPT	''	
spool 	/tmp/contact.txt
-- sql select statement (did not place here because it's rather large)
spool 	off
set	termout ON
exit	0
EOF

The SET ECHO OFF command is what would prevent the statement from showing up BUT it must be issued from within a script and not in a line by line mode as you are doing.

Simply take all the lines of code you currently have and put them in a script file as follows:

-- my_script.sql
set ECHO OFF		
whenever sqlerror exit 1 
whenever oserror exit 2 
set NEWPAGE	0
set SPACE	0
set LINESIZE 1397
set PAGESIZE 0		
set FEEDBACK OFF	
set HEADING OFF
set	MARKUP HTML	SPOOL OFF
set	SQLPROMPT	''	
spool /tmp/contact.txt
-- sql select statement 
spool off
set	termout ON
exit 0
Then use the following call instead:
$ORACLE_HOME/bin/sqlplus xx/xx@xxxx my_script.sql

For More Information


Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close