asked on

Passing parameters to a sql script in Oracle 10g running linux redhat

Afternoon, I am wondering how I can do the following.

I use Oracle sitting on a Linux OS and use scripts quite a lot I would like to improve on there usage by adding parameters to then. The idea is to save me from having to edit the sql script everytime when I want to run it.

I would run this script from within rman to restore logs so have to constantly change the values.

the script is

run{allocate channel ch1 type disk;
restore archivelog from sequence 87700 thread 4 until sequence 87801 thread 4;
release channel ch1;
}

Its not a massively complex script but if when I run it it asked me for the thread and sequence parameters this would be great

or even better if this was converted into an executable which opened up rman on the correct database and ran the sql script with my parameters.

Is this at all possible or am I limited to entering this in by hand.

Any Ideas

Thanks

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DarrenJackson

ASKER

John this is great :)

Can I have the ability of prompts so the script will ask me for THREAD , FROM_SEQ, TO_SEQ

as the below is not fantastic but if this is my only option then fine no problem

script.sh 4 87700 87801

johnsone

You certainly can do that. You would use a combination of the echo and read commands.

My opinion would be that isn't the UNIX way to do things. You really should be using parameters like -f -d and -t (or whatever letters you want). For that you would use the getopts (I believe this is a shell built in) or you can use the getopt command (typically /usr/bin/getopt, which would allow for multi character parameter names rather than getopts single character). Search around the web, there are plenty of great examples of using both.

DarrenJackson

ASKER

John again thank you

I thought I would just test this by doing a very simple command but it isn't working

Where am I going wrong

#!/bin/sh

Database=$1

<< EOF
export ORACLE_SID=${Database}
EOF

johnsone

What are you trying to do? Just set the variable? That would be this:

#!/bin/sh

Database=$1

export ORACLE_SID=${Database}

Open in new window

Remember that a script runs in a separate process, so that change will not be visible in your environment once the script completes.

DarrenJackson

ASKER

Ahh of course. Thanks John Ill test.

Regards

DarrenJackson

ASKER

Thank you John works great