oracle plsql: retrieve runtime parameter values when you call a procedure

Posted by Luca Vaccaro on Stack Overflow See other posts from Stack Overflow or by Luca Vaccaro
Published on 2012-09-04T11:54:23Z Indexed on 2012/09/10 3:38 UTC
Read the original article Hit count: 231

I need a generalized method to get list of runtime parameters (values) when I call a procedure. I need something similar to the $$PLSQL_UNIT that returns the name of the running procedure. (plsql Oracle 10g)

E.g. look at this sample procedure: (it simply prints its own name and parameters )

CREATE OR REPLACE PROCEDURE  MY_PROC(ow in varchar2, tn IN varchar2)
 IS

BEGIN
     dbms_output.put_line('proc_name: '||$$PLSQL_UNIT||' parameters: '|| ow||' '||tn  );

    EXCEPTION
    WHEN OTHERS THEN 
       DBMS_OUTPUT.PUT_LINE('ERRORE: ' ||SQLERRM);

END MY_PROC;
/

Running procedure produces the following output:

SQL> 
  1   BEGIN
  2     IBAD_OWN.MY_PROC('first_par', 'second_par');
  3   END;
  4  /

proc_name: MY_PROC parameters: first_par second_par

PL/SQL procedure successfully completed.

I'm not satisfy because I can't copy and paste in all my procedures because I have to hard code each procedure to set their right parameter variables.

Thanks in advance for the help.

© Stack Overflow or respective owner

Related posts about Oracle

Related posts about stored-procedures