Search code examples
sqloracle-databasestored-procedures

How to convert this Query to Procedure?

I write following select query and it work find , and given out put.

    Select custname,contactno, enc_dec.decrypt(creditcardno,password) as  
creditcardno ,enc_dec.decrypt(income,password) as 
income from employees where custid=5;

enter image description here

Now I need to need to write procedure to this nameas 'retrieve_decrypt(5)' . I write procedure like this, it complied but when calling it not print result and given error.

    CREATE OR REPLACE  PROCEDURE retrieve_decrypt(
    custid  in NUMBER,
    decrypt_value out sys_refcursor
    ) 
    IS
   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  ;
     COMMIT;
   END;
/

I called it like this SELECT retrieve_decrypt(5) FROM DUAL; . enter image description here

need some expert help to resolve this. As this issue, I am struggle in lot of time.

Solution

  • You have created a procedure, not a function, so you cannot call it from a SQL statement. The arguments also don't match the definition.

    A simple way to test it, as you're using SQL Developer, is to use a ref-cursor bind variable, declared in the client:

    variable rc refcursor;
execute retrieve_decrypt(5, :rc);
print rc

    and then run all three lines as a script.

    Notice the colon before the rc when used as the procedure argument. Also note that variable, execute and print are all client commands. And execute is just shorthand for an anonymous PL/SQL block.

    A more general use for the procedure would be to call it from a PL/SQL block using a local refcursor variable, and loop over the result set, doing something with each row. It isn't clear what you want to do with them though.

    You could also turn the procedure into a function and return a refcursor, instead of having it as an out parameter:

    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;
/

    (untested) and you could then call that as you showed:

    SELECT retrieve_decrypt(5) FROM DUAL;

    but not all clients will display the result neatly. You can also call from a PL/SQL block and iterate over the results.

    However, if your custid is unique then the result set will be a single value, so you could use a scalar variables and out parameters instead. It isn't clear if that is the case though.