There is approach is generally frowned upon because it obfuscates the
meaning of the code. The next guy to look at the code has to figure out that
you don't REALLY want the count ... what you really want is to know if that
record exists or not. That being said, It is easy to understand why one
would want to avoid the messy exception handling business. If you really
want to go this route you could also do
function myfunction (deptnum in number)
returns pls_integer as
return_code pls_integer;
begin
select nvl2(max(deptno)-,1,1) into return_code from dept where deptno =
deptnum;
return return_code;
end;
On Sun, Sep 21, 2008 at 9:44 PM, [EMAIL PROTECTED] <[EMAIL PROTECTED]>wrote:
>
> You can use the count(deptno) to avoid all exceptions(NO_DATA_FOUND,
> TOO_MANY_ROWS).
> select count(deptno) into v_count from dept where deptno = deptNum;
> if v_count=1 then
> if deptNum = v_deptNum then
> returnValue := -1;
> else
> returnValue := 1;
> end if;
> else
> returnValue := 1;
> end if;
>
> Hope this helps.
>
> Anran
>
>
> On Sep 22, 10:44 am, JNewMember <[EMAIL PROTECTED]> wrote:
> > I was able to construct the procedure without syntax but i think I am
> > not handling exceptions correctly.
> >
> > create or replace procedure check_dno (deptNum integer)
> > is
> > v_deptNum integer;
> > returnValue integer;
> > begin
> > select deptno into v_deptNum from dept where deptno = deptNum;
> > if deptNum = v_deptNum then
> > returnValue := -1;
> > else
> > returnValue := 1;
> > end if;
> > exception
> > when access_into_null then
> > dbms_output.put_line('Null Pointer Exception');s
> > dbms_output.put_line(returnValue);
> > end check_dno;
> >
> > What I want to do is based on what select query find return the value.
> >
> > Thanks,
> > Anu
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
[EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
