SELECT INTO and CURSORS

[karmst]

Hi all,

I have a stored procedure that currently has 3 cursors. One of them is definitely required, but the other 2 are used ONLY to get past the 'NO ROWS FOUND' ORA error when doing a SELECT INTO cluase.

The most rows the SELECT INTO will return is 1 (I know this from the data) - but there are definitely cases where I will get no results.

Is there no way I can use SELECT INTO with a 'do this if there are no rows found' clause? I do not want to use 2 extra cursors if I do not have to.

Thanks in advance,

Keith

[chrisrlong]

BEGIN
....

--BEGIN
----SELECT
------COL1
----INTO
------L_Col1
----WHERE
------<whatever>
--EXCEPTION
----WHEN NO_DATA_FOUND THEN
------<whatever>
--END

...

END

HTH,

- Chris

[lesstjm]

declare

cursor c1 is
(this is the one that returns 1 or 0 rows)

begin

for v1 in c1 loop

if c1%rowcount = 0 then
(this checks to see how many rows are returned in your cursor)

end loop

end

[akkerend]

As Chrisrlong stated you can also put the SELECT INTO in a PL/SQL block with an exception handler. You also need to handle the TOO_MANY_ROWS exception.

Why don't you want to use the cursors? With cursors you don't have to change your code if the data changes and there are no or many rows returned. And with cursors the code is more easy to read and maintain, then with the different exception handlers.