# calling sqlplus from shell

**URL:** <https://community.unix.com/t/calling-sqlplus-from-shell/144562>\
**Category:** Shell Programming and Scripting\
**Created:** [October 23, 2002, 12:16am UTC](https://community.unix.com/t/calling-sqlplus-from-shell/144562 "2002-10-23T00:16:05Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![suds19](https://community.unix.com/letter_avatar/suds19/32/5_5575768a8748004e209b776fc1b2916d.png) [@suds19](https://community.unix.com/u/suds19)\
**Post date:** [October 23, 2002, 12:16am UTC](https://community.unix.com/t/calling-sqlplus-from-shell/144562/1 "2002-10-23T00:16:05Z")

</div>

Hi All,

I am executing the following code :-

sqlplus -s ${DATABASE\_USER} |&  
print -p -- 'set feed off pause off pages 0 head off veri off line 500'  
print -p -- 'set term off time off serveroutput on size 1000000'  
print -p -- "set sqlprompt ''"  
print -p -- "SELECT run\_command from tmp\_run\_batch where upper(batch\_name) = upper('${PAR\_PROGRAM\_NAME}');"  
read -p RUN\_COMMAND  
eval print -p -- \""execute dbms\_output.put\_line(${RUN\_COMMAND});"\"  
read -p RET\_VAL  
print -p -- "exit;"

The select stmt given above gives sample output as :-

pack\_claims\_clas\_utils.func\_main('$PAR\_RUN\_DATE','$PAR\_RUN\_LEVEL','$PAR\_EXCLUSIVE\_RUN\_YN')

And then this package is executed.

The problem that I am facing is how to handle the no\_data\_found case of the select stmt. . When this case arises then the stmt. "read -p RUN\_COMMAND" hangs.

Could you please provide any solution ?

Thanks  
Suds

---

<div class="post-metadata">

**Author:** ![RTM](https://community.unix.com/user_avatar/community.unix.com/rtm/32/58_2.png) [@RTM](https://community.unix.com/u/RTM)\
**Post date:** [October 24, 2002, 2:17pm UTC](https://community.unix.com/t/calling-sqlplus-from-shell/144562/2 "2002-10-24T14:17:35Z")

</div>

Not sure if this will help or not - post your version of Sybase if it does not help you.

[Sybase FAQ](http://www.faqs.org/faqs/databases/sybase-faq/part13/)

---

<div class="post-metadata">

**Author:** ![LivinFree](https://community.unix.com/user_avatar/community.unix.com/livinfree/32/20_2.png) [@LivinFree](https://community.unix.com/u/LivinFree)\
**Post date:** [October 24, 2002, 4:01pm UTC](https://community.unix.com/t/calling-sqlplus-from-shell/144562/3 "2002-10-24T16:01:19Z")

</div>

It appears that you're using ksh, or some other modern shell, so you should be able to use the timeout option of read... to be honest, I don't know if it's work in this situation, since it should timeout "when reading from a terminal or pipe" - I don't know if a coprocess is considered a pipe in this case.

In any case, you should be able to say "read -t 30 -p RET\_VAL".  
If after 30 seconds, nothing happens, read will return code 1, and exit. You can place some code to check the return of read, and act from there.

Good luck!