Modifying an Oracle Ref Cursor

Question

Given:
Oracle 10.2g is the database
I have a table called emp.
emp has a VARCHAR2 column called SECRET.
SECRET might contain a plaintext string, or it might contain an encrypted string, but I can distinguish one from the other
A function called DECRYPT already exists that, given the encrypted string, will return an unencrypted string.

How can I write a function that will return a ref_cursor which always contains an unencrypted string in the SECRET column?

What I'm looking for in pseudocode is:

   use a cursor to get all the rows of emp
   for each row in emp
   see if SECRET is encrypted
   if yes, decrypt and store the unencrypted value back into SECRET
   if no, leave the row untouched
   return the cursor as a ref_cursor

Essentially what I want to do is modify a refcursor before it is returned. However, I don't think that's possible.

My first thought was to build up an associative array of cursor%ROWTYPE. That's fine. I can do that. However, I can't find a way to return an associative array as a refcursor.

Any thoughts on a strategy?

Answer 1

Create a "is_encrypted" function which returns if the string is encrypted, then use a case statement to return the decrypted value either via the decrypt function or just straight out of the table.

select case
       when is_encrypted(secret) = 'Y' then decrypt(secret)
       else secret end as ecrypted_secret
from emp

Or as suggested in question comments, just change the decrypt function so if its passed a non encrypted string, it just returns the string it was passed.