October 16, 2008

SQL Developer Data Modeling

Here are the first screenshots of the new data modeling tool, called Oracle SQL Developer Data Modeling (OSDM), which is part of the SQL Developer:


First of all I tested the capability of a table capture. Few clicks and he grabbed my datamodell and placed it in the relational-section of the OSDM.


Then you can "Engineer to Logical Model": After that you have an ER-Model:


Here is the link to the download-area on OTN: Oracle SQL Developer Data Modeling

I'll test more in the next days and write down the next experiences
Gerd

October 15, 2008

Views based on Year, Month and Day

While developing applications I need often list-of-values, which have a result-set of years, months or days.

You can hard-code those selects each time in each form, but much more elegant is to create views, which do the whole work.

Here are 3 views for years, months and days:


CREATE OR REPLACE FORCE VIEW YEARS_V
(YDATE) AS
SELECT add_months (trunc (sysdate, 'YYYY'), 12 * (50 - Level))
FROM Dual
CONNECT BY Level <= 100;

CREATE OR REPLACE FORCE VIEW MONTHS_V
(MDATE) AS
SELECT add_months (trunc (sysdate, 'MM'), 500 - Level)
FROM Dual
CONNECT BY Level <= 1000;

CREATE OR REPLACE FORCE VIEW DAYS_V
(DDATE) AS
SELECT trunc (sysdate) + 15000 - Level
FROM Dual
CONNECT BY Level <= 30000;


Those Views helps us to Select Data for the

- actual year +/- 50 years
- actual month +/- 500 months
- actual day +/- 15000 days

In a Record-Group you can use those views:

LOV of the next 10 years

SELECT YDATE
FROM Years_V
WHERE YDATE BETWEEN trunc (sysdate, 'YYYY')
AND add_months (trunc (sysdate, 'YYYY'), 10*12);


LOV of the last 30 and the next 10 days

SELECT DDATE
FROM Days_V
WHERE DDATE BETWEEN trunc (sysdate-30) AND trunc (sysdate+10);


Try it
Gerd