Re: insert into returns 0 rows

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

Well, kind of:

SQL> select (select ename from emp where empno=123) name from dual;

NAME

But it is hardly more elegant than the other ways! Especially if you want more than one column returned...

Another way (just for fun) would be:

SQL> select emp.ename
2 from emp, dual
3 where emp.empno (+) = decode(dual.dummy,'x',123);

ENAME

But frankly I would prefer to handle the NO_DATA_FOUND. The OP's objection that "that would suggest that I'm handling such a condition that shouldn't occur" pre-supposes that the developer reading the code doesn't know that "that's just how PL/SQL works!"

If the same SELECT is performed in many places, it may be convenient to wrap it in a function, which can then hide the NO_DATA_FOUND handling. Received on Wed Oct 27 2004 - 11:49:55 CDT