OraFAQ Forum: Unix » sql script from sh script.

Home » Infrastructure » Unix » sql script from sh script. (oracle 9i , solaris)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sql script from sh script. [message #386655]

Mon, 16 February 2009 08:09

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

Report message to a moderator

Re: sql script from sh script. [message #386658 is a reply to message #386655]

Mon, 16 February 2009 08:22

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Please use CODE tags to format the code.
You need to use a "here" document.

Code: [Select all] [Show/ hide]

#!/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

Code: [Select all] [Show/ hide]

sqlplus scott/password@dev5 @somesql.sql

Report message to a moderator

Re: sql script from sh script. [message #386660 is a reply to message #386655]

Mon, 16 February 2009 08:23

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And as stated by the red ball SEARCH BEFORE POSTING.
There are many examples in the Unix forum.

Regards
Michel

Report message to a moderator

Re: sql script from sh script. [message #388897 is a reply to message #386658]

Thu, 26 February 2009 09:44

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.

Code: [Select all] [Show/ hide]

#!/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

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: sql script from sh script. [message #388898 is a reply to message #388897]

Thu, 26 February 2009 09:48

Mahesh Rajendran
Messages: 10708
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

Report message to a moderator

Re: sql script from sh script. [message #388899 is a reply to message #386655]

Thu, 26 February 2009 09:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

sqlplus scott/password@dev5 @somesql.sql;
sqlplus scott/password@dev5 @somesql2.sql
sqlplus scott/password@dev5 @somesql3.sql

Report message to a moderator

Previous Topic:	file creation
Next Topic:	Data Spool

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 10:56:20 CDT 2025