Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • USER_TABLES.Freelists Oracle
  • Ports used by Oracle Software Oracle
  • Changing default shell Linux/Unix
  • Oracle Material from OTN Oracle
  • CTAS with LONG Column for 9i and higher Oracle
  • All About Trace Fils Oracle
  • AWR settings- MMON is not taking snapshot. Oracle
  • Find Multiple levels of object dependencies : depen.sql Oracle
  • Standby Database File Management in 10g with STANDBY_FILE_MANAGEMENT Oracle
  • dbms_job.submit example Oracle
  • Very clear article about oracle dataguard Oracle
  • chk_space_SID.ksh Linux/Unix
  • send email from unix mailx with attachment. Linux/Unix
  • ORACLE_SID in sqlplus Oracle
  • My FTP Job Scheduling for www.pvmehta.com PHP/MYSQL/Wordpress

How does one SELECT a value from a table into a Unix variable? From SQL to Shell

Posted on 23-Aug-2005 By Admin No Comments on How does one SELECT a value from a table into a Unix variable? From SQL to Shell

One can select a value from a database column directly into a Unix environment variable. Look at the following shell script examples:

#!/bin/sh

VALUE=`sqlplus -silent user/password@instance < set pagesize 0 feedback off verify off heading off echo off

select max(c1) from t1;

exit;

END`

if [ -z “$VALUE” ]; then

echo “No rows returned from database”

exit 0

else

echo $VALUE

fi

Second example, using the SQL*Plus EXIT status code (can only return integer values smaller than 256):

#!/bin/ksh

sqlplus -s >junk1 /nolog < connect user/password@instance

column num_rows new_value num_rows format 9999

select count(*) num_rows

from table_name;

exit num_rows

EOF

echo “Number of rows are: $?”

Yet another example, only this time we will read multiple values from SQL*Plus into shell variables.

sqlplus -s /nolog |& # Open a pipe to SQL*Plus

print -p — ‘connect user/password@instance’

print -p — ‘set feed off pause off pages 0 head off veri off line 500’

print -p — ‘set term off time off’

print -p — “set sqlprompt ””

print -p — “select sysdate from dual;”

read -p SYSDATE

print -p — “select user from dual;”

read -p USER

print -p — “select global_name from global_name;”

read -p GLOBAL_NAME

print -p — exit

echo SYSDATE: $SYSDATE

echo USER: $USER

echo GLOBAL_NAME: $GLOBAL_NAME

Linux/Unix, shell

Post navigation

Previous Post: Good Link from metalink 1
Next Post: How does one overcome the Unix 2 Gig file limit?

Related Posts

  • How to collect CPU usage on Linux using Shell script Linux/Unix
  • How does one overcome the Unix 2 Gig file limit? Linux/Unix
  • xargs use Linux/Unix
  • Looping for remote servers and find its database from oratab file. Linux/Unix
  • Passing from Unix to PLSQL using bind variables Linux/Unix
  • Find long Running Transaction Linux/Unix

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • Oracle (392)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (341)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • load SPM baseline from cursor cache05-Jun-2025
  • Drop all SPM baselines for SQL handle05-Jun-2025
  • Load SPM baseline from AWR05-Jun-2025
  • Drop specific SQL plan baseline – spm05-Jun-2025
  • findinfo.sql (SQL for getting CPU and Active session info)27-May-2025
  • SQL Tracker by SID sqltrackerbysid.sql22-Apr-2025
  • How to connect to Oracle Database with Wallet with Python.21-Mar-2025
  • JSON/XML Types in Oracle18-Mar-2025
  • CPU Core related projections12-Mar-2025
  • Exadata Basics10-Dec-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • TABLE SIZING WITH DB_BLOCK ARCHITECTURE Reference : Metalink note : 10640.1 Oracle
  • DETERMINING WHICH INSTANCE OWNS WHICH SHARED MEMORY & SEMAPHORE SEGMENTS Oracle
  • Btee and Bitmap Plans in Oracle 9i and higher Oracle
  • findx.sql /* Find Indexes on specified USER.TABLE_NAME */ Oracle
  • replace alphabets using sed Linux/Unix
  • The most important Tuning Notes Oracle
  • Useful Solaris Commands on 28-SEP-2005 Linux/Unix
  • Sort with ASCII order and Numeric Order Linux/Unix

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme