Connecting to oracle database from shell script

Shell Programming and Scripting
1

Hi all,

I am satyakiran , i am new to the forum. i never done shell scripts for connecting to the data base (oracle) and fetching the data from the database( thru sql select statements ) i want to know

  1. how to connect to the data base(oracle) using shell script
  2. how to retrieve data from data base
  3. how to process the data retrieved from database (i.e result set)
  4. how to update the data base in turn.

Please help me out in solving this.

Thanks in advance

sincerely
satya

2

  1. how to connect to the data base(oracle) using shell script
    Look at this post

  2. how to retrieve data from data base
    Post

  3. how to process the data retrieved from database (i.e result set)
    Post

  4. how to update the data base in turn.
    Post

Use the search to look through previous postings. They could help.

Vino

3

Hi,

Like in windows we click the pl/sql icon and we write our code or any sql query to execute.Like wise how to write in unix environemnt and execute .

Hope someone will help me out.

Thanks ...

4

Hi,

Suppose u hav written a script for scheduling job using "crontab",and u r doing ur job.
But according to requirement u need to change the execution of that job to some other time.What is the solution of that.

Actually in an interview someone asked me.I told ,i will change value for time in the crontab.

Waiting for ur reply...

Thanks ....

5

First set up the oracle environment variables, normally by . oraenv or similar.

$ORACLE_HOME/bin/sqlplus
6

I want to know in details that what exactly we need to do to connect , retrieve and to work around the data.

I have seen several reply but no one makes me satisfied .
Please definitive.:slight_smile:

7

sqlplus -s Username/Password@schemaname

8

Hi,

Here's my simple code that may solve your problem.
Lets say you have a 'usr' table with 'user ID, user Name, status (Active(A) or Suspended (S))'

  1. Connection to DB is established wrt DB name in the tnsnames entry
  2. Select/update query will retrive/update the information from the table resp.
#!/bin/ksh
echo "Please enter the User Id of the USER whose status needs to be activated"
read usrId
sqlplus username/password@database_name <<ENDOFSQL
set head off;
set feedback off;
set lines 300;
select * from usr where usrid='$usrId';
update usr set status='A' where usrId='$usrId';
ENDOFSQL
echo " "
echo "User Activated :: `echo $usrId`"
echo " "
exit;

Regards,
Sumedha

9 
echo "User Activated :: `echo $usrId`"

no need to use echo inside echo...

echo "User Activated :: $usrId"

will do...