Home » SQL & PL/SQL » SQL & PL/SQL » Function won't return correct results (Oracle, Oracle 9i, Windows XP)
Function won't return correct results [message #420437] Sat, 29 August 2009 11:57 Go to next message
ebrownoracle
Messages: 2
Registered: August 2009
Junior Member
I'm having a problem with a function that I created returning the correct results. Please see attachment IS_THERE_A_DUPLICATE.txt. I have named the function IS_THERE_A_DUPLICATE. The function examines a table that I have created called test_hid with one column called pid, pid's attribute is varchar2(9). I have inserted 4 rows the values of which are shown below:

PID
BRO101-1
BRO101-2
BRO101-3
BRO101-4

The purpose of the fucntion is to return the literal true if the value entered in the function matches any value in the pid column and return the literal false if it does not. However my problem is the function always returns the literal false even when the value entered into the function is there. For example If I enter IS_THERE_A_DUPLICATE('BRO101-3'),the function will return the literal 'false' even though the value clearly is shown in the table.

I have however narrowed down where the problem exists but I don't know how to fix it. If you look at my code right under where the FOR LOOP starts you will see the statement:

If pl_pids (inx1) = dup_pid
then
RETURN 'True';

For whatever reason when the code loops through the pid column comparing the value to the one passed through the function represented by the variable dup_pid it will not recognize any matches. I know the loop is working properly because I tested it using the dbms_output.put_line function and it prints all four values that exist in the PID column from BRO101-1..BRO101-4 to the screen. And I know my IF ELSE statment is working properly because If I substitute a literal for the pl_pids(inx1)I get the desired results for example the code below will return true:

If 'BRO101-2' = dup_pid
then
RETURN 'True';

And this code below will return false.

If 'BRO101-6' = dup_pid
then
RETURN 'True';

But when I use the original code no matter what value I pass through the function it always returns false. Can anyone help me?

Signed
Frustrated
Re: Function won't return correct results [message #420438 is a reply to message #420437] Sat, 29 August 2009 12:07 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>Can anyone help me?
We will see if you can catch a clue.

The RETURN is misplaced.
If initial test fails, then RETURN 'False' & function exits

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Function won't return correct results [message #420439 is a reply to message #420437] Sat, 29 August 2009 12:12 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
Supposing, that PL_PIDS is filled correctly, the code "works" as written:
   FOR inx1 IN 1..pl_pids.count LOOP
       If pl_pids (inx1) = dup_pid
        then
           RETURN 'True';
   ELSE      
           RETURN 'False';      
   END IF;
In English: compare the first record and return TRUE or FALSE according the condition. So, other comparisons are not performed. Maybe you want to return FALSE after the LOOP.

Or, maybe better, use simple SQL like
SELECT COUNT(*)
  INTO l_cnt
FROM test_hid
WHERE pid = dup_pid
  AND rownum = 1; -- for performance reason - stop for the first found row
And, in case you use this function to avoid PID duplicates: it (= using the function to determinate whether the inserted row is not present in TEST_HID) has serious flaw in multiuser environment. The only safe way to achieve PID uniqueness is to create a unique index or constraint on PID column.
Re: Function won't return correct results [message #420440 is a reply to message #420439] Sat, 29 August 2009 12:25 Go to previous messageGo to next message
ThomasG
Messages: 3184
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Of course the approach itself is absolutely horrible.

It's like you are trying to pick an apple from a tree by first chopping down the entire orchard.

This here should do basically the same thing:

.....
 (dup_pid  IN  varchar2)RETURN varchar2
 IS 
  v_ret varchar2(10)
 BEGIN
   SELECT nvl(max('TRUE'),'FALSE') into v_ret 
     FROM test_hid where pid = dup_pid;
   return v_ret;
 END;
/
Re: Function won't return correct results [message #420454 is a reply to message #420437] Sat, 29 August 2009 22:46 Go to previous messageGo to next message
ebrownoracle
Messages: 2
Registered: August 2009
Junior Member
I would like to thank Thomas G, Flyboy, and BlackSwan for their quick replies. I went with Flyboy's suggestion, but I will also look at the others and analyze why they might be effective as well. Thank you again.



Re: Function won't return correct results [message #420455 is a reply to message #420437] Sat, 29 August 2009 22:49 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>I would like to thank Thomas G
Thomas G's solution should be given serious consideration.
Re: Function won't return correct results [message #420456 is a reply to message #420454] Sat, 29 August 2009 23:52 Go to previous message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
ebrownoracle wrote on Sun, 30 August 2009 05:46
I went with Flyboy's suggestion

As you are talking in singular and I gave you three suggestions, I am curious which one you have chosen.
According to your terminology (function IS_THERE_A_DUPLICATE checking whether there is a row with given value), I am afraid that the third one shall be implemented.
Previous Topic: Global Hints for Scalar subquery
Next Topic: Complex Timeline / Calendar Question
Goto Forum:
  


Current Time: Mon Sep 26 14:33:43 CDT 2016

Total time taken to generate the page: 0.08305 seconds