470,863 Members | 1,111 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,863 developers. It's quick & easy.

insert into returns 0 rows

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;


Jul 19 '05 #1
3 12185
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
Jul 19 '05 #2
Does Oracle support this?
myvar := (select MIN(thefield) ....)
"Scalar subqueries" should have "null on empty" semnatics.

Cheers
Serge
Jul 19 '05 #3
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-
Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

10 posts views Thread by johnnyboy10017 | last post: by
16 posts views Thread by Philip Boonzaaier | last post: by
2 posts views Thread by Stéphane Cazeaux | last post: by
8 posts views Thread by Josué Maldonado | last post: by
20 posts views Thread by Mark Harrison | last post: by
3 posts views Thread by Bob Bedford | last post: by
4 posts views Thread by Michel Esber | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.