cancel
Showing results for 
Search instead for 
Did you mean: 

SQL scripts

SQL scripts

In shell scripts, I often call SQL like this :
sqlplus user/passw << END_SQL
select *
from table
EMD_SQL

I found that this doesn't work, mostly if there are multiple calls from the same script
Does any one know the conditions?
If it ain't rough it ain't me
6 REPLIES
Duncan Edmonstone
Honored Contributor

Re: SQL scripts

I never had any problem with this, even with multiple calls in one script. You have made sure that there are no indents between the two 'END_SQL's..'

One point though... I'd always end each SQL statement with a ';' as in:

sqlplus user/passw << END_SQL
select *
from table ;
END_SQL

HTH

Duncan

HTH

Duncan
harry d brown jr
Honored Contributor

Re: SQL scripts

Re: SQL scripts

The syntax is correct, Im quite sure.
I fI cut and paste it all in a new file, it works.

It's like after x number of runs, it wont work


P.S. If I lived in a better place, then I would be allowed PERL
If it ain't rough it ain't me
Brian Crabtree
Honored Contributor

Re: SQL scripts

One option, rather than piping the information, would be to create a sql script, or have a set prebuilt, that you can call from the command line:

sqlscript.sql
connect scott/tiger
select * from table;
exit;
---
sqlplus /nolog @sqlscript.sql
-or-
sqlplus scott/tiger @sqlscript.sql

Either way will get you out of piping information directly to the command. One thing that I have found that can cause problems in the past (for me) is not outputting the information to something.

ie:
sqlplus <> /dev/null
connect internal
select * from table;
!

Brian
Victor Geere
Frequent Advisor

Re: SQL scripts

The ; does it for me, but also make sure that you do not have empty lines in your script between statements.
I thought that I was because I had thought.

Re: SQL scripts

SQL Syntax is correct,
I actually work around it by creating a sql file and call it from sql.
If it ain't rough it ain't me