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 0Then use the following call instead:
$ORACLE_HOME/bin/sqlplus xx/xx@xxxx my_script.sql
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.