4 Replies Latest reply: Mar 8, 2013 10:59 AM by 367852 RSS

    pl/sql  parameter best practice

    spur230
      While code review i notice in our system all our in parameter are reassigned to local variable and local variable is used in the body. Is there any benefit in doing so?

      I would have directly use parameter (pi_q_nm) without assigning to any other local variable because it is less coding.


      Eg.
      PROCEDURE sp_enqueue
       ( pi_q_nm        IN VARCHAR2, pi_proctor_msg IN t_proctor_msg )
      IS
      
      --Assigning parameter to local variables
      l_queue_nm VARCHAR2(64) := pi_q_nm;
      
      BEGIN
      dbms_aq.enqueue
        (
         queue_name =>         l_queue_nm);
      ...
      end;
      Oracle Database : 11.2.0.3

      Thanks for your time.
        • 1. Re: pl/sql  parameter best practice
          Hoek
          Is there any benefit in doing so?
          No.
          I would have directly use parameter (pi_q_nm) without assigning to any other local variable because it is less coding.
          Agreed.
          I recall an example on AskTom, going a bit more in-depth and stating more or less the same, I'll post that thread here as soon as I found it back.
          • 2. Re: pl/sql  parameter best practice
            Carlovski
            No real benefit. Only issue I can think is possibly if using NOCOPY parameters. Of course by making a copy anyway you are making the NOCOPY pretty redundant!
            • 3. Re: pl/sql  parameter best practice
              Marwim
              Hello,

              it is necessary to do so, when the parameter of the called procedure is an out parameter, because you cannot asign a value to the in parameter of the calling procedure. I don't know any other reason to do so.

              Regards
              Marcus
              • 4. Re: pl/sql  parameter best practice
                367852
                Recall one really huge procedure with 30+ parameters where this was done and the critical proc changed in every major and minor release. As the local variables were defined(for each parameter defined in the proc) we could pull the code out and quickly convert it into a sql block and assign values to the required variables for testing multiple scenarios quickly. Well, that may not be a justifiable reason to propagate it as a standard but it was definitely convenient in a way.