CODEWITHSUNDEEP
databaseoracle-databasestored-proceduresstored-functions
uma
uma

Reputation: 1577

when passing parameter to function Return null in oracle?

This is my function

---Write procedure for retrive data----
CREATE OR REPLACE FUNCTION retrieve_decrypt(
    custid  in NUMBER
)
RETURN sys_refcursor
IS
    decrypt_value sys_refcursor;
BEGIN
     open decrypt_value for Select custname,contactno, enc_dec.decrypt(creditcardno,password) as  
       creditcardno ,enc_dec.decrypt(income,password) as 
            income  from employees where custid=custid ;
    RETURN decrypt_value;
END;
/

I called it like this : SELECT retrieve_decrypt(5) FROM DUAL; Then it return only {}.

enter image description here

but am I , hard code custid=5 insted of custid=custid .it will return result correctly. need some expert help to resolve this issue.

enter image description here

Upvotes: 0

Views: 43

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

Don't name parameters as columns; if column name is custid, let parameter be par_custid. Otherwise, it will cause problems (as you can see).

In your example:

CREATE OR REPLACE FUNCTION retrieve_decrypt (par_custid IN NUMBER)
   RETURN SYS_REFCURSOR
IS
   decrypt_value  SYS_REFCURSOR;
BEGIN
   OPEN decrypt_value FOR
      SELECT custname,
             contactno,
             enc_dec.decrypt (creditcardno, password) AS creditcardno,
             enc_dec.decrypt (income, password) AS income
        FROM employees
       WHERE custid = par_custid;

   RETURN decrypt_value;
END;
/

Upvotes: 1

Related Questions