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


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting (Urgent):Creating flat file using sql script and sqlplus from UNIX Shell Script
# 1  
Old 04-10-2009
MySQL (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 SQL*Plus 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.
# 2  
Old 04-10-2009
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.
# 3  
Old 04-10-2009
MySQL Creating flat file using SQLPLUS from UNIX Shell Script

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 SQL*Plus 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.
# 4  
Old 04-10-2009
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
# 5  
Old 04-10-2009
MySQL Create the Flat file using SQLPLUS in UNIX Shell Script

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.
# 6  
Old 04-10-2009
use the silent mode of sqlplus -S

or better - strip off the unwanted information after the file is created
# 7  
Old 04-15-2009
Check the sqlplus "set" options - you can eliminate the unwanted stuff using those (iirc, "set heading off" for example)

BRgds
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to execute 10 sql files from a folder through sqlplus in shell script?

I am new to shell scripting and i want to know how to execute the *.sql files from a folder through sqlplus in shell script and files should be execute in sequentially one by one while execution if any ORA error it has to exit from sqlplus session 1) scripts from external folder 2) logs has... (1 Reply)
Discussion started by: sreekanth Reddy
1 Replies

2. Shell Programming and Scripting

Oracle/SQLPlus help - ksh Script calling .sql file not 'pausing' at ACCEPT, can't figure out why

Hi, I am trying to write a script that calls an Oracle SQL file who in turns call another SQL file. This same SQL file has to be run against the same database but using different username and password at each loop. The first SQL file is basically a connection test and it is supposed to sort... (2 Replies)
Discussion started by: newbie_01
2 Replies

3. Shell Programming and Scripting

How to pass Oracle sql script as argument to UNIX shell script?

Hi all, $ echo $SHELL /bin/bash Requirement - How to pass oracle sql script as argument to unix shell script? $ ./output.sh users.sql Below are the shell scripts and the oracle sql file in the same folder. Shell Script $ cat output.sh #!/bin/bash .... (7 Replies)
Discussion started by: a1_win
7 Replies

4. Shell Programming and Scripting

SQL query in UNIX script - output in flat file

Hi, I never did this before... what I want to do is execute a SQL query from a unix script and redirect sql query's output to a flat file (comma separated one) without the header info (no column names). I would also want not to print the query's output to the screen. snapshot of my script:... (13 Replies)
Discussion started by: juzz4fun
13 Replies

5. UNIX and Linux Applications

how to execute multiple .sql scripts from within a shell script using sqlplus

using sqlplus I want to execute a .sql script that has dbms_output statments in rhe script. I want to write the dbms_output statements from .sql file to a log file. is this possible. thanks any help would be appreciated :wall: (1 Reply)
Discussion started by: TRS80
1 Replies

6. UNIX for Dummies Questions & Answers

calling a unix shell script from sqlplus

I want to execute a shell script from sqlplus prompt and get its output back to sqlplus. Is this possible? if yes just give me an example for doing that. (2 Replies)
Discussion started by: boopathyvasagam
2 Replies

7. Shell Programming and Scripting

URGENT: cron job not running the sqlplus command in shell script

cron job not running the sqlplus command in shell script but the shell script works fine from command line.. Cronjob: 5 * * * * /home/dreg/script.sh script.sh: #!/bin/ksh /oracle/u000/app/oracle/product/10204/GEN/bin/sqlplus -s <user>/<pass>@<sid/home/dreg/sqlscript.sh ... (18 Replies)
Discussion started by: Ikea
18 Replies

8. Shell Programming and Scripting

urgent-extracting block data from flat file using shell script

Hi, I want to extract block of data from flat file. the data will be like this start of log One two three end of log i want all data between start of log to end of log i.e One two three to be copied to another file. This particular block may appear multiple times in same file. I... (4 Replies)
Discussion started by: shirish_cd
4 Replies

9. Shell Programming and Scripting

help me in sending parameters from sqlplus script to unix shell script

Can anybody help me out in sending parameters from sql*plus script to unix shell script without using flat files.. Initially in a shell script i will call sql*plus and after getting some value from some tables, i want that variable value in unix shell script. How can i do this? Please tell me... (2 Replies)
Discussion started by: Hara
2 Replies

10. Shell Programming and Scripting

How To create Flat Files using Unix Shell Script?

Hi all, How to create Flat Files using Unix Shell Script. The Script is supposed to be sheduled to run at a particular time? Thanks in advance Appu (4 Replies)
Discussion started by: Aparna_k82
4 Replies
Login or Register to Ask a Question