'How to connect Database and run query inside a shell script?

I am trying to connect to DB from a shell script, but I am getting below errors for this.

Database output: ERROR: ORA-12154: TNS:could not resolve the connect identifier specified

SP2-0306: Invalid option. Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}] where ::= [/][@] | / SP2-0306: Invalid option. Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}] where ::= [/][@] | /

Trail code:

#!/bin/bash

LogDirectory='/users/users-06/p6***8/scripts/dir'

ORACLE_HOME=/tools/ver/oracle-10.2.0.1-64
export ORACLE_HOME

DBUSER='p6*02*1'
DBUSERPASSWORD='R****07'
DB='O**XDA3'

var=`$ORACLE_HOME/bin/sqlplus -S ${DBUSER}/${DBUSERPASSWORD}@${DB} << EOD
spool ${LogDirectory}/query.txt
set linesize 32767
set feedback off
set heading off
SELECT * FROM Omi.ESP_FEED_REQUEST WHERE FEED_NAME='PSAR_TRANSACTION_FEED' AND REQUEST_ID='3694707322503' AND AS_OF='04-Jan-2017' ORDER BY 1 DESC;
spool off
exit;
EOD`

echo $var > ${LogDirectory}/DB_output.txt

Could you please suggest me how I will get the sql output in "var" variable, please? Thanks a lot !

oraclebashshellunixsqlplus


Solution 1:[1]

The output of your query is getting spooled at below path

{LogDirectory}/query.txt

var will only have a status of code 1 or 0 . If the statement is executed successfully then var status will be 0 else 1.

Solution 2:[2]

It's working fine here:

#!/bin/bash

LogDirectory='/home/oracle'

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_HOME

DBUSER='scott'
DBUSERPASSWORD='scott'
DB='db11g'

var=`$ORACLE_HOME/bin/sqlplus -S ${DBUSER}/${DBUSERPASSWORD}@${DB} << EOD
spool ${LogDirectory}/query.txt
set linesize 32767
set feedback off
set heading off
select 5 from dual;
exit;
EOD`

echo "Database output: ${var}"

$ ./stack.sh
Database output:
         5

Is your database up and running? check:

ps -ef | grep pmon

This will show a process if it's running. When it's not you get the error you're getting:

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
$ ./stack.sh
Database output: ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
      <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
      <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

edit for XING:

I changed the code to:

 select * from t1;

$ ./stack.sh
Database output:
A
B

and select * from user_tables

$ ./stack.sh

Database output:
DEPT                           USERS                                                                                  VALID             10                     1        255          65536     1048576           1  2147483645           YES N           4          5            0          0          0          20                         0             0           1          1     N ENABLED            4 07-OCT-14 NO               N N NO  DEFAULT DEFAULT DEFAULT DISABLED YES NO             DISABLED YES                                DISABLED DISABLED              NO  NO  YES DEFAULT
SALGRADE                       USERS                                                                                  VALID             10                     1        255          65536     1048576           1  2147483645           YES N           5          5            0          0          0          10                         0             0           1          1     N ENABLED            5 07-OCT-14 NO               N N NO  DEFAULT DEFAULT DEFAULT DISABLED YES NO             DISABLED YES                                DISABLED DISABLED              NO  NO  YES DE

Solution 3:[3]

echo "SELECT * FROM Omi.ESP_FEED_REQUEST WHERE FEED_NAME='PSAR_TRANSACTION_FEED' AND REQUEST_ID='3694707322503' AND AS_OF='04-Jan-2017' ORDER BY 1 DESC;" | sqlplus -s $DBUSER@$DB/$DBUSERPASSWORD >> dboutput.txt

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 XING
Solution 2
Solution 3 Brett Schneider

Related Questions