Showing posts with label constructors. Show all posts
Showing posts with label constructors. Show all posts

Friday, February 05, 2010

11g Named notation in SQL for Constructor functions

One of the 11gR1 enhancements allowed the use of named notation in SQL function calls.

In 11g, you can do
  select utl_raw.cast_from_number(n=> 1) from dual;
In 10g, the same sql will error.

I prefer named notation. You can see in the statement what values are being passed where, and that makes mistakes more obvious. The lack of it for SQL calls wasn't generally a big deal though. Code is generally in packages, and you could always create a wrapper function with a different name. However there's one situation where you can't do that, and that concerns constructor functions.

When you create an SQL type, you get a default constructor where, if you pass in all the attributes in the correct order, it will return an instance of the object.
Example:
  select mdsys.sdo_point_type(1,2,3) from dual;
But sometimes you don't want to specify values for all the attributes, so you create a non-default constructor.

create type address_typ is object
  (street_number  varchar2(10),
  street_name     varchar2(50),
  locality        varchar2(50),
  postcode        varchar2(4),
  state           varchar2(3), 
  CONSTRUCTOR FUNCTION
    address_typ(pi_street_number in varchar2 default null, 

              pi_street_name in varchar2 default null,
              pi_locality in varchar2 default null, 
              pi_postcode in varchar2 default null, 
              pi_state in varchar2 default null)
  return self as result,
  CONSTRUCTOR FUNCTION address_typ(pi_wide in varchar2)
  return self as result
);
/

create type body address_typ is
  CONSTRUCTOR FUNCTION
  
address_typ(pi_street_number in varchar2 default null, 
              pi_street_name in varchar2 default null,
              pi_locality in varchar2 default null, 
              pi_postcode in varchar2 default null, 
              pi_state in varchar2 default null)
  return self as result is
  begin
    self.street_number  := pi_street_number;
    self.street_name    := pi_street_name;
    self.locality       := pi_locality;
    self.postcode       := pi_postcode;  
    self.state          := pi_state;
    return; 
  end address_typ;
  --
  CONSTRUCTOR FUNCTION address_typ(pi_wide in varchar2)
  return self as result is
  begin
    self.state := 'NSW'; --dummy line replacing complex parsing routine
    return;
  end address_typ;
end;
/

I have created a type with two constructors. The first replaces the default constructor so that I don't need to specify all the atrributes. The second is an alternative constructor (eg to parse a string into a properly formatted address).

In 10g, if I try
select address_typ('1') from dual;
I get "ORA-06553: PLS-307: too many declarations of 'ADDRESS_TYP' match this call". I can't rename either function as constructors need to have the same name as the object. In 10g, I'd be stuck. Or I'd have to create a function or packaged function to do the job, and end up with code splashed here and there.
.
In 11g, I'd still get the same error, but I have a workaround. I can use
select address_typ(pi_wide => '1') from dual;
As long as the specified parameter names point to a single function, I can use named notation to bypass the problem. Even with constructors, I've got freedom to name my parameters as I please.

Problem solved...for those on 11g at least