Thanks Michael,
Yeap I am not interest in the count, as you mentioned I want to return a
value (1, -1) if the record exists.
On Mon, Sep 22, 2008 at 11:46 AM, Michael Moore <[EMAIL PROTECTED]>wrote:
> 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
>>
>>
>>
>
> >
>
--
Anuradha Uduwage
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
