Did the query return any rows

#1

I'm calling SQL*Plus to run a query from inside a Unix (ksh) script. How
can I tell whether the query returned any rows or not?

#2

Consider:

SQL> select row_id from CX_LOCK;

ROW_ID
---------------
1
2
3
4
5
6
7
8
9
10
11

ROW_ID
---------------
12

12 rows selected.

SQL> select row_id from CX_LOCK where ROW_ID='abc';

no rows selected

#3

"Randy Harris" <randy (AT) SpamFree (DOT) com> wrote in
news:1q8ke.90$3V.58 (AT) newssvr31 (DOT) news.prodigy.com:

Quote:

I'm calling SQL*Plus to run a query from inside a Unix (ksh) script. How
can I tell whether the query returned any rows or not?

By making "proper" use of SQL*Plus & the ability to generate a SPOOL file,
I test for non-zero length of the output file. In other words zero rows
results in a spool file of length 0. I use this approach daily in cron jobs

#4

"absinth" <absinth (AT) gmail (DOT) com> wrote

Quote:

Consider:

SQL> select row_id from CX_LOCK;

ROW_ID
---------------
1
2
3
4
5
6
7
8
9
10
11

ROW_ID
---------------
12

12 rows selected.

SQL> select row_id from CX_LOCK where ROW_ID='abc';

no rows selected

But how can I determine in the script whether any rows were returned? Is
there some way to "capture" that no rows selected message?

#5

Couldn't you grep for it?

grep '^No rows selected' blah.out

if [[ $? -eq 0 ]]
then
xxxxx
fi

#6

"IANAL_VISTA" <IANAL_Vista (AT) hotmail (DOT) com> wrote

Quote:

"Randy Harris" <randy (AT) SpamFree (DOT) com> wrote in
news:1q8ke.90$3V.58 (AT) newssvr31 (DOT) news.prodigy.com:

I'm calling SQL*Plus to run a query from inside a Unix (ksh) script.
How
can I tell whether the query returned any rows or not?

By making "proper" use of SQL*Plus & the ability to generate a SPOOL file,
I test for non-zero length of the output file. In other words zero rows
results in a spool file of length 0. I use this approach daily in cron
jobs

I'm running the jobs from cron and generating spool files. It didn't occur
to me that the file would be 0 length. I assumed that it would contain the
message "no rows selected". Thanks.

#7

"Randy Harris" <randy (AT) SpamFree (DOT) com> wrote in
news:PK9ke.107$l51.44 (AT) newssvr31 (DOT) news.prodigy.com:

Quote:

"IANAL_VISTA" <IANAL_Vista (AT) hotmail (DOT) com> wrote in message
news:Xns965EB230B9ADFSunnySD (AT) 68 (DOT) 6.19.6...
"Randy Harris" <randy (AT) SpamFree (DOT) com> wrote in
news:1q8ke.90$3V.58 (AT) newssvr31 (DOT) news.prodigy.com:

I'm calling SQL*Plus to run a query from inside a Unix (ksh)
script.
How
can I tell whether the query returned any rows or not?

By making "proper" use of SQL*Plus & the ability to generate a SPOOL
file, I test for non-zero length of the output file. In other words
zero rows results in a spool file of length 0. I use this approach
daily in cron
jobs

I'm running the jobs from cron and generating spool files. It didn't
occur to me that the file would be 0 length. I assumed that it would
contain the message "no rows selected". Thanks.

Not if
SET FEEDBACK NO

#8

Couldn't you grep for it?

grep '^No rows selected' blah.out

if [[ $? -eq 0 ]]
then
xxxxx
fi

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Did the query return any rows

comp.databases.oracle.misc comp.databases.oracle.misc

Did the query return any rows - 05-22-2005 , 06:17 PM

Re: Did the query return any rows - 05-22-2005 , 07:12 PM

Re: Did the query return any rows - 05-22-2005 , 07:31 PM

Re: Did the query return any rows - 05-22-2005 , 07:32 PM

Re: Did the query return any rows - 05-22-2005 , 07:44 PM

Re: Did the query return any rows - 05-22-2005 , 07:47 PM

Re: Did the query return any rows - 05-22-2005 , 07:52 PM

Re: Did the query return any rows - 05-22-2005 , 08:09 PM