Shell Script to insert value in database
 

Hi,


I wanted to insert a row in database using shell script.. (I do use HSQL and Oracle databases).

How do I write my shell script .. ? How to specify the database details ?

Create a .sql file with the appropriate insert statement.

use sqlplus to run the file, i.e.

I would also re-direct the output (stdout and stderr) to a file and then grep that or error messages (i.e. check for "ORA-").

Bear in mind this will reveal your database password in the process list on the computer, so it is a bad solution from a security standpoint.

A better solution would be to use Perl/DBI to do the whole thing. If you want to do that, google for a Perl/DBI tutorial.

But is it not possible to achieve this using a shell script..?

Cos my application has to be used by a Tester and he wants every time to enter the value in database...

Well, there is no shell API for inserting directly into the database, so you need to use some tool or other. sqlplus is such a tool which works with oracle. I don't know about HSQL - I would assume there is some analogue to sqlplus.

When I say "create an .sql file", you can do that in a script. You didn't say what data needs to be entered, so here's a pretty abstract example:

Thanks...

Let me try this..

btw.. HSQL is a file/memory based developed using Java.. And mainly help developers with easy debug...

There are many scripting engines for Java, you can use one, open a connection to a database and do what you have to do.
Google for JDBC and Groovy, Jython, BSH, Rhino, JRuby, etc.

---------------------

Hi Matthew,

I am new to shell scripting and PL SQL. I tried executing your script. I am encountering an error as ': not a valid identifier

Please suggest me what to be done.

Thanks

Please show your code & full error msg.

Hi Chris,

Below is my code:

read -p "enter value for c1 " c1
read -p "enter value for c2 " c2
read -p "enter value for c3 " c3

cat <<EOD > temp.sql

INSERT INTO emp VALUES ('$c1', '$c2', '$c3');

commit

quit
EOD

sqlplus -s pimco/pimco@pimdev @temp.sql

rm -f temp.sql
----------------------------------------

Error is:


/usr/home/kiran> ./temp.sql
enter value for c1 test1
': not a valid identifier
enter value for c2 test2
': not a valid identifier
enter value for c3 test3
': not a valid identifier
: command not found
---------------------------------------

Please help me

Thanks
Kiran

Kiran,

temp.sql is created within a separate bash shell script file (say "temp_runner.shl").

So temp_runner.shl contains the code that mathewg42 suggested.

It is as below:
Once you create it (temp_runner.shl), test it out and give it to your tester.

Hope this clariufies the error.

End

---------------------------------

Thank you Anantha...It worked :-)

hide password
 

i know it's been a long time since this post, but for what it's worth....

you can use an oracle wallet to hide the password

you can do this from a shell script. You do not have to create and remove a .sql script within the bash script.

nice explanation

But again, that can be done without creating an sql script outside of the bash script to be called.

replace the sql script creation and deletion with the above and you are done. The - in front of EOF ignores white space if you indent your sql commands.