21st Century PL/SQL

May 27, 2005

KEYWORD name change

I'd like to suggest changing the keyword used to describe PL/SQL tables (i.e. varrays, nested tables, etc...) from TABLE to ARRAY or COLLECTION.

The word "TABLE" means to me, an Oracle table.

Posted by Giovanni Jaramillo at 03:16 PM | Add your thoughts (4)

June 02, 2005

htp.p and htp.f

Change these packages so that they write out the tags in lowercase so that the resulting HTML is XHTML compliant.

Posted by Greg Wenden at 10:35 AM | Add your thoughts (2)

July 21, 2005

date differences regarding leap year(s)

Create new built-in functions that take difference between two dates and can return the number of days:

1) including leap year days or 2) excluding leap year days

Here is some background on why this is needed:

I work with an application that relies on effective dates heavily and determining how many years based on 365 days (not 366 days since that does not occur every year) are involved. Having a function that takes two dates and returns the number of days, excluding any leap year days within the two dates would be nice.

2004 is a leap year, 2005 is not a leap year. This is what happens using Oracle's date arithmetic.

select TO_DATE('12/31/2004','MM/DD/YYYY') - TO_DATE('01/01/2004','MM/DD/YYYY') FROM DUAL -- 365 days

select TO_DATE('12/31/2005','MM/DD/YYYY') - TO_DATE('01/01/2005','MM/DD/YYYY') FROM DUAL -- 364 days
 
select TO_DATE('01/01/2005','MM/DD/YYYY') - TO_DATE('01/01/2004','MM/DD/YYYY') FROM DUAL -- 366 days

select TO_DATE('01/01/2006','MM/DD/YYYY') - TO_DATE('01/01/2005','MM/DD/YYYY') FROM DUAL -- 365 days
 
select TO_DATE('02/29/2004','MM/DD/YYYY') - TO_DATE('02/01/2004','MM/DD/YYYY') FROM DUAL -- 28

select TO_DATE('01/31/2004','MM/DD/YYYY') - TO_DATE('01/01/2004','MM/DD/YYYY') FROM DUAL -- 30

The timestamp used for the effective dates are all set to 00:00:00 in the database.

select TO_DATE('01/01/2005 00:00:00','MM/DD/YYYY HH24:MI:SS') - 
O_DATE('01/01/2004 00:00:00','MM/DD/YYYY HH24:MI:SS') FROM DUAL -- 366

This is what our application uses regarding effective dates:

select TO_CHAR(effective_from_date,'MM/DD/YYYY HH24:MI:SS'), TO_CHAR(effective_to_date,'MM/DD/YYYY HH24:MI:SS')
FROM pth
 
effective_from_date        effective_to_date
06/15/1997 00:00:00      06/15/1998 00:00:00

When I subtracted two dates that involved more than one leap year, Oracle's date arithmetic will just include each leap year day for each leap year within the two dates.

To work around the extra days for leap years...

1) Use the standard Oracle date arithmetic to get total days, including leap year.

2) Use INTERVAL function to bring P_DATE_IN_PAST to the year for P_DATE_FROM v_no_days := ( P_DATE_IN_PAST + INTERVAL '1' YEAR * number_of_365day_years_possible) - P_DATE_FROM;

3) Check if the year for either of the two dates is a leap year (PM_VERIFY_LEAP_YEAR function below...)

4) Check if the leap year day 02/29 is between the two dates (remaining code in function below...)

IF ( PM_VERIFY_LEAP_YEAR(TO_CHAR(  P_DATE_FROM, 'MM/DD/YYYY')) = TRUE AND
TO_DATE('02/29/'||TO_CHAR( P_DATE_FROM, 'YYYY'),'MM/DD/YYYY')

BETWEEN P_DATE_FROM AND P_DATE_TO )

OR

( PM_VERIFY_LEAP_YEAR(TO_CHAR( P_DATE_TO, 'MM/DD/YYYY')) = TRUE AND
TO_DATE('02/29/'||TO_CHAR( P_DATE_TO, 'YYYY'),'MM/DD/YYYY') 

BETWEEN P_DATE_FROM AND P_DATE_TO ) AND

(v_total_days IS NOT NULL and v_total_days <> 0) THEN
    v_total_days := v_total_days - 1;  /* subtract the extra leap year days */ 

Posted by Kathy Limbaugh at 02:02 PM | Add your thoughts (180)

August 17, 2005

make functions GREATEST and LEAST ignore NULLs

When trying to find extremes out of several fields and any of those fields happens to be NULL you don\'t regard your extreme as being NULL as well. However Oracles functions GREATEST and LEAST result in NULL whenever any of your parameters happens to be NULL.

It would be cool to have functions like GREATEST and LEAST that just ignore NULLs amongst its parameters and delever the extremes of all the parameters that are NOT NULL.

Posted by Bernhard Schwarz at 05:10 AM | Add your thoughts (76)

September 09, 2005

%ROWTYPE - SQL processing PL/SQL function stored objects

This is not completely a PL/SQL request, because it is related to Oracle SQL and PL/SQL functionality. It is regarding SQL SELECT statements with function calls to PL/SQL function stored objects.

I created a packaged function that has input parameters, two of which are defined as tablename%ROWTYPE.

funcname(P1 tab1.col1%TYPE,
                P2 tab2%ROWTYPE,
                P3 tab3%ROWTYPE);

I would like to be able to call the packaged function from SQL using the asterisk reference. The function has the %ROWTYPE formal parameter "datatype" defined:

SELECT t1.col1, t2.col5, t2.col6, t3.col2, ..., 
            funcname(t1.col1, t2.*, t3.*),
            t4.col5, etc....
FROM tab1 t1, tab2 t2, tab3 t3, tab4 t4
where tab1.col1 = tab2.col1
     and tab2.col2 = tab3.col2
    etc...

Posted by Kathy Limbaugh at 12:02 PM | Add your thoughts (0)

September 30, 2005

Support Unified SQL and PL/SQL Parser in Pro*C

Every Pro*C shop is being limited by the fact that the Pro*C development team has yet to implement the unified SQL parser. It\'s not been done in 10g and there are no plans to do so. This means our Pro*C code can\'t make use of some new features in both SQL and PL/SQL because we can\'t embed them directly in Pro*C -- the precompiler will throw an error. My only workaround is to completely hide the new usages in a package. I thnk it\'s important to the thousands of Pro*C shops that they stop being treated as the half-breed stepchild, and get their perferred product the full support it deserves.

Posted by Daniel Clamage at 12:16 PM | Add your thoughts (0)