How to connect to user defined Oracle database using Shell script?

Question

I have a server containing several different databases. I am trying to create a script that will ask the user which database they want to connect to, along with username and password, and then connect to it and perform remaining operations.

So far, I have written this:

awk 'BEGIN {FS=":"}!/^#/ && !/^\*/ && !/^$/{print $1":"$2}' ${ORATAB}

echo "Enter database instance name: "
read sid

export ORACLE_SID='echo $sid'
export ORACLE_HOME=`grep -i "^${ORACLE_SID}:" /etc/oratab | grep -v "^#" | cut -d ':' -f 2,2 `
export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/local/bin:/etc
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}

sqlplus /nolog << EOF
CONNECT / as sysdba
select object_type,status,count(1) from dba_objects where owner='abc' and status='INVALID' group by object_type,status;
EXIT;
EOF

However, this block of code is not connecting to the database, nor does it seem to connect to the database either.

I am getting the following error: sqlplus: not found [No such file or directory]

Isha · Accepted Answer · 2020-10-09 14:54:59Z

1

This worked after several attempts.

awk 'BEGIN {FS=":"}!/^#/ && !/^\*/ && !/^$/{print $1":"$2}' ${ORATAB}
    
    echo "Enter database instance name: "
    read sid
    
    export ORACLE_SID=$sid
    export ORACLE_HOME=`grep -i "^${ORACLE_SID}:" /etc/oratab | grep -v "^#" | cut -d ':' -f 2,2 `
    export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/local/bin:/etc
    export LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}
    
    sqlplus /nolog << EOF
    CONNECT / as sysdba
    select object_type,status,count(1) from dba_objects where owner='abc' and status='INVALID' group by object_type,status;
    EXIT;
    EOF

answered Oct 9, 2020 at 14:54

Isha

555 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jon Heller Over a year ago

You may want to check with your DBAs, chances are good that they have already built scripts to set the environment variables for each database.

Collectives™ on Stack Overflow

How to connect to user defined Oracle database using Shell script?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related