How can I retrieve query results from Oracle using shel…

You can retrieve Oracle query results in a shell using the following methods:

1. Using the sqlplus command: sqlplus is a command-line tool provided by Oracle, which can connect to an Oracle database and execute SQL statements. It can be used in shell scripts to execute queries using the sqlplus command and output the results to the standard output or save them to a file. For example:

#!/bin/bash
result=$(sqlplus -S username/password@hostname:port/service_name << EOF
set heading off
set feedback off
set pagesize 0
select column_name from table_name;
exit;
EOF
)
echo $result

1. By using Oracle Instant Client and programming languages such as Python and Java, it is possible to connect to an Oracle database, execute query statements, and retrieve results. These programming languages’ commands or scripts can be called within shell scripts to obtain query results. For instance, connecting to Oracle and executing queries in Python using the cx_Oracle library.

import cx_Oracle

# 连接Oracle数据库
connection = cx_Oracle.connect('username/password@hostname:port/service_name')

# 创建游标
cursor = connection.cursor()

# 执行查询语句
cursor.execute('SELECT column_name FROM table_name')

# 获取查询结果
result = cursor.fetchall()

# 关闭游标和连接
cursor.close()
connection.close()

# 输出查询结果
for row in result:
    print(row[0])

Call a Python script in a shell script to obtain query results.

#!/bin/bash
result=$(python script.py)
echo $result

Choose the method that best suits you to obtain Oracle query results.