How to convert this Query to Procedure?

Question

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;

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

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

Perhaps you mean to create a function rather than a procedure. Take a look at this example: https://stackoverflow.com/questions/36484546/how-to-do-a-function-to-return-row-type-from-a-table-in-pl-sql — Glenn, Feb 15 '19 at 00:46

Alex Poole · Accepted Answer · 2019-02-15T01:11:30.407

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.

I try execute this, there are now any errors. but dosn't print any out put. — uma, Feb 15 '19 at 00:56
I've shown a function version. Did you run the procedure version as a script or as separate statements? — Alex Poole, Feb 15 '19 at 01:00
same script . I try to execute your second procedure, it give error near 3rd line return statement .(syntax error) — uma, Feb 15 '19 at 01:03
Oops, forgot the most important change! Fixed. But still untested. — Alex Poole, Feb 15 '19 at 01:07
Alex Pole , how can i call this function ? I called it select statement like this 'SELECT retrieve_decrypt(5) FROM DUAL;' it return now , {} bracket. — uma, Feb 15 '19 at 01:09

How to convert this Query to Procedure?

1 Answers1