This discussion is archived
7 Replies Latest reply: Feb 8, 2013 9:42 AM by jgarry RSS

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

Arun Natarajan-OC Explorer
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points