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

On Jan 4, 8:51 am, "Mark D Powell" <Mark.Pow..._at_eds.com> 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.
>
> Observe
> UT1 > !cat test.sql test1.sql
> set echo on
> --
> create procedure test ( p_input in varchar2 ) is
> begin
> if p_input is null then dbms_output.put_line('No input');
> else dbms_output.put_line(p_input);
> end if;
> end;
> /
> set echo on
> --
> create procedure test1 ( p_input in varchar2 ) is
> begin
> if p_input = null then dbms_output.put_line('No input');
> else dbms_output.put_line(p_input);
> end if;
> end;
> /
>
> The two procedures are identical except one tests for null using "is
> null" and the other "= null".
>
> UT1 > execute test(NULL);
> No input
>
> PL/SQL procedure successfully completed.
>
> UT1 > execute test1(NULL);
>
> PL/SQL procedure successfully completed.
>
> UT1 > execute test('Bobcat');
> Bobcat
>
> PL/SQL procedure successfully completed.
>
> UT1 > execute test1('Bobcat');
> Bobcat
>
> PL/SQL procedure successfully completed.
>
> If I hardcode the FALSE path message you would see that the "= null"
> test was false.
>
> HTH -- Mark D Powell --- Hide quoted text -- Show quoted text -

PS forgot to mention version: test ran on version 9.2.0.6 on AIX 5.2

• Mark --