OraFAQ Forum: SQL & PL/SQL » Select Into

Home » SQL & PL/SQL » SQL & PL/SQL » Select Into - Nulls

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Select Into - Nulls [message #20126]

Tue, 30 April 2002 07:55

Rob
Messages: 70
Registered: January 2000

Member

Hello all,

I have a select...into statement in a stored procedure. The statement can bring back no results, thus resulting in a No_data error. What's the best approach around this? (nvl will not work in this instance)

Thanks

Report message to a moderator

Re: Select Into - Nulls [message #20127 is a reply to message #20126]

Tue, 30 April 2002 08:12

Kalpa
Messages: 21
Registered: February 2002

Junior Member

try using an exception call in your stored procedure,
EXCEPTION
WHEN_NO_dATA_FOUND THEN
do whatever...

Report message to a moderator

Re: Select Into - Nulls [message #20133 is a reply to message #20126]

Tue, 30 April 2002 10:11

Rob
Messages: 70
Registered: January 2000

Member

But processing won't resume on the following line after the statment that caused the error, will it?

Report message to a moderator

Re: Select Into - Nulls [message #20135 is a reply to message #20133]

Tue, 30 April 2002 10:51

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Rob, if you want to continue on and just end up with a NULL value in your variable, then:

begin
  begin
    select col into x...;
  exception
    when no_data_found then
      null; -- silently handle the exception
  end;
  -- continue on, value of x is null if not found
end;

Report message to a moderator

Previous Topic:	plz help me in knowing this
Next Topic:	extract date

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 14:34:39 CDT 2024