topic SQL scripts in Operating System - HP-UX

SQL scripts

Fritz-Gerald: Zephir — Wed, 05 Jun 2002 15:02:59 GMT

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?

Re: SQL scripts

Duncan Edmonstone — Wed, 05 Jun 2002 15:08:08 GMT

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

Re: SQL scripts

harry d brown jr — Wed, 05 Jun 2002 15:09:52 GMT

Use perl:

http://www.bookpool.com/.x/3dp5s6acd6/sm/1565926994

examples:

http://www.oreilly.com/catalog/perldbi/chapter/ch04.html

live free or die
harry

Re: SQL scripts

Fritz-Gerald: Zephir — Wed, 05 Jun 2002 15:25:48 GMT

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

Re: SQL scripts

Brian Crabtree — Thu, 06 Jun 2002 01:27:25 GMT

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

Re: SQL scripts

Victor Geere — Thu, 06 Jun 2002 05:43:00 GMT

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

Re: SQL scripts

Fritz-Gerald: Zephir — Thu, 06 Jun 2002 13:31:17 GMT

SQL Syntax is correct,
I actually work around it by creating a sql file and call it from sql.