Creeating Korn Shell to run 2 SQL scripts

[txwylde]

I am trying to create a script that will run 2 sql scripts in Korn shell but a little lost. Does anyone have any examples that I can use as a guide? The 2 SQL scripts are called loopers_fiscal.sql and looper_query_v2.sql.

Thanks!
Bill

 

[SantaMufasa]

TX,

Here is an example of my invoking a sql script (that I named txwylde.sql and stored in the /tmp directory). The txylde.sql script simply contains the query:

select * from dual;

Here is the invocation from my *nix prompt:

[server]:/dbsrv$ sqlplus username/password @/tmp/txwylde

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Feb 5 14:09:21 2007

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


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production


D
-
X

SQL>

...which generates the correct output.

The contents of the script, txwylde.sql, could just as easily containted invocations of your two scripts:

% cat /tmp/txwylde.sql

@/<path>/loopers_fiscal.sql
@/<path>/looper_query_v2.sql

Let us know if this helps guide you to a solution that meets your needs.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[txwylde]

Thats one way. I wanted to create a chron job and have this script run every week.

Is there a different way to do it?

Thanks!
Bill

 

[SantaMufasa]

Yes, TX,

You can set up a cron job to do the same thing, either directly in the cron-job definition or have the command embedded in a .sh file that the cron-job runs.

Have you not tried that yet as a test?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[stefanhei]

Bill,
keep in mind, that cronjobs run in a restricted environment: the userprofile (~/.profile) is not called when the job starts.
Given this I'd recommend Dave's .sh file approach and set the environment variables ($ORACLE_HOME, $ORACLE_SID) in that script before calling sqlplus.

Stefan

 

[KenCunningham]

Stefan's quite right. I'd also add the appropriate PATH variable for belt and braces purposes.