Connecting to Oracle DB using sqlplus

shrutihardas · May 4, 2010, 4:03pm

Hi,

I am very new to shell scripting and trying to write a simple shell script in which i am trying to achieve the following:

Connect to oracle database hosted on a different server
fire a query on the oracle db
store the output in a variable
use this variable for further logic

The code that i have written so far is as follows:

#!/bin/bash

#Connect to the database using sqlplus

sqlplus 'DBUSER/dbuser@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.5.199.106)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=PHYGD1)))' >> outputlogfile.log

# Fire the query on database

DCOFCVALUE=select sync_cmpl
from table(dbdev.db_inv_dtl_hdr)
where dc_id ='30'
and to_char(sync_req_dttm,'YYYYMMDD')= '20100331';

ENDSQL

# Check if DCOFCVALUE  was succesful or not
if [ $DCOFCVALUE = Y ]
then
    echo "DCOFCVALUE  Succesfull\n"
  exit 2
else
  if [ $DCOFCVALUE = N]
  then
     echo "DCOFCVALUE  Unsuccesfull\n"
  exit 3
  fi
fi

exit 0

Now the error message i am getting is as follows:

line 29: sync_cmpl: command not found
.line 30: syntax error near unexpected token `('
line 30: `from table(dbdev.db_inv_dtl_hdr)'

Could someone please guide where am I going wrong? And what is the correct way to write such a code.

Your help is highly appreciated!

Regards,
Shruti

methyl · May 4, 2010, 5:00pm

Unfortunately you are so far adrift that it is hard to guess what you are trying to achive. There are multiple significant errors both the Unix shell syntax and the Oracle command syntax.

The sqlplus line posted looks like an extract from an Oracle listener log. It is not a valid sqlplus command line at all.

Maybe try using the search facility on this site to look for examples of Oracle sqlplus commands within unix shell. Don't forget to set the Oracle environment variables such as ${ORACLE_HOME}.

In this context if you can type the commands at a command prompt they can be turned into a unix shell script.
Tip: First try getting the command sequence to work manually before trying to script it.

durden_tyler · May 4, 2010, 5:39pm

Actually, it is possible to connect to the Oracle database by specifying the connect descriptor from the tnsnames.ora file. But it's not a secure way of connecting since all important pieces of information (server name, port etc.) are exposed in the process list when connecting this way.

@OP - find out the net_service_name that corresponds to this connect descriptor. It may be "PHYGD1" or something else - depends on what your Oracle DBA set it to during installation, so you may want to talk to him/her.
Once it is known, you can connect as "username/password@net_service_name". Or better, set the ORACLE_SID variable to that value and connect as "username/password".

In my case, I've set my ORACLE_SID to "ora11g", which is the net_service_name in my tnsnames.ora for my local database. So I can connect as "test/test".

Have a look at the script below; it should give you some idea -

$ 
$ 
$ # First, let's check what we have in the table "T" of schema "TEST" of my local database
$ (echo "select * from t;") | sqlplus -s test/test

     X
----------
       100

$ 
$ # So, there's just one column "X" and one row and the one value in the table is 100
$ # Now show the contents of the shell script that connects to this database and
$ # fetches this value
$ 
$ cat -n fetch_value.sh
     1    #!/bin/bash
     2    
     3    #Connect to the database using sqlplus
     4    x=`sqlplus -s /nolog <<EOF
     5    connect test/test
     6    set pages 0 
     7    select x from t;
     8    exit
     9    EOF`
    10    
    11    # print the value of x fetched from the database
    12    echo "Value fetched from database, x = $x"
    13    
    14    # check if this value equals 100
    15    if [ $x == 100 ]; then
    16      echo "Yes, the value of x is 100"
    17    else
    18      echo "No, the value of x is $x"
    19      return 1
    20    fi
    21    return 0
    22    
$ 
$ # Now run the script
$ . fetch_value.sh
Value fetched from database, x =        100
Yes, the value of x is 100
$ 
$

The SQL syntax of your script looks suspicious:

...
from table(dbdev.db_inv_dtl_hdr)
...

If "dbdev.db_inv_dtl_hdr" is a regular (heap) table i.e. one that has been created using the "create table dbdev.db_inv_dtl_hdr (blah...);" syntax then your syntax should be -

...
from dbdev.db_inv_dtl_hdr
...

As a matter of fact, there is a "table()" operator in Oracle that casts a nested table to a regular table. However, that type of syntax is typically used by advanced Oracle programmers and somehow I believe that's not the case here.

HTH,
tyler_durden

curleb · May 4, 2010, 5:45pm

to paraphrase methyl above: baby steps...

Try to run this code on the remote machine (assuming your Connect String is accurate):

sqlplus 'DBUSER/dbuser@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.5.199.106)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=PHYGD1)))' <<ENDSQL >> outputlogfile.log

--# Fire the query on database

select 
         sync_cmpl
from 
         dbdev.db_inv_dtl_hdr
where 
         dc_id 
            =        '30'
   and   to_char(sync_req_dttm,'YYYYMMDD')
            =        '20100331'
;

ENDSQL

If it works...you can move ahead with attempting to call it remotely, etc...

palanisvr · May 5, 2010, 5:27am

Connect to oracle database hosted on a different server
fire a query on the oracle db
store the output in a variable
use this variable for further logic

Try this ...

##Intiliase the required variables....
 
USERID='SCOTT'
USERPWD='TIGER'
dbname='US.ORACLE.COM' 

##Spool the query output into a unix variable 

Output_variable=`sqlplus -s $USERID/$USERPWD@$dbname <<!
set heading off feedback off trimspool on 
--your query here;
select sysdate from dual;
exit
!`

echo Output_variable : $Output_variable

--> use the variable to add your own logic

shrutihardas · May 5, 2010, 12:38pm

Hi All,

Thanks for your responses!

As per the suggestions below i thought of taking 1 step at a time by doing the following:

Checked all environment variables are set in my .bash_profile file. $ORACLE_HOME and $ORACLE_SID returns correct results.
I tried to connect to SQLPLUS from the unix prompt directly....I enter the user name and password....but I get the following error:

ERROR:
ORA-12545: Connect failed because target host or object does not exist

I tried to research on this error message and everywhere it mentions that i should check the entries in TNSNAMES.ora......i cross checked the entries and everything looks fine to me....below is the entry from my tnsnames.ora file...

VOMSD1=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.5.199.106)(PORT=1521))(CONNECT_DATA=(SID=PHYGD1)))

Could anyone please guide me as to what i might be missing which is preventing me from connecting to the database on a remote machine - 10.5.199.106??

Thanks,
Shruti

durden_tyler · May 5, 2010, 1:09pm

What's the output of the following 2 commands ?

echo $ORACLE_SID
tnsping VOMSD1

Did you export ORACLE_SID ?

Are you able to connect if you specify this ?

username/password@<value_of_ORACLE_SID>

If not, what error do you encounter ?

tyler_durden

curleb · May 5, 2010, 1:11pm

start off by seeing that you can 'see' everything as needed.

telnet 10.5.199.106 1521

tnsping PHYGD1

Beyond this, if the instance is down...or the schema and/or object is not present you'll want to speak to your DBA. Good luck!

shrutihardas · May 5, 2010, 1:56pm

1) echo $ORACLE_SID returns the following:

bash: PHYGD1: command not found

where PHYGD1 is the correct SID.

2) tnsping to VOMSD1 returns OK

3) telnet to the server says 'Connected'

From the basic checks everything looks fine to me.

Could someone please confirm if the tnsnames.ora entry in Unix can exactly be same as the one in windows?

because i am able to connect to the remote database through toad from my windows machine w/o any issues.

Scott · May 5, 2010, 1:59pm

That's surely not possible!!

Is that what you really typed?

echo $ORACLE_SID

I'm surely not the only person who uses oraenv to set an Oracle environment?

durden_tyler · May 5, 2010, 3:18pm

(a) Run the commands posted earlier on the Unix/Linux dollar prompt.
(b) Select the relevant area from your terminal emulator program (PuTTY/Konsole/Gnome Terminal/XTerm etc.), copy it and paste it over here.
So that we could see what exactly you typed, and its result.

tyler_durden

shrutihardas · May 5, 2010, 4:15pm

@durden_tyler - I am able to establish a DB connection now. Using the following command from Unix prompt:

sqlplus <User_ID>/<Password>@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.5.199.106)(PORT=1521))(CONNECT_DATA=(SID=PH YGD1)))'

I was now trying to use the sample code which you had provided below -

#!/bin/bash
#Connect to the database using sqlplus
x=`sqlplus -s /nolog <<EOF
connect test/test
set pages 0 
select x from t;
exit
EOF`

As follows:

sqlplus gidicm/gidicm@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.5.199.106)(PORT=1521))(CONNECT_DATA=(SID=PHYGD1)))' >> outputlogfile.log
echo "Step 1 Done"
select sysdate from dual;
ENDSQL
echo "Output_variable, sysdate=$sysdate"

This code is getting stuck at the select statement.

Could you please let me know the correct way to invoke a sql from shell script? Please note that the way I am using the command sqlplus needs to be the same as above...since i am able to establish connection using this command now.

Please advise.

Thanks!
Shruti

curleb · May 5, 2010, 4:18pm

preface the line starting "echo ... " with the sqlplus command host. This is needed to produce a host-side command from within the sqlplus client...

But, you're also mixing and matching syntax provided by tyler and your original approach. As stated before, one step at a time...prove you can get the select to run from the command-line...manually, and then you can try to get it into a script.

shrutihardas · May 5, 2010, 4:42pm

I am sorry I did not explain it clearly before:

Yes, I was able to connect to the database from the Unix prompt and also I was able to run the following query successfully on the prompt. Please see the query ran and the output I got below:

bash-2.05b$ sqlplus gidicm/gidicm@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.5.199.106)(PORT=1521))(CONNECT_DATA=(SID=PHYGD1)))'

SQL*Plus: Release 9.2.0.8.0 - Production on Wed May 5 16:31:34 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select sysdate from dual;

SYSDATE
---------
05-MAY-10

SQL>

Now I am trying to include this simple query in my shell script. For which I have written the below script:

#!/bin/bash
sqlplus gidicm/gidicm@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.5.199.106)(PORT=1521))(CONNECT_DATA=(SID=PHYGD1)))"<< EOF
'select sysdate from dual;
EOF'
echo "Output_variable, x=$sysdate"

This script is able to establish the database connection successfully...but it fails at the step where i try to fire the select query. I know i am missing the write syntax here....but that's where i need the help from you experts.

Thanks!

Scott · May 5, 2010, 5:06pm

Hi.

(minus the connection string - that I'm not totally sure of the reason for) your script is OK, except for the single quotes

'select sysdate from dual;
EOF'

I'm not sure what purpose they serve.

Your echo statement afterwards (echo "Output_variable, x=$sysdate" is echoing a variable that you never set.

curleb · May 5, 2010, 5:19pm

Try this approach:

my_sql=$(sqlplus -s /nolog <<EOF
connect scott/tiger
set pages 0 
select sysdate from dual; 
exit
EOF)

echo $my_sql

In your case, you can certainly try the following, but you might just need to adjust your tnsnames.ora file so that you can work with just the label or the SID itself:

my_sql=$(sqlplus -s /nolog <<EOF
connect gidicm/gidicm@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.5.199.106)(PORT=1521))(CONNECT_DATA=(SID=PHYGD1)))'

set pages 0 
select sysdate from dual; 
exit
EOF)

echo $my_sql

methyl · May 5, 2010, 5:30pm

Further to "scottn". We lose the spurious single quotes and now try to get the output from the query into a unix environment variable called $myoutput . I've avoided using the name $sysdate for clarity.
The backticks `` enclose the entire sqlplus run and dump the output into the environment variable $sysdate (untested).

#!/bin/bash
myoutput=`sqlplus gidicm/gidicm@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.5.199.106)(PORT=1521))(CONNECT_DATA=(SID=PHYGD1)))"<< EOF
select sysdate from dual;
EOF
`
echo "Output_variable ${myoutput}"

If this works, we move to the next step which is getting Oracle to output a clean result without SQL prompts etc. .

Please also read curleb post above (which I had not read prior to posting) because it contains valuable advice. The lengthy connection string is unusual (and until today I didn't know that syntax). The $( ... commands ... ) syntax is a modern alternative to backticks and recommended by many posters.
The post from curleb also tackles many of the issues with unwanted output from the sqlplus program.

shrutihardas · May 6, 2010, 10:12am

@curleb - Thanks a lot for your help!! The code provided by you

my_sql=$(sqlplus -s /nolog <<EOF
connect gidicm/gidicm@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.5.199.106)(PORT=1521))(CONNECT_DATA=(SID=PHYGD1)))'

set pages 0 
select sysdate from dual; 
exit
EOF)

echo $my_sql

Does return a value for $my_sql variable. Now the only problem I am left with is - the output which it returns is as follows:

Connected. 06-MAY-10

I do not need the word 'Connected' in the output. I only need the date part.

Could you please let me know how do i get that?

Thanks a ton for all your help!

Highly appreciated!!

Please advise.

Scott · May 6, 2010, 10:22am

shrutihardas:

@curleb - The code provided by you
my_sql=$(sqlplus -s /nolog <<EOF
connect gidicm/gidicm@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.5.199.106)(PORT=1521))(CONNECT_DATA=(SID=PHYGD1)))'

set pages 0 
select sysdate from dual; 
exit
EOF)

echo $my_sql
Establishes a database connection and fires the query on the database. But the my_sql variable does not return anything. It returns a blank output. I guess the $ (....command....) doesnt seem to work at my end. Do you have any other suggestions? I am sure its a very small tweak somewhere in the code that I need. Wondering what am I missing!!

Please advise.

Hi shrutihardas,

I remember from a long time ago, that under some situations $(...) did not work with SQL*Plus (so long, that I don't remember which circumstances :))

See if using back-ticks, instead, helps:

my_sql=`sqlplus -s /nolog <<EOF
connect gidicm/gidicm@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.5.199.106)(PORT=1521))(CONNECT_DATA=(SID=PHYGD1)))'

set pages 0 
select sysdate from dual; 
exit
EOF`

echo $my_sql

Both `...` and $(...) work for me, though.

shrutihardas · May 6, 2010, 10:27am

I am sorry - The code provided by curleb actually works like a charm!

@curleb - Thanks a lot for your help!!

The code Does return a value for $my_sql variable. Now the only problem I am left with is - the output which it returns is as follows:

Connected. 06-MAY-10

I do not need the word 'Connected' in the output. I only need the date part.

Could you please let me know how do i get that?

Thanks a ton for all your help!

Highly appreciated!!

Please advise.