(Urgent):Creating flat file using sql script and sqlplus from UNIX Shell Script

Hi,

I need help urgently for following issue. Pls help me to resolve this issue.

I am calling sql script file(file1.sql) from UNIX Shell Script(script1.ksh) using sql plus and trying to create flat file that contains all records returned from SQL query in SQL script(file1.sql)

I given sqlplus command as below.

sqlplus -S testuser/testpwd@testserver file1.sql > outputfile.dat

Actually this command is creating flat file with name of outputfile.dat but the file does not contains any records.

The output file contents that generated using sqlplus as below.

Usage: SQLPLUS [ [<option>] [<logon>] [<start>] ] where <option> ::= -H | -V | [ [-L] [-M <o>] [-R <n>] [-S] ]
<logon> ::= <username>[/<password>][@<connect_string>] | / | /NOLOG
<start> ::= @<URI>|<filename>[.<ext>] [<parameter> ...]
"-H" displays the SQLPlus version banner and usage syntax
"-V" displays the SQL
Plus version banner
"-L" attempts log on just once
"-M <o>" uses HTML markup options <o>
"-R <n>" uses restricted mode <n>
"-S" uses silent mode

I need the output file contains the records that is returned from SQL Query in SQL Script(file1.sql)

Pls help me to resolve this problem. it's urgent.

It indicates sqlplus usage is not correct.

Try running it from command line without the redirect option, you should be able to debug from there.

I tried in command line also. I am getting the same thing again.
I executed the same sql query from command line. It's giving the output. but the flat file contains does not have any data.

The output flat file contains data as below

Usage: SQLPLUS [ [<option>] [<logon>] [<start>] ] where <option> ::= -H | -V | [ [-L] [-M <o>] [-R <n>] [-S] ]
<logon> ::= <username>[/<password>][@<connect_string>] | / | /NOLOG
<start> ::= @<URI>|<filename>[.<ext>] [<parameter> ...]
"-H" displays the SQLPlus version banner and usage syntax
"-V" displays the SQL
Plus version banner
"-L" attempts log on just once
"-M <o>" uses HTML markup options <o>
"-R <n>" uses restricted mode <n>
"-S" uses silent mode

Pls help me to solve this problem.

Looking at your output, it clearly says the usage of sqlplus option is not proper.

Try the following

sqlplus << EOF > o.txt
username/password
set linesize 1000
set head off

<your query goes here>

EOF

Thanks for your timely help but i need some more inputs.

I used the same syntax whatever u sent and now the file is created and records are available in file but the file contains header and trailer details also like below.

SQL*Plus: Release 9.2.0.7.0 - Production on Fri Apr 10 05:29:17 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter user-name:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 9

I want to avoid these header & trailer information.

Pls help me to resolve this.

use the silent mode of sqlplus -S

or better - strip off the unwanted information after the file is created

Check the sqlplus "set" options - you can eliminate the unwanted stuff using those (iirc, "set heading off" for example)

BRgds