Tuesday, August 11, 2009

%ROWTYPE, Part II

In the last entry on the subject, Gary Meyers pointed me to this link. Not having used %ROWTYPE that often I was not aware of some of the features.

The 2 that interested me the most were the INSERT and UPDATE:
DECLARE
l_table MY_TABLE%ROWTYPE;
BEGIN
INSERT INTO my_table
VALUES l_table;
END;

DECLARE
l_table MY_TABLE%ROWTYPE;
BEGIN
UPDATE my_table
SET ROW = l_table
WHERE my_table_id = l_table.my_table_id;
END;
So I began to use them (despite the bad feeling I get...).

I started to receive errors however, NOT NULL constraint violations in fact. How could that be? Here's the definition of MY_TABLE
CREATE TABLE my_table
(
my_table_id NUMBER(10)
CONSTRAINT pk_mytableid PRIMARY KEY,
status VARCHAR2(1) DEFAULT 'A'
CONSTRAINT nn_status_mytable NOT NULL
CONSTRAINT ck_aori_status_mytable CHECK ( status IN ( 'I', 'A' ) ),
start_date DATE DEFAULT SYSDATE
CONSTRAINT nn_startdate_mytable NOT NULL
);
See the column DEFAULTs?

I would imagine these are functioning as expected, but I don't like it. One benefit I see to this method is that it saves a little bit of typing (despite the SELECT * feel), or so I thought. If you don't explicitly set the value like this:
  l_table.status := 'A';
l_table.start_date := SYSDATE;
you are hosed. Plus, what if the calling application had already set those, now you have this:
IF l_table.status IS NULL THEN
l_table.status := 'A';
END IF;

IF l_table.start_date IS NULL THEN
l_table.start_date := SYSDATE;
END IF;
So where is the savings in that?

6 comments:

Noons said...

The *theory* behind using %ROWTYPE is that if your table defines column status as VARCHAR2(1)and later on you do

ALTER TABLE my_table MODIFY status varchar2(10);

you then don't need to chase the PL/SQL code declarations of STATUS and change them as it will "know" it has changed length.

But as you well note, it has some short-comings as well: the constraints are not exactly propagated the same way...

oraclenerd said...

@noons

That is too bad, as it might make life a bit easier.

I don't think it will be a method I use in the future, but for this particular "clean-up" project, it's a good "first-pass" solution (as I don't know all the rules yet).

Crisatunity said...

I'm not a big fan of DEFAULT in table definitions anyway. Maybe they are OK for a timestamp column that is intended to be hidden from application usage anyway.

But when you are talking about default values for a record variable, you should be creating a "constructor" function that does that instead.

The risk of using DEFAULTs in your DDL is that the binding between their existence and your DML code is extremely opaque and a recipe for failure over time.

Crisatunity said...

also: they are OK for sys_guid() on raw(16) columns - because that is occasionally something very useful and the opaque pitfall is avoided if the column has NOT NULL and UNIQUE constraints.

oraclenerd said...

@cristaunit

could you explain what you mean by a "constructor?" I'm not sure I follow (may be just a difference in language).

I do consider the fact that I am binding this to the table definition and to change it requires DDL...I typically use it (DEFAULT values) on yes/no or on/off columns or those that have a lookup table (with the NOT NULL, FK constraints). Not necessarily items that would be exposed to the application.

chet

Crisatunity said...

re: "constructor"

Obviously PL/SQL record types don't have the OO feature of a constructor, but you can easily mimic that with a function that returns that record type.

That function could be overloaded as needed. That function could be more flexible and more transparent to the code that is using it than some DEFAULT values that are tricky to troubleshoot.

DEFAULT values in your DDL are generally a bad idea and should be considered suspect upon any review.