Re: Cannot insert data when there is "select ... into..." in stored procedure

Mark D Powell wrote:
> On Jan 4, 8:20 am, Jim Smith <use..._at_ponder-stibbons.com> wrote:
> > In message <1167908289.603934.281..._at_31g2000cwt.googlegroups.com>,
> > cherryparadise..._at_gmail.com writes
> >
> > >Hi All,
> >
> > >I am doing database programming using VB.Net and Oracle DB 8.1.7.
> > >when there is command "select ...into..." in the following procedure,
> > >it doesn't insert anything and also no error return to the application.
> > >When I comment those command, it does insert records even they are
> > >null.
> > >What's wrong with my select statement?
> > >I can select value when I run the command in sqlplus.
> > >Anyone can have idea?What happens when you run the procedure in sql*plus?
> > e.g.
> > begin
> > insert_template_detail(....);
> > end;
> > /
> >
> > You are almost certainly getting an exception which causes any of the
> > code following to be skipped.
> >
> > You should use begin...exception...end blocks in your procedure to trap
> > errors and give meaningful messages.
> >
> > You should also check the error return in your vb.net code.
> > --
> > Jim Smith
> > Ponder Stibbons Limited <http://oracleandting.blogspot.com/>
> > RSS <http://oracleandting.blogspot.com/atom.xml>
>
> The OP should not be testing equal to null in their IF statements.
> Nothing is ever equal to null.

More than that, the OP should not be comparing to an empty string either, which is NULL in all Oracle releases up to, and including, 10.2 (though things may eventually change in some future release as Oracle was criticized for "empty string is NULL" rule for ages and their own docs state that it may be removed in the future.) Since nothing is equal to NULL, even another NULL, the comparisons have no chance to result in anything but FALSE and SELECT INTO parts are always executed. If one of them then throws NO_DATA_FOUND exception, execution stops and INSERT is never executed.

Regards,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Thu Jan 04 2007 - 12:13:14 CST