%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?

Labels: %ROWTYPE, development

  ¶ 8/11/2009 06:00:00 PM