21st Century PL/SQL

June 07, 2005

CHECK constraint for procedure/function parameters

I don't think I'm not the only one who validates procedure/function's input parameters. And in most cases it's a considerable part of the code. So I think it would be a nice feature if we could define such validation rules along with parameter lists. Just something like we do with check constraint for tables. I'm sure it would make program units more readable and maintainable.

Posted by Oleh Tishchenko at 07:04 AM | Add your thoughts (3)

August 19, 2005

Method Parameter Modifiers - a more reliable/transparent way to pass parameters

- Method Parameter Modifiers - a more reliable way to pass parameters - Hello, in PL/SQL we now the parameter modifieres

• IN : Default Call by Value
• OUT : Value is only assigned in the Method body
• IN OUT - NOCOPY : with NOCOPY a call by reference [/list] These modifiers are specified at the declaration of a method

     PROCEDURE foo( pVal IN <aType>);
  

  The poblem now is if you are call the procedure you do not really know if its a call by value, out or refererence. Hence you are not sure which side effect a call on the parameter will have. This is dangerous if you only use the PL/SQL Method and did not implement it. Followig example :

  DECLARE
    i PLS_INTEGER := 0;
  BEGIN
    i := 2 * 2;
    foo(i);
    dbms_output.put_line( i );
  END;
  /
  

  Looking on this code block you never know [list]
• value of i was passed to the procdure and that\'s it?
• value of i was not passed because of an out parameter?
• value of i was passed as reference and was changed? [/list] But developing solid software a code section should always be clear to a user/developer what really happens - without looking into the API description. In C# parameter modifiers are not only used in decalarion section but have to be used at the call of a Method.

      // the ans variabe is defined as *out*    
      public static void Add(int x, int y, out int ans)
      {                                                
          ans = x + y;                                
      }  
      
      
      ....
      
      int ans;                                                                                   
      // Note use of *out\' keyword in calling syntax   
      Add(90, 90, out ans);                        
      Console.WriteLine(\\"90 + 90 = {0} \\", ans);
      
  

  Loooking at this code you really know what happens. the C# compiler would not allow to compile the call like this :

  	
  	Add(90, 90, int ans);
  	
  

  What what i like to have ? [list]
• a pragma used in package/procdedure/function context which enables STRONG_PARAM_MODIFIER
• throwing compiler errors if the parameter modifier was not specified for out and ref parameters calling the Procdure/Function
• replacing IN OUT NOCOPY simple with the new Parameter modifier REF

Carl

Posted by Carl Reitschuster at 06:40 AM | Add your thoughts (3)

October 31, 2005

Auto store parameters in a pl/sql array

I would like to have a kernel filled prenamed in-memory table in every stored procedure, that holds all parameters and states their:

• name
• in and/or out
• type (inheritance (%TYPE) if given)
• default value
• invocation value In this way I can easily:
• store all invocation parameters in some metadata framework (without hard coding any parameter names)
• compare the currenct value with the default value without defining a package constant for implementing this

I have not been able to find a V$/X$ view that gives these details. I can implement this on dba_source, but do not want to. The default list could be named e.g. dbms_list in order to prevent clashes with existing plsql declarations.

Posted by erik ykema at 09:37 AM | Add your thoughts (4)

November 07, 2005

Need a way to tell if the value of a parameter was 1) passed in or was 2) assigned a default value.

I have a procedure whos job it is to update only those fields of a table that the calling module passes me a value for. The difficulty is that if you make the parameters optional by assigning each a default value (such as null), I still have no way of knowing if the user passed me a null or if the parameter was defaulted to null. One way of implementing this is to implement a built-in method for every parameter. The method could be named something like passed_in so that I could say something like if my_param.passed_in then..... The work-around to this issue is to default each of the parameters to some bogus value (such as -999999) and then check to see if the parameter is that value. If it is, then the parameter value was not passed in. This is not very intuitive. Besides, what if someday I want to use -999999 as an actual value for that parameter?

Posted by Rich Firman at 06:53 PM | Add your thoughts (1)