7 Replies Latest reply: Feb 8, 2013 11:42 AM by jgarry RSS

    Unable to pass username/pasword@dbname for sqlplus in unix shell script

    Arun Natarajan-OC
      Hi Friends,

      I would like to use sqlplus in a shell script and i would like to passw the username , password and dbname as variable. so i developed a script but this is not working.


      Please let me know how to do this?

      .bash profile has ORACLE_HOME and ORACLE_SID set


      #test.sh
      --------------

      $HOME/.bash_profile
      export USERNAME=system
      export PASSWORD=manager
      export DBNAME=test

      export CONNECT=$USERNAME/$PASSWORD@$DBNAME
      sqlplus $CONNECT <<!
      select name from v$database;
      !

      Error is :
      ERROR:
      ORA-01017: invalid username/password; logon denied


      Enter user-name: SP2-0306: Invalid option.
      Usage: CONN[ECT] [logon [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
      where <logon> ::= <username>[<password>][@<connect_identifier>]
      <proxy> ::= <proxyuser>[<username>][<password>][@<connect_identifier>]


      Regards,
      DB
        • 1. Re: Unable to pass username/pasword@dbname for sqlplus in unix shell script
          Dropbear67
          try

          sqlplus "${USERNAME}/${PASSWORD}@${DATABASE}"

          or

          CONNECT="${USERNAME}/${PASSWORD}@${DATABASE}"
          sqlplus "$CONNECT"
          • 2. Re: Unable to pass username/pasword@dbname for sqlplus in unix shell script
            asahide
            Hi,

            It's looks like correct..

            ORACLE_SID is set.
            [hogehoge@hogehoge ~]$ cat test.sh
            #!/bin/sh
            export USERNAME=testusr
            export PASSWORD=testusr
            export CONNECT=$USERNAME/$PASSWORD@$ORACLE_SID
            
            echo $CONNECT
            sqlplus $CONNECT <<!
            select * from user_users;
            !
            [hogehoge@hogehoge ~]$
            [hogehoge@hogehoge ~]$
            [hogehoge@hogehoge ~]$
            [hogehoge@hogehoge ~]$ ./test.sh
            testusr/testusr@HOGEHOGE
            
            SQL*Plus: Release 10.1.0.5.0 - Production on Fri Feb 8 08:16:00 2013
            
            Copyright (c) 1982, 2005, Oracle.  All rights reserved.
            
            
            Connected to:
            Oracle Database 10g Release 10.1.0.5.0 - Production
            
            SQL>
            USERNAME                          USER_ID ACCOUNT_STATUS
            ------------------------------ ---------- --------------------------------
            LOCK_DATE    EXPIRY_DATE  DEFAULT_TABLESPACE
            ------------ ------------ ------------------------------
            TEMPORARY_TABLESPACE           CREATED      INITIAL_RSRC_CONSUMER_GROUP
            ------------------------------ ------------ ------------------------------
            EXTERNAL_NAME
            --------------------------------------------------------------------------------
            TESTUSR                                35 OPEN
                                      WORK
            TEMP                           25-DEC-12    DEFAULT_CONSUMER_GROUP
            
            
            
            SQL> Disconnected from Oracle Database 10g Release 10.1.0.5.0 - Production
            regards,
            • 3. Re: Unable to pass username/pasword@dbname for sqlplus in unix shell script
              Arun Natarajan-OC
              Hi Dropbear,

              Thanks the command works fine.

              But i have other issue:

              i am trying to scan line by line and pull ut the values and pass it in variables and then wil use it but doesn't work.

              Please see below for details.


              *#more dbtest.sh*
              -------------------------
              #!/bin/bash
              . $HOME/.bash_profile


              while read line
              do

              export DBNAME=`cat dblist.txt | awk '{print $1}'`
              export USERNAME=`cat dblist.txt | awk '{print $2}'`
              export PASSWORD=`cat dblist.txt | awk '{print $3}'`

              echo "$DBNAME , $USERNAME , $PASSWORD"

              done < dblist.txt


              *#more dblist.txt*
              test system managertest
              dev system managerdev


              Current output:
              ----------------------------
              [oracle@test01.com] sh dbtest.sh
              test
              dev , system
              system , managertest
              managerdev
              test
              dev , system
              system , managertest
              managerdev



              Required output
              -------------------------
              test , system , managertest
              dev , system , managerdev

              Regards,
              DB
              • 4. Re: Unable to pass username/pasword@dbname for sqlplus in unix shell script
                JohnWatson
                This is a little off topic for an Oracle forum :) But here you go:
                db121a $
                db121a $ while read line
                
                do
                export A=`echo $line|awk '{print $1}'`
                export B=`echo $line|awk '{print $2}'`
                export C=`echo $line|awk '{print $3}'`
                echo "$A $B $C"
                done < dblist.txt
                a b c d e f g h i db121a $ cat dblist.txt a b c d e f g h i db121a $
                --
                John Watson
                http://skillbuilders.com

                Edited by: JohnWatson on Feb 8, 2013 8:41 AM
                Those tags are pathetic. Sorry the above doesn't display correctly.
                • 5. Re: Unable to pass username/pasword@dbname for sqlplus in unix shell script
                  Osama_Mustafa
                  its not good idea to use clear password in the shell script, therefore you should find some way to hide the password for security reasons specially if this user have high privileges

                  You can read
                  http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:142212348066
                  Re: Encrypting password in shell script on Solaris 10.
                  • 6. Re: Unable to pass username/pasword@dbname for sqlplus in unix shell script
                    EdStevens
                    839396 wrote:
                    Hi Friends,

                    I would like to use sqlplus in a shell script and i would like to passw the username , password and dbname as variable. so i developed a script but this is not working.


                    Please let me know how to do this?

                    .bash profile has ORACLE_HOME and ORACLE_SID set


                    #test.sh
                    --------------

                    $HOME/.bash_profile
                    export USERNAME=system
                    export PASSWORD=manager
                    export DBNAME=test

                    export CONNECT=$USERNAME/$PASSWORD@$DBNAME
                    sqlplus $CONNECT <<!
                    select name from v$database;
                    !
                    And I'm sure you'll also ask another shell question when you hit the next error, so here's your hint:

                    The shell will read and process everything down to the terminating "?" before passing it to sqlplus. Thus, it will try to expand what it sees as the environment variable "$databse" (from "v$database").

                    Error is :
                    ERROR:
                    ORA-01017: invalid username/password; logon denied


                    Enter user-name: SP2-0306: Invalid option.
                    Usage: CONN[ECT] [logon [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
                    where <logon> ::= <username>[<password>][@<connect_identifier>]
                    <proxy> ::= <proxyuser>[<username>][<password>][@<connect_identifier>]


                    Regards,
                    DB
                    • 7. Re: Unable to pass username/pasword@dbname for sqlplus in unix shell script
                      jgarry
                      Wouldn't it be easier to:
                      echo "one two three" > dblist.txt
                      while read a b c
                      do
                        echo "$a $b $c"
                      done < dblist.txt
                      one two three