Home » SQL & PL/SQL » SQL & PL/SQL » redirecting result of a sql query in a file in shell scripting
redirecting result of a sql query in a file in shell scripting [message #210112] Tue, 19 December 2006 07:51 Go to next message
Rony123
Messages: 14
Registered: December 2006
Junior Member
hello
I m very new to shell scripting
during coding a new shell script faced the following program

I want to send the result of a simple query to a file
Ex:
sqlplus -s sername/passwd <<- !
select * from test;
commit;
quit
!

the following code is not working

sqlplus -s sername/passwd <<- !
select * from test;
commit;
quit
! > myfile

can nybody help me regarding this


Re: redirecting result of a sql query in a file in shell scripting [message #210117 is a reply to message #210112] Tue, 19 December 2006 08:11 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Just do a

spool myfile
select * from test;
spool off

[Updated on: Tue, 19 December 2006 08:12]

Report message to a moderator

Re: redirecting result of a sql query in a file in shell scripting [message #210122 is a reply to message #210117] Tue, 19 December 2006 08:32 Go to previous messageGo to next message
Rony123
Messages: 14
Registered: December 2006
Junior Member
still it is not working i used

i m writing a shell script
according to u
i write the code now as:

#!usr/bin/ksh

# just checking

sqlplus -S eot/eot <<- !

spool myfile
Select * from testhil1;
commit;
spool off ;
quit
!

cat miram
cat miram

now the result shold come : two times the result set must be printed instead only one time it is printing..
Re: redirecting result of a sql query in a file in shell scripting [message #210126 is a reply to message #210112] Tue, 19 December 2006 08:51 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Hmm Try something like this

#!usr/bin/ksh
sqlplus -s user/password@db <<-EOF
spool /"your directory"/myfile.txt
select * from testhil1;
spool off
exit
EOF



Re: redirecting result of a sql query in a file in shell scripting [message #210127 is a reply to message #210126] Tue, 19 December 2006 08:58 Go to previous messageGo to next message
Rony123
Messages: 14
Registered: December 2006
Junior Member
Thanks Tahpush!

It worked. But is there any way to stop the printing the result of the query to the console.

Re: redirecting result of a sql query in a file in shell scripting [message #210131 is a reply to message #210112] Tue, 19 December 2006 09:21 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Well try this then, another approach thou

#!usr/bin/ksh

ksh -c "sqlplus -s user/password@db <<-EOF
select * from testhil1;
exit
EOF" > myfile2.txt

There alot of diffrent ways Smile
Re: redirecting result of a sql query in a file in shell scripting [message #210160 is a reply to message #210112] Tue, 19 December 2006 13:29 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Simple

#!usr/bin/ksh
sqlplus -s user/password@db <<-EOF
set termout off
SET verify off
SET feedback off

spool /"your directory"/myfile.txt
select * from testhil1;
spool off
exit
EOF

[Updated on: Tue, 19 December 2006 13:31]

Report message to a moderator

Re: redirecting result of a sql query in a file in shell scripting [message #210236 is a reply to message #210160] Wed, 20 December 2006 00:03 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Bill B wrote on Tue, 19 December 2006 20:29
Simple



Might work in your env settings, not in mine..
Re: redirecting result of a sql query in a file in shell scripting [message #210270 is a reply to message #210236] Wed, 20 December 2006 01:52 Go to previous messageGo to next message
Rony123
Messages: 14
Registered: December 2006
Junior Member
TYhanks Tahpush nd BILL for ur help..
Re: redirecting result of a sql query in a file in shell scripting [message #210271 is a reply to message #210270] Wed, 20 December 2006 01:56 Go to previous messageGo to next message
Rony123
Messages: 14
Registered: December 2006
Junior Member
can u plz help in another problem :
Is it possible to use shell commands like
grep pattern file|cut -f 2 -d" "

within pl/sql block in a shell script
Re: redirecting result of a sql query in a file in shell scripting [message #210273 is a reply to message #210112] Wed, 20 December 2006 02:09 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

No you can't do that.



Re: redirecting result of a sql query in a file in shell scripting [message #210275 is a reply to message #210273] Wed, 20 December 2006 02:13 Go to previous messageGo to next message
Rony123
Messages: 14
Registered: December 2006
Junior Member
Then how to achieve the same functionalities within a pl/sql block.
Re: redirecting result of a sql query in a file in shell scripting [message #210277 is a reply to message #210275] Wed, 20 December 2006 02:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
PL/SQL is meant for manipulating database-data. It is not very strong on OS-files manipulation.
Depending on the structure of your file, you could create an external table (google for that if you don't know the concept). That would offer you the possiblity to do a 'grep' using SQL.
Re: redirecting result of a sql query in a file in shell scripting [message #210278 is a reply to message #210275] Wed, 20 December 2006 02:28 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Listen to Frank Wink

If you show us what you need in your output file we might be able to help you there.

The other option is just to spool your file and exit sqlplus and just modifie the file (in the same script)

ksh -c "sqlplus -s user/password@db <<-EOF
select * from testhil1;
exit
EOF" > myfile2.txt
more myfile2.txt | grep 'whatever' > t1.txt

[Updated on: Wed, 20 December 2006 02:29]

Report message to a moderator

Re: redirecting result of a sql query in a file in shell scripting [message #210280 is a reply to message #210275] Wed, 20 December 2006 02:47 Go to previous messageGo to next message
Admin@Lightsql.Com
Messages: 36
Registered: November 2006
Member

Hillol,

This issue in your code, I think, is the - (minus sign) before for EOF or "!". If you code looks like this, it will work

sqlplus -uid/pwd@dbs <<EOF
select sysdate from dual;
EOF

or

sqlplus -uid/pwd@dbs <<!
select sysdate from dual;
!

If you want to spool output. Here is my demo working code.

################code starts remove this line########
#!/bin/sh
#
# demo ==> Shell script
#
# This is an alert to ban the user logins!
#
sqlplus -s uid/pwd@dbs <<EOF > users.txt
select sysdate from dual;
EOF
mail -s "demo ==> Shell script" youremail@domain.com < users.txt
################code Ends remove this line########

This is just a sample, as you know we can achieve the same results using spool command too.

Good Luck

Jay
Fun Programming with Oracle & Visual C++, Author of http://www.lightsql.com

Re: redirecting result of a sql query in a file in shell scripting [message #210281 is a reply to message #210131] Wed, 20 December 2006 02:54 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Again
tahpush wrote on Tue, 19 December 2006 16:21

There alot of diffrent ways Smile


Re: redirecting result of a sql query in a file in shell scripting [message #210346 is a reply to message #210160] Wed, 20 December 2006 08:03 Go to previous message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Bill B wrote on Tue, 19 December 2006 13:29
Simple

#!usr/bin/ksh
sqlplus -s user/password@db <<-EOF
set termout off
SET verify off
SET feedback off

spool /"your directory"/myfile.txt
select * from testhil1;
spool off
exit
EOF

The set commands are sqlplus commands to stop the echoing of the select to the screen and to stop the actual select being put into the spool file.
Previous Topic: insert into
Next Topic: sql qurey
Goto Forum:
  


Current Time: Sat Dec 03 01:07:35 CST 2016

Total time taken to generate the page: 0.07855 seconds