Re-populate variable in function

[seequal]

Hi, I have writen a function that will return a specific field from a table. I want to be able to re-populate one of the parameters in the function as it executes, depending on what the user has entered as the parameter using the
following, but simple cant find where in the function to add the if..else statement without causing a compile error

BEGIN
IF vAddressElement = 1
THEN vAddressElement := 'FirstAddressLine';
ELSE vAddressElement := vAddressElement;
END IF;
END


CREATE OR REPLACE FUNCTION Fnc_Getaddresselements(iPerId IN Mpi_Addr_Link.Alk_Per_Id%TYPE :=1,
vAddressElement IN Mpi_Address.Add_Line1%TYPE,
vAddCurrentValue IN Mpi_Address.Add_Current%TYPE := 1)

RETURN VARCHAR IS
/*
$Revision: 2 $
$Modtime: 11/05/06 10:29 $
*/

RESULT Mpi_Address.Add_Line1%TYPE;
vSQL VARCHAR2(2000);

BEGIN
BEGIN
vSQL:= 'SELECT ' || vAddressElement || '
FROM vwe_mpi_address a INNER JOIN vwe_mpi_addr_link al
ON a.add_id = al.alk_add_id
WHERE al.alk_per_id = ' || (iPerId) || '
AND a.add_current = ' || vAddCurrentValue || '
AND (add_from IS NULL OR add_from <= trunc(SYSDATE))
AND (add_to IS NULL OR add_to >= trunc(SYSDATE))';
EXECUTE IMMEDIATE Rtrim(vSQL)
INTO RESULT;
EXCEPTION
WHEN OTHERS THEN
RESULT := NULL;
END;
/* dbms_output.put_line(vAddressElement);
dbms_output.put_line(RESULT);*/
RETURN(RESULT);
END Fnc_Getaddresselements;


Thanks in advance...

 

[jimirvine]

Hi,
You cannot write a value to a parameter in a function. It just doesn't work that way.
Just declare a local variable and use

IF vAddressElement = 1
        THEN lvar := 'FirstAddressLine';
        ELSE lvar := vAddressElement;
     END IF;

And then use your lvar in the dynamic string.

Can I also suggest that:

 vSQL:= 'SELECT ' || vAddressElement || '
          FROM vwe_mpi_address a                                                              INNER JOIN vwe_mpi_addr_link  al                         
                     ON a.add_id = al.alk_add_id
           WHERE al.alk_per_id  = ' || (iPerId) || '
                 AND a.add_current = ' || vAddCurrentValue || '
                 AND (add_from IS NULL OR add_from <= trunc(SYSDATE))
                 AND (add_to   IS NULL OR add_to   >= trunc(SYSDATE))';

Is a bit nasty and that you might consider using binds:

vSQL:= 'SELECT ' || vAddressElement || '
          FROM vwe_mpi_address a   
          INNER JOIN vwe_mpi_addr_link  al                         
          ON a.add_id = al.alk_add_id
           WHERE al.alk_per_id  = :b1
                 AND a.add_current = :b2
                 AND (add_from IS NULL OR add_from <= trunc(SYSDATE))
                 AND (add_to   IS NULL OR add_to   >= trunc(SYSDATE))';

...
EXECUTE IMMEDIATE Rtrim(vSQL)
        INTO RESULT USING iPerId, vAddCurrentValue;

Otherwise you are opening yourself up to all kinds of possible problems.

I assume that you can be 110% SURE that the query will NEVER return more than 1 row?

EXCEPTION
      WHEN OTHERS THEN
        RESULT := NULL;

No offense, but this is a really, really horrible bit of code. Apart from the fact that it fails to perform a return, any WHEN OTHERS THEN that doesn't at least LOG the raised error is asking for trouble.

 

[seequal]

Hi,

Thanks for this, and your constructive comments (no offence taken).

I am a T-SQL Programmer and am extremely new to PL/SQL.

I am sure that the function will only ever return one row as the join is a one to one relationship. Not sure what you mean by 'fails to perform a return' as the function contains a return clause at the end

I will look further at the error handing and into binds and see what I can do.

Regards

 

[jimirvine]

Not sure what you mean by 'fails to perform a return' as the function contains a return clause at the end

Well obviously what I mean by that is that I am clearly getting old and decrepit and didn't actually notice the return in the outer block
However, the main 'complaint' about WHEN OTHERS stands. I personally don't like saying in my code:
If an error occurs in this code , just ignore it and continue processing anyway, don't even tell me that you found a problem.

 

[SantaMufasa]

You cannot write a value to a parameter in a function. It just doesn't work that way.

For accuracy and full disclosure, Jim, although it is extremely poor form to return an OUT argument from a function (besides the RETURN expression), you technically can write a value to a parmeter in a function.

create or replace function seequal (x number, y out number) return varchar2 is
begin
    y := x + 1;
    return x + 2;
end;
/

Function created.

set serveroutput on
declare
    a number;
    b number;
begin
    a := 11;
    dbms_output.put_line('Function results: "'||seequal(a,b)||'", "'||b||'".');
end;
/
Function results: "13", "12".

PL/SQL procedure successfully completed.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jimirvine]

LOL, fair play Dave, I should have stipulated
You cannot write a value to an in parameter in a function. It just doesn't work that way.

And for fullest full disclosure we should maybe also point out IN OUT parameters

 

[SantaMufasa]

<grin>

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]