insert into returns 0 rows

[Agoston Bejo]

Hi,
when a SELECT in an INSERT INTO statement returns no rows, NO_DATA_FOUND
exception is raised. How do I write a "nice" SELECT INTO when I expect that
0 or 1 row gets returned, and the variable should be NULL if no rows are
returned.
By "nice" I mean that my intention is clear from the source code and no
exception-catching is involved, since that would suggest that I'm handling
such a condition that shouldn't occur.
Therefore the following two techniques are not satisfying:

SELECT MIN(thefield) INTO myvar ... -- (not clear what is intended)

BEGIN --exception handling where no real exceptional condition occurs
SELECT thefield INTO myvar...
EXCEPTION WHEN NO_DATA_FOUND THEN
myvar := NULL
END;

[Thomas Kellerer]

On 27.10.2004 15:17 Agoston Bejo wrote:

Hi,
when a SELECT in an INSERT INTO statement returns no rows, NO_DATA_FOUND
exception is raised. How do I write a "nice" SELECT INTO when I expect that
0 or 1 row gets returned, and the variable should be NULL if no rows are
returned.
By "nice" I mean that my intention is clear from the source code and no
exception-catching is involved, since that would suggest that I'm handling
such a condition that shouldn't occur.
Therefore the following two techniques are not satisfying:

SELECT MIN(thefield) INTO myvar ... -- (not clear what is intended)

BEGIN --exception handling where no real exceptional condition occurs
SELECT thefield INTO myvar...
EXCEPTION WHEN NO_DATA_FOUND THEN
myvar := NULL
END;

I can't see a problem with the second approach. Simply add a comment to explain
that this is an expected exception. From my point of view this is perfectly
acceptable

Thomas

[Serge Rielau]

Does Oracle support this?
myvar := (select MIN(thefield) ....)
"Scalar subqueries" should have "null on empty" semnatics.

Cheers
Serge

[Ed prochak]

Serge Rielau <sr*****@ca.ibm.com> wrote in message news:<2u*************@uni-berlin.de>...

Does Oracle support this?
myvar := (select MIN(thefield) ....)
"Scalar subqueries" should have "null on empty" semnatics.

Cheers
Serge

I'll interpret the above as two questions.
#1 Does Oracle support:
myvar := (select MIN(thefield) ....)
?

Well, AFAIK not in PL/SQL or embedded SQL. The above looks like some
form of PASCAL. I don't think any language supports a SQL construct
like that, but I could be wrong. Correct syntax in ORACLE looks like

SELECT MIN(thefield) into :myvar ...

#2 Does Oracle support:
"Scalar subqueries" should have "null on empty" semnatics.
?

YES.
If the value of the column named "thefield" is null for ALL rows in
the result set, then MIN() returns NULL. Same is also true for MAX()
and several other group functions. (COUNT() of course never return
NULL.)

HTH,
Ed-