using an shell script how to change a value in a file according to the user input

badrinath_tcs · 09-28-2009, 02:51 AM

Hi Friends ,

I have a file called a.sql which I need to execute in a database and the script I am calling in a shell script .Before executing the script I have to change a value in the script according to the user input.How can I do this .

Eg...

A.sql file contains an sql query like this
select * from employee
where salary>Enter_salary
order by salary desc;

and My shll script is like this
#!bin/sh
Echo enter the salary
read salary

sqlplus user@databse

@path/a.sql
commit ;

exit;

exit 4;

When executed the script it asks for enter salary and if the user has input 5000 then the Enter_salary value in a.sql must be replaced by 5000.

Please help me on this how can i achieve the same ..

Thanks
Badrinath

catkin · 09-28-2009, 03:15 AM

The echo command is "echo" not "Echo".

Are the following lines intended as input to the sqlplus command?

Code:

@path/a.sql
commit ;

exit;

If so then a shell "here document" may work (I do not know the sqlplus command but most work that way)

Code:

sqlplus user@databse <<EOF

@path/a.sql
commit ;

exit;
EOF

You have no place in what I think is the sqlplus input to put the salary. Is it in the path/a.sql file? If so, your script needs to edit path/a.sql, probably using sed and creating a temporary file.

Why are you exiting the script with return code 4? Non-zero is conventionally used to indicate an error.

It is easier to read you code if you put it in code tags.

badrinath_tcs · 09-28-2009, 03:22 AM

Hi Catkin,

Thanks for your reply and pointing some errors ..

The main problem I am facing is that I have to change the value of Enter_salary in a.sql file to 5000 i.e the input of the user in run time when executed the shell script ,,and which is stored in the shell variable..

Apart from this I don't have any problem ..Could you assist how can i do this ..

badrinath_tcs · 09-28-2009, 03:32 AM

I used the sed in the below format but it did not worked ,It replaced the value Enter_salary in a.sql file to $salary not the value that is stored in salary variable

echo "Enter salary"
read salary
sed 's/Enter_salary/$salary/g a.sql >a1.sql
mv a1.sql a.sql

catkin · 09-28-2009, 03:59 AM

Quote:

Originally Posted by badrinath_tcs

I used the sed in the below format but it did not worked ,It replaced the value Enter_salary in a.sql file to $salary not the value that is stored in salary variable

echo "Enter salary"
read salary
sed 's/Enter_salary/$salary/g a.sql >a1.sql
mv a1.sql a.sql

The single quotes prevent variable substitution (and you missed the closing quote -- or maybe what you posted is not what you ran, because it would not have run). Please use copy and paste and code tags (that's a link so it's easy for you to read about code tags) so we can best see what you are doing.

Try this

Code:

sed "s/Enter_salary/$salary/g" a.sql > a1.sql

You probably want to keep the template a.sql file for next time so better to keep it rather than overwrite it. Even clearer call it a.template.sql and modify it into a.sql

Code:

sed "s/Enter_salary/$salary/g" a.template.sql > a.sql

badrinath_tcs · 09-28-2009, 04:10 AM

Thanks a lot ..it is working now .

chrism01 · 09-28-2009, 07:29 PM

Use the proper SQLPlus syntax: http://www.orafaq.com/wiki/SQL*Plus_..._SQL.2APlus.3F