5 Replies Latest reply: Dec 18, 2012 4:21 AM by Billy~Verreynne RSS

    How to connect to remote Oracle Server through shell script.

    980455
      Hi, I am new to Shell scripting. I am trying to connect to remote oracle system, but i am getting Sqlplus not found error. i have sqlplus executable inside bin under oracle home but still i am getting the same error. can any one guide me to get this thing done.

      export ORACLE_HOME=/opt/oracle/oracle11g/product/11.2.0
      export NLS_LANG=American_America.US7ASCII
      export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
      export ORACLE_PATH=$ORACLE_HOME/bin
      export PATH=$ORACLE_HOME/bin:$PATH

      sqlplus -s /NOLOG <<EOF
      connect wcadmin/wcadmin@Oracle1.com:1521/wctest98
      select * from dual;
      exit
      EOF
        • 1. Re: How to connect to remote Oracle Server through shell script.
          bigdelboy
          977452 wrote:
          Hi, I am new to Shell scripting. I am trying to connect to remote oracle system, but i am getting Sqlplus not found error. i have sqlplus executable inside bin under oracle home but still i am getting the same error. can any one guide me to get this thing done.

          export ORACLE_HOME=/opt/oracle/oracle11g/product/11.2.0
          export NLS_LANG=American_America.US7ASCII
          export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
          export ORACLE_PATH=$ORACLE_HOME/bin
          export PATH=$ORACLE_HOME/bin:$PATH

          sqlplus -s /NOLOG <<EOF
          connect wcadmin/wcadmin@Oracle1.com:1521/wctest98
          select * from dual;
          exit
          EOF
          I assume you not finding the 'sqlplus' excutable.


          When you echo PATH:

          echo $PATH

          is it on that path ?

          is it at:

          /opt/oracle/oracle11g/product/11.2.0/bin/sqplus

          Or is it elsewhere? e.g:

          /opt/oracle/oracle11g/product/11.2.0/db_1/sqlplus

          Is your ORACLE_HOME correct ?

          it there a permissions problem ?

          ls -la /opt/oracle/oracle11g/product/11.2.0/bin/sqplus

          .... just some thoughts .....
          • 2. Re: How to connect to remote Oracle Server through shell script.
            980455
            well i am a bit confused. when i execute echo $ORACLE_HOME on remote ORACLE system then i get "/opt/oracle/oracle11g/product/11.2.0" which is what i have set on my local unix system. now when i execute echo $ORACLE_HOME on my local system it says "Variable not found". so where exactly i am setting this variable and how this export command work.
            • 3. Re: How to connect to remote Oracle Server through shell script.
              Dude!
              Please describe in more detail what you are doing. Are you running 2 scripts or is it all in one script? If you are defining the Oracle variables in a separate script, you need to source execute the script.

              Source executing a script means the commands in the script are run as if you are typing them at the current command prompt. A script that is not source executed will loose the variables you set inside the script when the script exits. You can source execute a script using the ".<space>script" or "source script" (bash). The export command adds variables to the environment of subsequently executed commands.

              Check the following examples:
              cat > shelltest <<EOF
              echo \$a
              a=b
              EOF
              chmod u+x shelltest
              Test1:
              $ a=a
              $ ./shelltest
              
              $ echo $a
              a
              
              Test2:
              $ export a=a
              $ ./shelltest
              a
              $ echo $a
              a
              
              Test3:
              $ . ./shelltest
              a
              $ echo $a
              b
              • 4. Re: How to connect to remote Oracle Server through shell script.
                980455
                Thank you all... finally solved it...!!!
                • 5. Re: How to connect to remote Oracle Server through shell script.
                  Billy~Verreynne
                  977452 wrote:

                  I am trying to connect to remote oracle system, but i am getting Sqlplus not found error.
                  You need an Oracle client installation. The most appropriate one is Oracle Instant Client. You need to download 2 zips. The basic Instant Client that provides the client driver. And the zip that includes SQL*Plus.

                  Unzip both these to the same directory. E.g. +$HOME/instantclient_11_2+

                  Next, set the following variables in your profile (typically +$HOME/.bash_profile+ on Linux):

                  To find the SQL*Plus executable:
                  export PATH=$PATH:$HOME/instantclient_11_2

                  To find the Oracle drivers:
                  export LD_LIBRARY_PATH=$HOME/instantclient_11_2:$LD_LIBRARY_PATH

                  Optionally, to find the tnsnames.ora and sqlnet.ora files:
                  export TNS_ADMIN=$HOME/sqlnet

                  No other environment settings are really needed.