Re: How to run sql scripts programmatically

kcollins15_at_cfl.rr.com (kay c) wrote in message news:<b24d403d.0302031118.78dc44a7_at_posting.google.com>...
> Hi,
> I have no experience with Oracle and could really use some help.
> I have several sql scripts that I need to run during an installation I
> am writting of a custom product. The install assumes Oracle 9i is
> installed before our product is.
> I need to run these scripts to create a database, create tables,
> create stored procedures ect..
> My installation thus far uses primarily jython to do all it files
> manipulation and system configuration.
>
> Is it possible "run" these sql using jython, or any scripting
> language?
> Or is it possible to envoke sqlplus programmatically? I would like to
> run these scripts in the backgound.

You've stumbled on a difficult question. In one sense, it is very simple,
as you can just write batch files (see the oracle installer scripts for examples) or scripts, ie on unix:
#!/bin/ksh
# pass in user and password as arguments, then run whatever commands you want.
user=$1
password=$2
echo '
$user/$password
select * from dual;' |sqlplus -s

See the various faq's for similar methods (orafaq.com, jlcomp.demon.co.uk etc.)

The problem arises if you don't know how big your customers data needs will be ahead of time. Then it becomes very difficult to perform the various dba functions in the scripts. The two basic answers to that are: have some sort of estimator that puts the right amounts in the scripts, or just tell the customers they have to have someone do it. While neither is particularly a good answer, fortunately the more modern Oracle isn't quite so picky given rational defaults. Of course, sometimes a strangely-tuned Oracle will hiccup, so you definitely want to write a script that checks everything is there and compiled. Most products that are for larger customers either tell the customers they need a dba, or look stupid.

Note that there is a hierarchy of oracle groups under the one you posted on.

jg

--
_at_home is bogus.
http://ourworld.compuserve.com/homepages/joel_garry/oraclech.htm