|
Data structures and datatypes
Offer ideas about new datatypes, enhancements to existing types and structures., etc.
Ideas within Data structures and datatypes
April 04, 2005
:new and :old pseudo records in triggers should be records of %rowtype
We could then separate easily event handling from code processing:
create or replace trigger emp_rb
before insert or update or delete
on table emp
for each row
begin
:new := emp_rules.process( :new, :old );
end emp_rb;
/
create or replace package emp_rules as
--
type tbl_type is table of emp%rowtype;
--
new_tbl tbl_type;
--
function process
( new emp%rowtype
, old emp%rowtype
) return emp%rowtype;
--
...
end emp_rules;
/
Posted by David Johnson at 11:19 AM
| Add your thoughts (2)
September 06, 2005
Be able to anchor the data length of a DB column
Like the way you can use .%TYPE, if often feel the need to be able to anchor the length separately as well, something like .%DATALENGTH. This way I can do things like this:
CREATE OR REPLACE FUNCTION act_subject (list_in IN stringtab)
RETURN act.subject%TYPE
IS
retval act.subject%TYPE;
maxlenretval act.subject%DATALENGTH; -- is a number containing the data length of the act.subject column
BEGIN
OPEN cur; -- some cursor that returns an unknown number of records
FETCH cur
INTO rec;
WHILE cur%FOUND AND LENGTH (retval || \\\', \\\' || rec.somestring) < maxlenretval
LOOP
retval := retval || \\\', \\\' || rec.somestring;
FETCH cur
INTO rec;
END LOOP;
CLOSE cur;
RETURN (retval);
END;
I know there are other ways to prevent a string to get too long (I could handle a ORA-06502 error), but somehow I find the above more elegant and descriptive.
Posted by Toine Beckhoven, van at 08:54 AM
| Add your thoughts (2)
October 24, 2005
NVL3(a) gives you a prefined value according to the type of the data in case a is null
I would like to have a NLV3(x) function that returns a predefined value if x is NULL, fitting with the datatype of x.
number: -9,99E125
char: chr(1)
date: 1-1-4712
etc.
I came up with this idea when working on a trigger and comparing various :old and :new values. I had to put those in NVL()s in order to have a succesfull comparison in the case of NULLs.
This would save me from looking up all datatypes every time I need to do such an NVL.
One could build this as an overloaded function:
CREATE OR REPLACE PACKAGE rdw25_adm_dm.foo
AS
FUNCTION nvl3(a IN NUMBER) RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES (nvl3, wnps, wnds);
FUNCTION nvl3(a IN VARCHAR2) RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES (nvl3, wnps, wnds);
END;
/
CREATE OR REPLACE PACKAGE BODY rdw25_adm_dm.foo
AS
FUNCTION nvl3(a IN NUMBER) RETURN NUMBER IS
BEGIN
if a is null then RETURN -9.99E125; end if;
END nvl3;
--
FUNCTION nvl3(a IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
if a is null then RETURN chr(1); end if;
END nvl3;
END;
/
Posted by erik ykema at 11:50 AM
| Add your thoughts (2)
| 