calling sqlplus from shell

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

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

Sybase FAQ

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!