Starting sql scripts from shell script

Dev Shed Forums Sponsor:

SlickEdit: Code in over 40 languages across 7 platforms. SlickEdit’s unmatched power, speed, and flexibility allows even the most accomplished developers to write better code faster. Download a free trial today!

June 25th, 2004, 08:00 PM

dvskid

Registered User

Join Date: Jun 2004

Posts: 5

Time spent in forums: < 1 sec
Reputation Power: 0

Starting sql scripts from shell script

Hi

I am not a DBA and I have to help someone at work so:

Basically, there are X number of .sql files that have to be run with a specific value for the wirecenter. Then, once the sql script is run, the output has to be captured to a csv file. The platform is oracle 8 on a solaris box (don't have version).

So I am supposed to write a script that:

1. Fires off all the .sql scripts.
2. Passes them the value of the wire center which is used to query the DB.
3. Capture the results of each sql script in a CSV file.
4. Highlight how much time it took the queries to execute.

Anyone want to help me with this?

Thanks!

June 27th, 2004, 11:58 PM

galith_haham

Contributing User

Join Date: Feb 2004

Posts: 64

Time spent in forums: 3 h 25 m 17 sec
Reputation Power: 5

1. Fires off all the .sql scripts. - create another sql files which contains the folowing :

@/xxxx/xxxx/xxxx/sqlscript.sql
....
2. Passes them the value of the wire center which is used to query the DB.
in order to pass a parameter your sql script will need to accept it (aaaa is the parameter value):

@/xxxx/xxxx/xxxx/sqlscript.sql 'aaaa'
and the sqlscript will be something like:

select '&1' from dual;
3. Capture the results of each sql script in a CSV file. - in order to create a file from an sql script you must create a spool:

spool /xxxx/xxxx/xxx/sqlscript.csv
@/xxxx/xxxx/xxxx/sqlscript.sql
spool off
4. Highlight how much time it took the queries to execute.
- set timing on will highlight how mach time a command/sql script took:

set timing on
spool ...
@ ....

Good luck,
Galit.

June 28th, 2004, 09:34 AM

jim mcnamara

......@.........

Join Date: Jun 2004

Posts: 1,307

Time spent in forums: 1 Week 3 Days 4 h 28 m 57 sec
Reputation Power: 48

Code:

#!/bin/ksh
# $1 = username
# $2 = password

UIPW=$1"/"$2
PrntCode=$(echo "$UIPW
           set pages 0          
           @/home/myself/mysqlscript.sql |
           $ORACLE_HOME/bin/sqlplus -s )

June 28th, 2004, 11:45 AM

dvskid

Registered User

Join Date: Jun 2004

Posts: 5

Time spent in forums: < 1 sec
Reputation Power: 0

Hi Galith and Jim,

Thanks for the reply.

This is what I did:

Quote:

#! /bin/sh

sqlplus -s scott/tiger@DatabaseName << ENDSQL

WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10

SPOOL ${SCI_PORT_OLD.csv}
@create table SCI_PORT_OLD.sql
SPOOL OFF

SPOOL ${CREATE_CLINK.csv}
@Create clink.sql
SPOOL OFF

SPOOL ${FMS_Before_Picture.csv}
@FMS Before Picture.sql "Value_for_Wirecenter"
SPOOL OFF

SPOOL ${Update_FMS_PORT.csv}
@update FMS.SCI_PORT.sql
SPOOL OFF

SPOOL ${FMS_After_Picture.csv}
@FMS After Picture.sql "Value_For_Wirecenter"
SPOOL OFF

SPOOL ${REPORT_FMS_Fallout.csv}
@REPORT_FMS_Fallout.sql "Value_For_Wirecenter"
SPOOL OFF

SPOOL ${REPORT_FMS_NON_CONVERSION.csv}
@REPORT_FMS_Non_Conversion.sql "Value_For_Wirecenter"
SPOOL OFF

SPOOL ${DROP_VANC_CLINK.csv}
@DROP VANCOUVER_CLINK.sql
SPOOL OFF

EXIT;
ENDSQL

What do you think?

Thanks!

June 29th, 2004, 12:57 AM

galith_haham

Contributing User

Join Date: Feb 2004

Posts: 64

Time spent in forums: 3 h 25 m 17 sec
Reputation Power: 5

Hi,

I think that will do the work.

Galit.

June 29th, 2004, 10:36 AM

dvskid

Registered User

Join Date: Jun 2004

Posts: 5

Time spent in forums: < 1 sec
Reputation Power: 0

Quote:

Originally Posted by galith_haham

Hi,

I think that will do the work.

Galit.

Thank you.

If I have any other question on this, I'll ask.

Btw: I need to do the exact same thing in Sybase: do you have experience with Sybase?

June 29th, 2004, 11:47 PM

galith_haham

Contributing User

Join Date: Feb 2004

Posts: 64

Time spent in forums: 3 h 25 m 17 sec
Reputation Power: 5

Hi,

don't know sybase (only oracle,db2 and sql server).

Galit.

July 2nd, 2004, 09:20 AM

dvskid

Registered User

Join Date: Jun 2004

Posts: 5

Time spent in forums: < 1 sec
Reputation Power: 0

Quote:

Originally Posted by galith_haham

Hi,

don't know sybase (only oracle,db2 and sql server).

Galit.

No problem.

I have another question about using sqlldr in my script.

I have this line:

Quote:

SQLLDR USERID=fxxx/fxx@fms_dev control=x.ctl data=x.DAT log=x.log DIRECT=false

Everything else works but when I run the script, the log file says "SP2-0734: unknown command beginning "SQLLDR USE..." - rest of line ignored."

Do you see anything wrong with the way I am calling sqlldr? This is k-shell btw.

Thank you!

July 2nd, 2004, 09:55 AM

dvskid

Registered User

Join Date: Jun 2004

Posts: 5

Time spent in forums: < 1 sec
Reputation Power: 0

I got the problem.

The problem was that i was calling sqlldr from within the sqlplus section of the shell script.

Doing some googling told me that sqlldr is a separate application and has to be run directly from the prompt.

So I placed the sqlldr section outside of the STOP block.

Thanks!

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: