Home » Infrastructure » Unix » sql script from sh script. (oracle 9i , solaris)
sql script from sh script. [message #386655] Mon, 16 February 2009 08:09 Go to next message
koff10
Messages: 58
Registered: December 2006
Location: france
Member
Hi all,
I got 2 script: test.sql an test.sh
I'm trying to lunch sql script from sh script.

**test.sh script content is ****:
#!/bin/sh
sqlplus scott/password@dev5;
@test.sql;
exit;

** test.sql script content is ****:
select * from department;
exit
/


From my unix prompt, I launch :

$ ./test.sh **** the result is below.**

SQL*Plus: Release 9.2.0.5.0 - Production on Lu Fev 16 14:44:07 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connecté à :
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
JServer Release 9.2.0.7.0 - Production

SQL>
this means that @test.sql is not execute.
of course test.sql is in my current directory.

Any help welcome.
thanks

koff


Re: sql script from sh script. [message #386658 is a reply to message #386655] Mon, 16 February 2009 08:22 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10626
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Please use CODE tags to format the code.
You need to use a "here" document.

#!/bin/sh
sqlplus scott/password@dev5 <<EOF
@test.sql;
--
-- or you can also direct use sql
select thisColumn from thatTable;
exit;
EOF


other option
is
-- include your exit statements in somesql.sql
sqlplus scott/password@dev5 @somesql.sql
Re: sql script from sh script. [message #386660 is a reply to message #386655] Mon, 16 February 2009 08:23 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And as stated by the red ball SEARCH BEFORE POSTING.
There are many examples in the Unix forum.

Regards
Michel
Re: sql script from sh script. [message #388897 is a reply to message #386658] Thu, 26 February 2009 09:44 Go to previous messageGo to next message
koff10
Messages: 58
Registered: December 2006
Location: france
Member
Mahesh Rajendran wrote on Mon, 16 February 2009 15:22
Please use CODE tags to format the code.
You need to use a "here" document.

#!/bin/sh
sqlplus scott/password@dev5 <<EOF
@test.sql;
--
-- or you can also direct use sql
select thisColumn from thatTable;
exit;
EOF


other option
is
-- include your exit statements in somesql.sql
sqlplus scott/password@dev5 @somesql.sql




In this way how to launch two or many sql script in unix script shell .
example:
sqlplus scott/password@dev5 @somesql.sql;@somesql2.sql; @somesql3.sql


Only @somesql.sql is executed .
thanks
koff
Re: sql script from sh script. [message #388898 is a reply to message #388897] Thu, 26 February 2009 09:48 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10626
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
call all other sql statements inside the somesql.
sqlplus scott/password@dev5 @somesql.sql

somesql.sql should contain
@this.sql
@that.sql
@/this_path/thatsql.sql

Re: sql script from sh script. [message #388899 is a reply to message #386655] Thu, 26 February 2009 09:50 Go to previous message
BlackSwan
Messages: 22793
Registered: January 2009
Senior Member
sqlplus scott/password@dev5 @somesql.sql;
sqlplus scott/password@dev5 @somesql2.sql
sqlplus scott/password@dev5 @somesql3.sql
Previous Topic: file creation
Next Topic: Data Spool
Goto Forum:
  


Current Time: Sun Sep 21 13:30:57 CDT 2014

Total time taken to generate the page: 0.07997 seconds