Skip to Main Content
  • Questions

  • silent connection to sqlplus in Korn shell

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Venkataviswanadh.

Asked: August 12, 2022 - 11:36 am UTC

Last updated: August 23, 2022 - 10:33 pm UTC

Version: 19C

Viewed 10K+ times! This question is

You Asked

Test case is connect to sqlplus in silent mode in korn shell and run a bunch of sql queries in one single sqlplus session..and where the out would be seen..

tried the following in test.ksh

#!/usr/bin/ksh
CURR_DIR=`pwd`
echo "current directory is:" $CURR_DIR
. $CURR_DIR/db.env
echo "start time:" `date`

cd $ORACLE_HOME/bin
echo "location is: "`pwd`

sqlplus -s EMDBO?EMDBO@hr92u017 |&

OUT=""

print -p "set heading off;"
print -p "set feedback off;"
print -p "set pagesize 0;"
print -p "set linesize 500;"
print -p "SELECT count(*)FROM dba_invalid_objects;"
print -p "show con_name;"

read -p OUT
read -p OUT 2>/scratch/oracle/null

## when run , script is not progressing further as below, need help here..

$ sh testksh.ksh
current directory is: /u01/app/dynamicscripts
start time: Fri Aug 12 11:32:10 GMT 2022
location is: /u01/app/oracle/product/testdbh/bin








and Connor said...

Not sure exactly when you're trying to achieve, but for me, I just use simple "print" to pipe what I want into SQL Plus and the job is done


[oracle@db192 ~]$ cat /tmp/x

#!/usr/bin/ksh

print "
connect / as sysdba
select * from dual;
select count(*) from dba_objects;
exit" | sqlplus -s /nolog

[oracle@db192 ~]$ /tmp/x

D
-
X


  COUNT(*)
----------
     73479

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

executing sqls in single sqlplus session

A reader, August 16, 2022 - 4:51 am UTC

Thanks connor for the response, actually we were trying to execute about 1.5k *.sql in singel sqlplus sessions
Connor McDonald
August 17, 2022 - 4:09 am UTC

Something like

ls -1 *.sql | awk '{print "@"$1}' > /tmp/full_list.sql


would give you a file that looks like:

@script1.sql
@script2.sql

and so forth. Then its just

print "
connect / as sysdba
@/tmp/full_list.sql
exit" | sqlplus -s /nolog


A reader, August 18, 2022 - 9:21 am UTC

thanks much connor for your concern and help, we were able execute the Sqls sequentially..
Connor McDonald
August 23, 2022 - 10:33 pm UTC

glad we could help
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database