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

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 --