The steps I took where:
connect / as sysdba
alter database rename global_name to <sid><db_domain>;
alter system set db_domain=<db_domain> scope=spfile;
bounce the database
recreate the dbconsole repository as explained in this blogentry:
http://broersa.wordpress.com/2008/01/10/regenerate-database-console-repository/
After part 1 it is time to load the data in the database. We will use linq for it. In a previous article I I have shown how to get and build linq to oracle. With that in mind, we start creating the table. After this we create the linq proxy classes using dbmetal. And finally the code to degrees process.
Let’s start with the table:
drop table deg / create table deg ( degkey number not null, degdat number not null, degtim number not null, degcelcius number not null, constraint deg_pkey primary key (degkey), constraint degdat_fkey foreign key (degdat) references dat (datkey), constraint degtim_fkey foreign key (degtim) references tim (timkey) ) / drop sequence degkey_seq / create sequence degkey_seq start with 1 increment by 1 nomaxvalue / create or replace trigger degkey_trigger before insert on deg for each row begin select degkey_seq.nextval into :new.degkey from dual; end; /
After this we use dbmetal to generate the helper classes:
dbmetal /provider:oracle /conn:”Data Source=XE;User Id=gwe;Password=gwe;” /database=gwe /code:gwe.cs
The problem here is that the databasename is the same as a tablename in the schema. To solve this edit the gwe.cs file and change the DataContext name to GWE (all capitals). Also change the constructors to GWE.
Then the code to the degrees.cs:
using System;
using System.Web.Services.Protocols;
using System.Net;
using System.Xml;
using System.Text.RegularExpressions;
using System.Collections.Generic;
using System.Linq;
using DbLinq.Linq.Mapping;
using System.Data.OracleClient;
public class Degrees
{
public static void Main (string [] args)
{
try {
string degrees=”";
string success=”";
GlobalWeatherSoapClient w = new GlobalWeatherSoapClient();
// We load the string in an xml reader to parse it and find the temperature
XmlReader reader = XmlReader.Create(new System.IO.StringReader(w.GetWeather(“Groningen”,”Netherlands”)));
reader.MoveToContent();
// Parse the file and find the Temperature element its value
while (reader.Read())
{
if (reader.NodeType==XmlNodeType.Element && reader.Name==”Temperature”)
{
// Do some regex to find the temperature in celcius
reader.Read(); degrees = Regex.Replace(Regex.Replace(reader.Value,”^.*\\(“,”"),” C\\).*$”,”");
}
if (reader.NodeType==XmlNodeType.Element && reader.Name==”Status”)
{
reader.Read(); success = reader.Value.Trim();
}
}
reader.Close();
System.Console.WriteLine(degrees + success);
if (success==”Success”) {
System.DateTime d = System.DateTime.Now;
string connStr = “User Id=gwe;Password=gwe;Data Source=XE”;
GWE db = new GWE(new OracleConnection(connStr));
// get datkey
var q1 = (from p in db.Tims where p.TimHour==d.Hour select p).Single();
var q2 = (from p in db.Dat where p.DatYYYYMmDd==(d.Year*10000)+(d.Month*100)+d.Day select p).Single();
DEG deg = new DEG();
deg.DEGKey=1; // dummy
deg.DEGCeLcIUs=Convert.ToDecimal(degrees);
deg.DEGdAt=q2.DatKey;
deg.DEGTim=q1.TimKey;
db.DEG.InsertOnSubmit(deg);
db.SubmitChanges();
}
}
catch(Exception ex) // catch all exceptions so the batch continues
{
Console.WriteLine(ex.ToString());
}
}
}
build:
csc /r:dblinq.dll,dblinq.oracle.dll degrees.cs gwe.cs GlobalWeather.cs
And schedule every hour in windows.
Over here in Holland the utilities are getting more expensive everyday. Time for me to make a small registration tool to get some insigth information in the consumption of the resources. I want the resources (facts) to layout againsts dates and times. So I need two dimensions: date and time. I also need a fact table in which I register the usage of the resources every hour. Because it is not possible to get a sample of the meters every hour, I created a procedure which splits the difference between the previous and the current resource usage over the hours that are between. I know it isn’t the excact resource usage, but the more samples I have the more accurate it will get. The nice thing about splitting the usage over the hours is that querying the results gets very easy.
Over here in Holland we have another ‘problem’. We have peak and dew hours for the electricity. Also the certain holidays and the weekends are dew prices.
Lets start with creating the schema:
create user gwe identified by gwe;
grant connect,resources to gwe;
connect gwe/gwe
After this, create the dat dimension as showed in the previous blog.
Now the time dimension:
create table tim ( timkey number not null, timhour number(2) not null, timelekhoog varchar2(1) not null, constraint tim_pkey primary key (timkey) ) / drop sequence timkey_seq / create sequence timkey_seq start with 1 increment by 1 nomaxvalue / create or replace trigger timkey_trigger before insert on tim for each row begin select timkey_seq.nextval into :new.timkey from dual; end; / create unique index timhour_idx1 on tim (timhour) / insert into tim (timhour,timelekhoog) values (0,'Y'); insert into tim (timhour,timelekhoog) values (1,'Y'); insert into tim (timhour,timelekhoog) values (2,'Y'); insert into tim (timhour,timelekhoog) values (3,'Y'); insert into tim (timhour,timelekhoog) values (4,'Y'); insert into tim (timhour,timelekhoog) values (5,'Y'); insert into tim (timhour,timelekhoog) values (6,'Y'); insert into tim (timhour,timelekhoog) values (7,'N'); insert into tim (timhour,timelekhoog) values (8,'N'); insert into tim (timhour,timelekhoog) values (9,'N'); insert into tim (timhour,timelekhoog) values (10,'N'); insert into tim (timhour,timelekhoog) values (11,'N'); insert into tim (timhour,timelekhoog) values (12,'N'); insert into tim (timhour,timelekhoog) values (13,'N'); insert into tim (timhour,timelekhoog) values (14,'N'); insert into tim (timhour,timelekhoog) values (15,'N'); insert into tim (timhour,timelekhoog) values (16,'N'); insert into tim (timhour,timelekhoog) values (17,'N'); insert into tim (timhour,timelekhoog) values (18,'N'); insert into tim (timhour,timelekhoog) values (19,'N'); insert into tim (timhour,timelekhoog) values (20,'N'); insert into tim (timhour,timelekhoog) values (21,'N'); insert into tim (timhour,timelekhoog) values (22,'N'); insert into tim (timhour,timelekhoog) values (23,'Y'); commit /
Now the fact table and the foreignkeys:
create table gwe ( gwekey number not null, gwedat number not null, gwetim number not null, gweelekhoog number(10,5) not null, gweeleklaag number(10,5) not null, gwegas number(10,5) not null, gwewater number(10,5) not null, constraint gwe_pkey primary key (gwekey), constraint gwedat_fkey foreign key (gwedat) references dat (datkey), constraint gwetim_fkey foreign key (gwetim) references tim (timkey) ) / drop sequence gwekey_seq / create sequence gwekey_seq start with 1 increment by 1 nomaxvalue / create or replace trigger gwekey_trigger before insert on gwe for each row begin select gwekey_seq.nextval into :new.gwekey from dual; end; / declare l_datkey number; begin select datkey into l_datkey from dat where datdate=trunc(sysdate)-1; insert into gwe ( gwedat ,gwetim ,gweelekhoog ,gweeleklaag ,gwegas ,gwewater ) values ( l_datkey ,1 ,0 ,0 ,0 ,0 ); end; / commit;
The fact table is initialized with 0 values at the date yesterday.
Now we can use the following stored procedure to add meter measures.
create or replace procedure tussenstand( p_datumin date
,p_elekhoog number
,p_eleklaag number
,p_gas number
,p_water number
) as
l_prevdatum date;
l_diffelekhoog number(10,5);
l_diffeleklaag number(10,5);
l_diffgas number(10,5);
l_diffwater number(10,5);
l_inselekhoog number(10,5);
l_inseleklaag number(10,5);
l_datum date;
l_prevkey number;
l_aantalelekhoog number;
l_aantaleleklaag number;
l_aantalgas number;
l_aantalwater number;
l_elekhoog varchar2(1);
l_datkey number;
l_timkey number;
l_weekend varchar2(1);
l_feestdag varchar2(1);
begin
select max(gwekey)
into l_prevkey
from gwe
;
select p_elekhoog-sum(gweelekhoog)
,p_eleklaag-sum(gweeleklaag)
,p_gas-sum(gwegas)
,p_water-sum(gwewater)
into l_diffelekhoog
,l_diffeleklaag
,l_diffgas
,l_diffwater
from gwe
;
select datdate + (timhour/24)
into l_prevdatum
from gwe,dat,tim
where gwekey=l_prevkey
and gwedat=datkey
and gwetim=timkey
;
l_datum := l_prevdatum+(1/24);
l_aantalelekhoog := 0;
l_aantaleleklaag := 0;
l_aantalgas := 0;
l_aantalwater := 0;
while (l_datum<=p_datumin)
loop
select datweekend, datholiday
into l_weekend, l_feestdag
from dat
where datdate = trunc(l_datum)
;
select timelekhoog
into l_elekhoog
from tim
where timhour=to_number(to_char(l_datum,'HH24'))
;
if (l_elekhoog='N' or l_weekend='Y' or l_feestdag='Y') then
l_aantaleleklaag := l_aantaleleklaag + 1;
else
l_aantalelekhoog := l_aantalelekhoog + 1;
end if;
l_aantalgas := l_aantalgas + 1;
l_aantalwater := l_aantalwater + 1;
l_datum := l_datum + (1/24);
end loop;
if l_aantaleleklaag=0 then l_aantaleleklaag:=1; end if;
if l_aantalelekhoog=0 then l_aantalelekhoog:=1; end if;
if l_aantalwater=0 then l_aantalwater:=1; end if;
if l_aantalgas=0 then l_aantalgas:=1; end if;
l_diffgas := l_diffgas / l_aantalgas;
l_diffwater := l_diffwater / l_aantalwater;
l_diffeleklaag := l_diffeleklaag / l_aantaleleklaag;
l_diffelekhoog := l_diffelekhoog / l_aantalelekhoog;
l_datum := l_prevdatum + (1/24);
while (l_datum <= p_datumin)
loop
select datkey
,datweekend
,datholiday
into l_datkey
,l_weekend
,l_feestdag
from dat
where datdate = trunc(l_datum)
;
select timkey
,timelekhoog
into l_timkey
,l_elekhoog
from tim
where timhour=to_number(to_char(l_datum,'HH24'))
;
if (l_elekhoog='N' or l_weekend='Y' or l_feestdag='Y') then
l_inseleklaag := l_diffeleklaag;
l_inselekhoog := 0;
else
l_inselekhoog := l_diffelekhoog;
l_inseleklaag := 0;
end if;
insert into gwe (
gwedat
,gwetim
,gweelekhoog
,gweeleklaag
,gwegas
,gwewater
) values (
l_datkey
,l_timkey
,l_inselekhoog
,l_inseleklaag
,l_diffgas
,l_diffwater
);
l_datum := l_datum + (1/24);
end loop;
end;
/
After adding some measures, we can use the following views in our Business Intelligence tool to create nice graphs:
create or replace view elek as
select null link, datyyyyww label, sum(gweelekhoog+gweeleklaag) value
from "GWE"."GWE", "GWE"."DAT"
where gwedat=datkey and datwwcum >
( select max(datwwcum)-30
from gwe.dat, gwe.gwe where datkey=gwedat)
group by datwwcum,datyyyyww
order by datwwcum
/
create or replace view gas as
select null link, datyyyyww label, sum(gwegas) value
from "GWE"."GWE", "GWE"."DAT"
where gwedat=datkey and datwwcum >
( select max(datwwcum)-30
from gwe.dat, gwe.gwe where datkey=gwedat)
group by datwwcum,datyyyyww
order by datwwcum
/
create or replace view water as
select null link, datyyyyww label, sum(gwewater) value
from "GWE"."GWE", "GWE"."DAT"
where gwedat=datkey and datwwcum >
( select max(datwwcum)-30
from gwe.dat, gwe.gwe where datkey=gwedat)
group by datwwcum,datyyyyww
order by datwwcum
/
In my opinion it is always handy to have a detailed date dimension in a database where you can always join against. This is handy to get the current month or all mondays that aren’t holidays.
select datdate from dat where datyyyymm = (select datyyyymm from dat where datdate=trunc(sysdate));
select datdate from dat where datdayeng = ‘Monday’ and datholiday=’Y’ ;
in the underlying code the dat dimension is created.
create table dat
(
datkey number not null,
datdate date not null,
dattype varchar2(3) not null,
datyyyy number(4) not null,
datyyyymm number(6) not null,
datyyyymmdd number(8) not null,
datyyyyww number(8) not null,
datmm number(2) not null,
datww number(2) not null,
datdd number(2) not null,
datmmcum number not null,
datwwcum number not null,
datddcum number not null,
datmontheng varchar2(25) not null,
datmonthned varchar2(25) not null,
datdayofweek number(1) not null,
datdayeng varchar2(25) not null,
datdayned varchar2(25) not null,
datweekend varchar2(1) not null,
datholiday varchar2(1) not null,
constraint dat_pkey primary key (datkey)
)
/
drop sequence datkey_seq;
create sequence datkey_seq
start with 1
increment by 1
nomaxvalue
/
create or replace trigger datkey_trigger
before insert on dat
for each row
begin
select datkey_seq.nextval into :new.datkey from dual;
end;
/
create unique index datdate_idx1 on dat (datdate)
/
declare g_startdate date := '01-JAN-1950';
g_enddate date := '31-DEC-2100';
d date := g_startdate;
g_weekcum number := 0;
g_week number := 0;
begin
while d < g_enddate
loop
if (g_week <> to_char(d,'YYYYIW')) then
g_week := to_char(d,'YYYYIW');
g_weekcum := g_weekcum + 1;
end if;
insert into dat (
datdate
,dattype
,datyyyy
,datyyyymm
,datyyyymmdd
,datyyyyww
,datmm
,datww
,datdd
,datmmcum
,datwwcum
,datddcum
,datmontheng
,datmonthned
,datdayofweek
,datdayeng
,datdayned
,datweekend
,datholiday
) values (
d
,'DAT'
,to_char(d,'YYYY')
,to_char(d,'YYYYMM')
,to_char(d,'YYYYMMDD')
,to_char(d,'YYYYIW')
,to_char(d,'MM')
,to_char(d,'IW')
,to_char(d,'DD')
,trunc(months_between(d,g_startdate))
,g_weekcum
,d - to_date(g_startdate) + 1
,to_char(d,'FMMonth','NLS_DATE_LANGUAGE=American')
,to_char(d,'FMMonth','NLS_DATE_LANGUAGE=Dutch')
,to_char(d,'D') -- Monday first weekday
,to_char(d,'FMDay','NLS_DATE_LANGUAGE=American')
,to_char(d,'FMDay','NLS_DATE_LANGUAGE=Dutch')
,case when to_char(d,'FMDAY','NLS_DATE_LANGUAGE=American') = 'SATURDAY' then 'Y'
when to_char(d,'FMDAY','NLS_DATE_LANGUAGE=American') = 'SUNDAY' then 'Y'
else 'N'
end
,'N'
);
d := d + 1;
end loop;
end;
/
-- First day of week is monday
alter session set nls_territory = "the netherlands"
/
update dat set datdayofweek=to_char(datdate,'D')
/
-- Kerst
update dat set datholiday='Y' where datmm=12 and (datdd=25 or datdd=26)
/
-- Koninginnedag
update dat set datholiday='Y' where datmm=4 and ((datdd=30 and datdayofweek!=7) or (datdd=29 and datdayofweek=6))
/
-- 5 mei
update dat set datholiday='Y' where datmm=5 and mod(datyyyy,5)=0
/
update dat set datholiday='Y' where datdate=to_date('19500409','YYYYMMDD') or datdate-1=to_date('19500409','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19500409','YYYYMMDD') or datdate-1=to_date('19500409','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19510325','YYYYMMDD') or datdate-1=to_date('19510325','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19520413','YYYYMMDD') or datdate-1=to_date('19520413','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19530405','YYYYMMDD') or datdate-1=to_date('19530405','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19540418','YYYYMMDD') or datdate-1=to_date('19540418','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19550410','YYYYMMDD') or datdate-1=to_date('19550410','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19560401','YYYYMMDD') or datdate-1=to_date('19560401','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19570421','YYYYMMDD') or datdate-1=to_date('19570421','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19580406','YYYYMMDD') or datdate-1=to_date('19580406','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19590329','YYYYMMDD') or datdate-1=to_date('19590329','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19600417','YYYYMMDD') or datdate-1=to_date('19600417','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19610402','YYYYMMDD') or datdate-1=to_date('19610402','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19620422','YYYYMMDD') or datdate-1=to_date('19620422','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19630414','YYYYMMDD') or datdate-1=to_date('19630414','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19640329','YYYYMMDD') or datdate-1=to_date('19640329','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19650418','YYYYMMDD') or datdate-1=to_date('19650418','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19660410','YYYYMMDD') or datdate-1=to_date('19660410','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19670326','YYYYMMDD') or datdate-1=to_date('19670326','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19680414','YYYYMMDD') or datdate-1=to_date('19680414','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19690406','YYYYMMDD') or datdate-1=to_date('19690406','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19700329','YYYYMMDD') or datdate-1=to_date('19700329','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19710411','YYYYMMDD') or datdate-1=to_date('19710411','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19720402','YYYYMMDD') or datdate-1=to_date('19720402','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19730422','YYYYMMDD') or datdate-1=to_date('19730422','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19740414','YYYYMMDD') or datdate-1=to_date('19740414','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19750330','YYYYMMDD') or datdate-1=to_date('19750330','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19760418','YYYYMMDD') or datdate-1=to_date('19760418','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19770410','YYYYMMDD') or datdate-1=to_date('19770410','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19780326','YYYYMMDD') or datdate-1=to_date('19780326','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19790415','YYYYMMDD') or datdate-1=to_date('19790415','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19800406','YYYYMMDD') or datdate-1=to_date('19800406','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19810419','YYYYMMDD') or datdate-1=to_date('19810419','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19820411','YYYYMMDD') or datdate-1=to_date('19820411','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19830403','YYYYMMDD') or datdate-1=to_date('19830403','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19840422','YYYYMMDD') or datdate-1=to_date('19840422','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19850407','YYYYMMDD') or datdate-1=to_date('19850407','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19860330','YYYYMMDD') or datdate-1=to_date('19860330','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19870419','YYYYMMDD') or datdate-1=to_date('19870419','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19880403','YYYYMMDD') or datdate-1=to_date('19880403','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19890326','YYYYMMDD') or datdate-1=to_date('19890326','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19900415','YYYYMMDD') or datdate-1=to_date('19900415','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19910331','YYYYMMDD') or datdate-1=to_date('19910331','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19920419','YYYYMMDD') or datdate-1=to_date('19920419','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19930411','YYYYMMDD') or datdate-1=to_date('19930411','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19940403','YYYYMMDD') or datdate-1=to_date('19940403','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19950416','YYYYMMDD') or datdate-1=to_date('19950416','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19960407','YYYYMMDD') or datdate-1=to_date('19960407','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19970330','YYYYMMDD') or datdate-1=to_date('19970330','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19980412','YYYYMMDD') or datdate-1=to_date('19980412','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19990404','YYYYMMDD') or datdate-1=to_date('19990404','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20000423','YYYYMMDD') or datdate-1=to_date('20000423','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20010415','YYYYMMDD') or datdate-1=to_date('20010415','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20020331','YYYYMMDD') or datdate-1=to_date('20020331','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20030420','YYYYMMDD') or datdate-1=to_date('20030420','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20040411','YYYYMMDD') or datdate-1=to_date('20040411','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20050327','YYYYMMDD') or datdate-1=to_date('20050327','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20060416','YYYYMMDD') or datdate-1=to_date('20060416','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20070408','YYYYMMDD') or datdate-1=to_date('20070408','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20080323','YYYYMMDD') or datdate-1=to_date('20080323','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20090412','YYYYMMDD') or datdate-1=to_date('20090412','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20100404','YYYYMMDD') or datdate-1=to_date('20100404','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20110424','YYYYMMDD') or datdate-1=to_date('20110424','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20120408','YYYYMMDD') or datdate-1=to_date('20120408','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20130331','YYYYMMDD') or datdate-1=to_date('20130331','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20140420','YYYYMMDD') or datdate-1=to_date('20140420','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20150405','YYYYMMDD') or datdate-1=to_date('20150405','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20160327','YYYYMMDD') or datdate-1=to_date('20160327','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20170416','YYYYMMDD') or datdate-1=to_date('20170416','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20180401','YYYYMMDD') or datdate-1=to_date('20180401','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20190421','YYYYMMDD') or datdate-1=to_date('20190421','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20200412','YYYYMMDD') or datdate-1=to_date('20200412','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20210404','YYYYMMDD') or datdate-1=to_date('20210404','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20220417','YYYYMMDD') or datdate-1=to_date('20220417','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20230409','YYYYMMDD') or datdate-1=to_date('20230409','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20240331','YYYYMMDD') or datdate-1=to_date('20240331','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20250420','YYYYMMDD') or datdate-1=to_date('20250420','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20260405','YYYYMMDD') or datdate-1=to_date('20260405','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20270328','YYYYMMDD') or datdate-1=to_date('20270328','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20280416','YYYYMMDD') or datdate-1=to_date('20280416','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20290401','YYYYMMDD') or datdate-1=to_date('20290401','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20300421','YYYYMMDD') or datdate-1=to_date('20300421','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20310413','YYYYMMDD') or datdate-1=to_date('20310413','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20320328','YYYYMMDD') or datdate-1=to_date('20320328','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20330417','YYYYMMDD') or datdate-1=to_date('20330417','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20340409','YYYYMMDD') or datdate-1=to_date('20340409','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20350325','YYYYMMDD') or datdate-1=to_date('20350325','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20360413','YYYYMMDD') or datdate-1=to_date('20360413','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20370405','YYYYMMDD') or datdate-1=to_date('20370405','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20380425','YYYYMMDD') or datdate-1=to_date('20380425','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20390410','YYYYMMDD') or datdate-1=to_date('20390410','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20400401','YYYYMMDD') or datdate-1=to_date('20400401','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20410421','YYYYMMDD') or datdate-1=to_date('20410421','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20420406','YYYYMMDD') or datdate-1=to_date('20420406','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20430329','YYYYMMDD') or datdate-1=to_date('20430329','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20440417','YYYYMMDD') or datdate-1=to_date('20440417','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20450409','YYYYMMDD') or datdate-1=to_date('20450409','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20460325','YYYYMMDD') or datdate-1=to_date('20460325','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20470414','YYYYMMDD') or datdate-1=to_date('20470414','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20480405','YYYYMMDD') or datdate-1=to_date('20480405','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20490418','YYYYMMDD') or datdate-1=to_date('20490418','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20500410','YYYYMMDD') or datdate-1=to_date('20500410','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20510402','YYYYMMDD') or datdate-1=to_date('20510402','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20520421','YYYYMMDD') or datdate-1=to_date('20520421','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20530406','YYYYMMDD') or datdate-1=to_date('20530406','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20540329','YYYYMMDD') or datdate-1=to_date('20540329','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20550418','YYYYMMDD') or datdate-1=to_date('20550418','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20560402','YYYYMMDD') or datdate-1=to_date('20560402','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20570422','YYYYMMDD') or datdate-1=to_date('20570422','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20580414','YYYYMMDD') or datdate-1=to_date('20580414','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20590330','YYYYMMDD') or datdate-1=to_date('20590330','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20600418','YYYYMMDD') or datdate-1=to_date('20600418','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20610410','YYYYMMDD') or datdate-1=to_date('20610410','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20620326','YYYYMMDD') or datdate-1=to_date('20620326','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20630415','YYYYMMDD') or datdate-1=to_date('20630415','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20640406','YYYYMMDD') or datdate-1=to_date('20640406','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20650329','YYYYMMDD') or datdate-1=to_date('20650329','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20660411','YYYYMMDD') or datdate-1=to_date('20660411','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20670403','YYYYMMDD') or datdate-1=to_date('20670403','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20680422','YYYYMMDD') or datdate-1=to_date('20680422','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20690414','YYYYMMDD') or datdate-1=to_date('20690414','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20700330','YYYYMMDD') or datdate-1=to_date('20700330','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20710419','YYYYMMDD') or datdate-1=to_date('20710419','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20720410','YYYYMMDD') or datdate-1=to_date('20720410','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20730326','YYYYMMDD') or datdate-1=to_date('20730326','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20740415','YYYYMMDD') or datdate-1=to_date('20740415','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20750407','YYYYMMDD') or datdate-1=to_date('20750407','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20760419','YYYYMMDD') or datdate-1=to_date('20760419','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20770411','YYYYMMDD') or datdate-1=to_date('20770411','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20780403','YYYYMMDD') or datdate-1=to_date('20780403','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20790423','YYYYMMDD') or datdate-1=to_date('20790423','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20800407','YYYYMMDD') or datdate-1=to_date('20800407','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20810330','YYYYMMDD') or datdate-1=to_date('20810330','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20820419','YYYYMMDD') or datdate-1=to_date('20820419','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20830404','YYYYMMDD') or datdate-1=to_date('20830404','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20840326','YYYYMMDD') or datdate-1=to_date('20840326','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20850415','YYYYMMDD') or datdate-1=to_date('20850415','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20860331','YYYYMMDD') or datdate-1=to_date('20860331','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20870420','YYYYMMDD') or datdate-1=to_date('20870420','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20880411','YYYYMMDD') or datdate-1=to_date('20880411','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20890403','YYYYMMDD') or datdate-1=to_date('20890403','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20900416','YYYYMMDD') or datdate-1=to_date('20900416','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20910408','YYYYMMDD') or datdate-1=to_date('20910408','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20920330','YYYYMMDD') or datdate-1=to_date('20920330','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20930412','YYYYMMDD') or datdate-1=to_date('20930412','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20940404','YYYYMMDD') or datdate-1=to_date('20940404','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20950424','YYYYMMDD') or datdate-1=to_date('20950424','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20960415','YYYYMMDD') or datdate-1=to_date('20960415','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20970331','YYYYMMDD') or datdate-1=to_date('20970331','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20980420','YYYYMMDD') or datdate-1=to_date('20980420','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20990412','YYYYMMDD') or datdate-1=to_date('20990412','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('21000328','YYYYMMDD') or datdate-1=to_date('21000328','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19500518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19510503','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19520522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19530514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19540527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19550519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19560510','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19570530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19580515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19590507','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19600526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19610511','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19620531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19630523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19640507','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19650527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19660519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19670504','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19680523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19690515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19700507','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19710520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19720511','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19730531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19740523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19750508','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19760527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19770519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19780504','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19790524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19800515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19810528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19820520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19830512','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19840531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19850516','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19860508','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19870528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19880512','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19890504','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19900524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19910509','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19920528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19930520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19940512','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19950525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19960516','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19970508','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19980521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19990513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20000601','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20010524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20020509','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20030529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20040520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20050505','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20060525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20070517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20080501','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20090521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20100513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20110602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20120517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20130509','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20140529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20150514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20160505','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20170525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20180510','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20190530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20200521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20210513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20220526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20230518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20240509','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20250529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20260514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20270506','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20280525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20290510','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20300530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20310522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20320506','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20330526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20340518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20350503','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20360522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20370514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20380603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20390519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20400510','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20410530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20420515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20430507','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20440526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20450518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20460503','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20470523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20480514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20490527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20500519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20510511','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20520530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20530515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20540507','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20550527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20560511','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20570531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20580523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20590508','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20600527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20610519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20620504','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20630524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20640515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20650507','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20660520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20670512','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20680531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20690523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20700508','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20710528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20720519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20730504','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20740524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20750516','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20760528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20770520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20780512','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20790601','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20800516','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20810508','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20820528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20830513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20840504','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20850524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20860509','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20870529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20880520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20890512','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20900525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20910517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20920508','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20930521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20940513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20950602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20960524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20970509','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20980529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20990521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('21000506','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19500528','YYYYMMDD') or datdate-1=to_date('19500528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19510513','YYYYMMDD') or datdate-1=to_date('19510513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19520601','YYYYMMDD') or datdate-1=to_date('19520601','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19530524','YYYYMMDD') or datdate-1=to_date('19530524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19540606','YYYYMMDD') or datdate-1=to_date('19540606','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19550529','YYYYMMDD') or datdate-1=to_date('19550529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19560520','YYYYMMDD') or datdate-1=to_date('19560520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19570609','YYYYMMDD') or datdate-1=to_date('19570609','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19580525','YYYYMMDD') or datdate-1=to_date('19580525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19590517','YYYYMMDD') or datdate-1=to_date('19590517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19600605','YYYYMMDD') or datdate-1=to_date('19600605','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19610521','YYYYMMDD') or datdate-1=to_date('19610521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19620610','YYYYMMDD') or datdate-1=to_date('19620610','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19630602','YYYYMMDD') or datdate-1=to_date('19630602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19640517','YYYYMMDD') or datdate-1=to_date('19640517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19650606','YYYYMMDD') or datdate-1=to_date('19650606','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19660529','YYYYMMDD') or datdate-1=to_date('19660529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19670514','YYYYMMDD') or datdate-1=to_date('19670514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19680602','YYYYMMDD') or datdate-1=to_date('19680602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19690525','YYYYMMDD') or datdate-1=to_date('19690525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19700517','YYYYMMDD') or datdate-1=to_date('19700517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19710530','YYYYMMDD') or datdate-1=to_date('19710530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19720521','YYYYMMDD') or datdate-1=to_date('19720521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19730610','YYYYMMDD') or datdate-1=to_date('19730610','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19740602','YYYYMMDD') or datdate-1=to_date('19740602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19750518','YYYYMMDD') or datdate-1=to_date('19750518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19760606','YYYYMMDD') or datdate-1=to_date('19760606','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19770529','YYYYMMDD') or datdate-1=to_date('19770529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19780514','YYYYMMDD') or datdate-1=to_date('19780514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19790603','YYYYMMDD') or datdate-1=to_date('19790603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19800525','YYYYMMDD') or datdate-1=to_date('19800525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19810607','YYYYMMDD') or datdate-1=to_date('19810607','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19820530','YYYYMMDD') or datdate-1=to_date('19820530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19830522','YYYYMMDD') or datdate-1=to_date('19830522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19840610','YYYYMMDD') or datdate-1=to_date('19840610','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19850526','YYYYMMDD') or datdate-1=to_date('19850526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19860518','YYYYMMDD') or datdate-1=to_date('19860518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19870607','YYYYMMDD') or datdate-1=to_date('19870607','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19880522','YYYYMMDD') or datdate-1=to_date('19880522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19890514','YYYYMMDD') or datdate-1=to_date('19890514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19900603','YYYYMMDD') or datdate-1=to_date('19900603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19910519','YYYYMMDD') or datdate-1=to_date('19910519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19920607','YYYYMMDD') or datdate-1=to_date('19920607','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19930530','YYYYMMDD') or datdate-1=to_date('19930530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19940522','YYYYMMDD') or datdate-1=to_date('19940522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19950604','YYYYMMDD') or datdate-1=to_date('19950604','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19960526','YYYYMMDD') or datdate-1=to_date('19960526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19970518','YYYYMMDD') or datdate-1=to_date('19970518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19980531','YYYYMMDD') or datdate-1=to_date('19980531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19990523','YYYYMMDD') or datdate-1=to_date('19990523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20000611','YYYYMMDD') or datdate-1=to_date('20000611','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20010603','YYYYMMDD') or datdate-1=to_date('20010603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20020519','YYYYMMDD') or datdate-1=to_date('20020519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20030608','YYYYMMDD') or datdate-1=to_date('20030608','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20040530','YYYYMMDD') or datdate-1=to_date('20040530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20050515','YYYYMMDD') or datdate-1=to_date('20050515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20060604','YYYYMMDD') or datdate-1=to_date('20060604','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20070527','YYYYMMDD') or datdate-1=to_date('20070527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20080511','YYYYMMDD') or datdate-1=to_date('20080511','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20090531','YYYYMMDD') or datdate-1=to_date('20090531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20100523','YYYYMMDD') or datdate-1=to_date('20100523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20110612','YYYYMMDD') or datdate-1=to_date('20110612','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20120527','YYYYMMDD') or datdate-1=to_date('20120527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20130519','YYYYMMDD') or datdate-1=to_date('20130519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20140608','YYYYMMDD') or datdate-1=to_date('20140608','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20150524','YYYYMMDD') or datdate-1=to_date('20150524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20160515','YYYYMMDD') or datdate-1=to_date('20160515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20170604','YYYYMMDD') or datdate-1=to_date('20170604','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20180520','YYYYMMDD') or datdate-1=to_date('20180520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20190609','YYYYMMDD') or datdate-1=to_date('20190609','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20200531','YYYYMMDD') or datdate-1=to_date('20200531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20210523','YYYYMMDD') or datdate-1=to_date('20210523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20220605','YYYYMMDD') or datdate-1=to_date('20220605','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20230528','YYYYMMDD') or datdate-1=to_date('20230528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20240519','YYYYMMDD') or datdate-1=to_date('20240519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20250608','YYYYMMDD') or datdate-1=to_date('20250608','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20260524','YYYYMMDD') or datdate-1=to_date('20260524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20270516','YYYYMMDD') or datdate-1=to_date('20270516','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20280604','YYYYMMDD') or datdate-1=to_date('20280604','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20290520','YYYYMMDD') or datdate-1=to_date('20290520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20300609','YYYYMMDD') or datdate-1=to_date('20300609','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20310601','YYYYMMDD') or datdate-1=to_date('20310601','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20320516','YYYYMMDD') or datdate-1=to_date('20320516','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20330605','YYYYMMDD') or datdate-1=to_date('20330605','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20340528','YYYYMMDD') or datdate-1=to_date('20340528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20350513','YYYYMMDD') or datdate-1=to_date('20350513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20360601','YYYYMMDD') or datdate-1=to_date('20360601','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20370524','YYYYMMDD') or datdate-1=to_date('20370524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20380613','YYYYMMDD') or datdate-1=to_date('20380613','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20390529','YYYYMMDD') or datdate-1=to_date('20390529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20400520','YYYYMMDD') or datdate-1=to_date('20400520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20410609','YYYYMMDD') or datdate-1=to_date('20410609','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20420525','YYYYMMDD') or datdate-1=to_date('20420525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20430517','YYYYMMDD') or datdate-1=to_date('20430517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20440605','YYYYMMDD') or datdate-1=to_date('20440605','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20450528','YYYYMMDD') or datdate-1=to_date('20450528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20460513','YYYYMMDD') or datdate-1=to_date('20460513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20470602','YYYYMMDD') or datdate-1=to_date('20470602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20480524','YYYYMMDD') or datdate-1=to_date('20480524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20490606','YYYYMMDD') or datdate-1=to_date('20490606','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20500529','YYYYMMDD') or datdate-1=to_date('20500529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20510521','YYYYMMDD') or datdate-1=to_date('20510521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20520609','YYYYMMDD') or datdate-1=to_date('20520609','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20530525','YYYYMMDD') or datdate-1=to_date('20530525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20540517','YYYYMMDD') or datdate-1=to_date('20540517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20550606','YYYYMMDD') or datdate-1=to_date('20550606','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20560521','YYYYMMDD') or datdate-1=to_date('20560521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20570610','YYYYMMDD') or datdate-1=to_date('20570610','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20580602','YYYYMMDD') or datdate-1=to_date('20580602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20590518','YYYYMMDD') or datdate-1=to_date('20590518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20600606','YYYYMMDD') or datdate-1=to_date('20600606','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20610529','YYYYMMDD') or datdate-1=to_date('20610529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20620514','YYYYMMDD') or datdate-1=to_date('20620514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20630603','YYYYMMDD') or datdate-1=to_date('20630603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20640525','YYYYMMDD') or datdate-1=to_date('20640525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20650517','YYYYMMDD') or datdate-1=to_date('20650517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20660530','YYYYMMDD') or datdate-1=to_date('20660530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20670522','YYYYMMDD') or datdate-1=to_date('20670522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20680610','YYYYMMDD') or datdate-1=to_date('20680610','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20690602','YYYYMMDD') or datdate-1=to_date('20690602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20700518','YYYYMMDD') or datdate-1=to_date('20700518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20710607','YYYYMMDD') or datdate-1=to_date('20710607','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20720529','YYYYMMDD') or datdate-1=to_date('20720529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20730514','YYYYMMDD') or datdate-1=to_date('20730514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20740603','YYYYMMDD') or datdate-1=to_date('20740603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20750526','YYYYMMDD') or datdate-1=to_date('20750526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20760607','YYYYMMDD') or datdate-1=to_date('20760607','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20770530','YYYYMMDD') or datdate-1=to_date('20770530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20780522','YYYYMMDD') or datdate-1=to_date('20780522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20790611','YYYYMMDD') or datdate-1=to_date('20790611','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20800526','YYYYMMDD') or datdate-1=to_date('20800526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20810518','YYYYMMDD') or datdate-1=to_date('20810518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20820607','YYYYMMDD') or datdate-1=to_date('20820607','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20830523','YYYYMMDD') or datdate-1=to_date('20830523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20840514','YYYYMMDD') or datdate-1=to_date('20840514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20850603','YYYYMMDD') or datdate-1=to_date('20850603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20860519','YYYYMMDD') or datdate-1=to_date('20860519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20870608','YYYYMMDD') or datdate-1=to_date('20870608','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20880530','YYYYMMDD') or datdate-1=to_date('20880530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20890522','YYYYMMDD') or datdate-1=to_date('20890522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20900604','YYYYMMDD') or datdate-1=to_date('20900604','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20910527','YYYYMMDD') or datdate-1=to_date('20910527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20920518','YYYYMMDD') or datdate-1=to_date('20920518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20930531','YYYYMMDD') or datdate-1=to_date('20930531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20940523','YYYYMMDD') or datdate-1=to_date('20940523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20950612','YYYYMMDD') or datdate-1=to_date('20950612','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20960603','YYYYMMDD') or datdate-1=to_date('20960603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20970519','YYYYMMDD') or datdate-1=to_date('20970519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20980608','YYYYMMDD') or datdate-1=to_date('20980608','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20990531','YYYYMMDD') or datdate-1=to_date('20990531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('21000516','YYYYMMDD') or datdate-1=to_date('21000516','YYYYMMDD');
commit
/
I think it is handy to create this dimension in it’s own schema:
create user dat identified by dat;
grant connect,resource to dat;
grant create public synonym to dat;
connect dat/dat
run the above script
grant select on dat to public;
create public synonym dat for dat;
Now everyone can use the dat dimension.
We build on previous sample. In this example we add an autogenerated primary key. This is done by adding a sequence and an insert trigger in oracle:
SQL> create sequence degrees_seq 2 start with 1 3 increment by 1 4 nomaxvalue; Sequence created. SQL> SQL> create trigger degrees_trigger 2 before insert on degrees 3 for each row 4 begin 5 select degrees_seq.nextval into :new.degkey from dual; 6 end; 7 / Trigger created. SQL>
As we can see in linq.cs generated file the column DEGKey is attached with the IsDbGenerated = true attribute. This says that the .Net environment knows the column is updated from the database, which is what we want.
Now we get a new sample oracletest2.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using DbLinq.Linq.Mapping;
using System.Data.OracleClient;
class OracleTest
{
static void Main(string[] args)
{
string connStr = “User Id=linq;Password=linq;Data Source=XE”;
Linq db = new Linq(new OracleConnection(connStr)); // Linq is the mapperclass created with DbMetal
Console.WriteLine(“Insert temperature 1:”);
Degrees a = new Degrees();
a.DEGKey = 1; // Must be set but doesn’t matter because is database generated
a.DEGDate = System.DateTime.Now;
a.DEGCeLcIUs = 1;
db.Degrees.InsertOnSubmit(a);
System.Threading.Thread.Sleep(5000);
Console.WriteLine(“Insert temperature 2:”);
Degrees b = new Degrees();
b.DEGKey = 1; // Must be set but doesn’t matter because is database generated
b.DEGDate = System.DateTime.Now;
b.DEGCeLcIUs = 2;
db.Degrees.InsertOnSubmit(b);
System.Threading.Thread.Sleep(5000);
Console.WriteLine(“Insert temperature 3:”);
Degrees c = new Degrees();
c.DEGKey = 1; // Must be set but doesn’t matter because is database generated
c.DEGDate = System.DateTime.Now;
c.DEGCeLcIUs = 3;
db.Degrees.InsertOnSubmit(c);
Console.WriteLine(“Key values before submit:”);
Console.WriteLine(“a.DEGKey:”+a.DEGKey);
Console.WriteLine(“b.DEGKey:”+b.DEGKey);
Console.WriteLine(“c.DEGKey:”+c.DEGKey);
db.SubmitChanges();
Console.WriteLine(“Key values after submit (Should be synchronized but are not)”);
Console.WriteLine(“a.DEGKey:”+a.DEGKey);
Console.WriteLine(“b.DEGKey:”+b.DEGKey);
Console.WriteLine(“c.DEGKey:”+c.DEGKey);
Console.WriteLine(“——”);
Console.WriteLine(“Select all temperatures (now the keys are synced): “);
var q3 = from p in db.Degrees select p;
Console.WriteLine(“Fired sql:”);
Console.WriteLine(db.GetQueryText(q3));
Console.WriteLine(“Result:”);
Console.WriteLine(“DEGKEY – DEGDATE – DEGCELCIUS”);
foreach (var v in q3)
{
Console.WriteLine(v.DEGKey + ” – ” + v.DEGDate + ” – ” + v.DEGCeLcIUs); // For some reason DbMetal makes a very cryptic property name
}
Console.WriteLine(“——”);
}
}
Mind the comments in the code. A flaw in dblinq is that on the SubmitChanged the primarykey field should be updated which aren’t. For the rest it works nice.
build:
csc /r:dblinq.dll,dblinq.oracle.dll linq.cs oracletest2.cs
and run:
oracletest2.exe
have fun..
Start with a database in my situation XE on localhost.
C:\>sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production SQL> create user linq identified by linq; User created. SQL> grant connect,resource to linq; Grant succeeded. SQL> connect linq/linq Connected. SQL> create table degrees 2 ( 3 degkey number primary key 4 ,degdate date 5 ,degcelcius number 6 ) 7 / Table created. SQL>
Download the dblinq library from : http://code2code.net/DB_Linq/
or get the latest via svn which has VisualMetal:
svn checkout http://dblinq2007.googlecode.com/svn/trunk/
Goto the directory and do the following to build the oracle libraries:
cd DbLinq.Oracle
msbuild
This will build the DbLinq.dll and DbLinq.Oracle.dll in bin\Debug
cd DbMetal
msbuild
This will build the DbMetal tool which creates the database to objects mapper classes. Ignore the copy errors at the end, it works without the copying because the oracle assemblies are in the GAC. DbMetal is created in the bin directory.
Run the command to create the mapper classes.
dbmetal /provider:oracle /conn:”Data Source=XE;User Id=linq;Password=linq;” /database=linq /code:linq.cs
create a new working directory (c:\dotnet\oracle) and copy the linq.cs, DbLinq.dll and DbLinq.Oracle.dll to this directory.
Create the test program oracletest.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using DbLinq.Linq.Mapping;
using System.Data.OracleClient;
class OracleTest
{
static void Main(string[] args)
{
string connStr = “User Id=linq;Password=linq;Data Source=XE”;
Linq db = new Linq(new OracleConnection(connStr)); // Linq is the mapperclass created with DbMetal
Console.WriteLine(“Select and delete all temperatures:”);
var q1 = from p in db.Degrees select p;
//db.Degrees.RemoveAll(q1); // Not implemented 
Console.WriteLine(“Fired sql:”);
Console.WriteLine(db.GetQueryText(q1));
Console.WriteLine(“Result:”);
foreach (var v in q1)
{
Console.WriteLine(v.DEGKey + ” – ” + v.DEGDate + ” – ” + v.DEGCeLcIUs); // For some reason DbMetal makes a very cryptic property name
db.Degrees.DeleteOnSubmit(v);
}
db.SubmitChanges();
Console.WriteLine(“——”);
Console.WriteLine(“Select temperatures (none):”);
var q2 = from p in db.Degrees select p;
Console.WriteLine(“Fired sql:”);
Console.WriteLine(db.GetQueryText(q2));
Console.WriteLine(“Result:”);
Console.WriteLine(“DEGKEY – DEGDATE – DEGCELCIUS”);
foreach (var v in q2)
{
Console.WriteLine(v.DEGKey + ” – ” + v.DEGDate + ” – ” + v.DEGCeLcIUs); // For some reason DbMetal makes a very cryptic property name
}
Console.WriteLine(“——”);
Console.WriteLine(“Insert temperature 1:”);
Degrees a = new Degrees();
a.DEGKey = 1;
a.DEGDate = System.DateTime.Now;
a.DEGCeLcIUs = 1;
db.Degrees.InsertOnSubmit(a);
System.Threading.Thread.Sleep(5000);
Console.WriteLine(“Insert temperature 2:”);
Degrees b = new Degrees();
b.DEGKey = 2;
b.DEGDate = System.DateTime.Now;
b.DEGCeLcIUs = 2;
db.Degrees.InsertOnSubmit(b);
System.Threading.Thread.Sleep(5000);
Console.WriteLine(“Insert temperature 3:”);
Degrees c = new Degrees();
c.DEGKey = 3;
c.DEGDate = System.DateTime.Now;
c.DEGCeLcIUs = 3;
db.Degrees.InsertOnSubmit(c);
db.SubmitChanges();
Console.WriteLine(“——”);
Console.WriteLine(“Select all temperatures:”);
var q3 = from p in db.Degrees select p;
Console.WriteLine(“Fired sql:”);
Console.WriteLine(db.GetQueryText(q3));
Console.WriteLine(“Result:”);
Console.WriteLine(“DEGKEY – DEGDATE – DEGCELCIUS”);
foreach (var v in q3)
{
Console.WriteLine(v.DEGKey + ” – ” + v.DEGDate + ” – ” + v.DEGCeLcIUs); // For some reason DbMetal makes a very cryptic property name
}
Console.WriteLine(“——”);
Console.WriteLine(“Select all temperatures higher than 1 and add 5 to these:”);
var q4 = from p in db.Degrees where p.DEGCeLcIUs>1 select p;
Console.WriteLine(“Fired sql:”);
Console.WriteLine(db.GetQueryText(q4));
Console.WriteLine(“Result:”);
Console.WriteLine(“DEGKEY – DEGDATE – DEGCELCIUS”);
foreach (var v in q4)
{
Console.WriteLine(v.DEGKey + ” – ” + v.DEGDate + ” – ” + v.DEGCeLcIUs); // For some reason DbMetal makes a very cryptic property name
}
foreach (var v in q4)
{
v.DEGCeLcIUs+=5;
}
db.SubmitChanges();
Console.WriteLine(“——”);
Console.WriteLine(“Select all temperatures:”);
var q5 = from p in db.Degrees select p;
Console.WriteLine(“Fired sql:”);
Console.WriteLine(db.GetQueryText(q5));
Console.WriteLine(“Result:”);
Console.WriteLine(“DEGKEY – DEGDATE – DEGCELCIUS”);
foreach (var v in q5)
{
Console.WriteLine(v.DEGKey + ” – ” + v.DEGDate + ” – ” + v.DEGCeLcIUs); // For some reason DbMetal makes a very cryptic property name
}
Console.WriteLine(“——”);
}
}
Compile and run:
csc /r:dblinq.dll,dblinq.oracle.dll *.cs
oracletest:
Select and delete all temperatures: Fired sql: SELECT p$.DEGCELCIUS, p$.DEGDATE, p$.DEGKEY FROM LINQ.DEGREES p$ Result: 1 - 5/22/2008 7:07:48 PM - 1 2 - 5/22/2008 7:07:53 PM - 2 3 - 5/22/2008 7:07:58 PM - 3 ------ Select temperatures (none): Fired sql: SELECT p$.DEGCELCIUS, p$.DEGDATE, p$.DEGKEY FROM LINQ.DEGREES p$ Result: DEGKEY - DEGDATE - DEGCELCIUS ------ Insert temperature 1: Insert temperature 2: Insert temperature 3: ------ Select all temperatures: Fired sql: SELECT p$.DEGCELCIUS, p$.DEGDATE, p$.DEGKEY FROM LINQ.DEGREES p$ Result: DEGKEY - DEGDATE - DEGCELCIUS 1 - 5/22/2008 7:10:14 PM - 1 2 - 5/22/2008 7:10:19 PM - 2 3 - 5/22/2008 7:10:24 PM - 3 ------ Select all temperatures higher than 1 and add 5 to these: Fired sql: SELECT p$.DEGCELCIUS, p$.DEGDATE, p$.DEGKEY FROM LINQ.DEGREES p$ WHERE p$.DEGCELCIUS > 1 Result: DEGKEY - DEGDATE - DEGCELCIUS 2 - 5/22/2008 7:10:19 PM - 2 3 - 5/22/2008 7:10:24 PM - 3 ------ Select all temperatures: Fired sql: SELECT p$.DEGCELCIUS, p$.DEGDATE, p$.DEGKEY FROM LINQ.DEGREES p$ Result: DEGKEY - DEGDATE - DEGCELCIUS 1 - 5/22/2008 7:10:14 PM - 1 2 - 5/22/2008 7:10:19 PM - 7 3 - 5/22/2008 7:10:24 PM - 8 ------
Have fun…
Let’s use the previous JPA sample in an EJB. We start creating a datasource on the OC4J container. Then we create the entities again. After this we use the entities in a Stateless EJB which we will deploy to OC4J 11g. Finally we create a stand-alone client which calls the EJB.
We start with the creation of the datasource. We need a ConnectionPool.
java -jar $OC4J_ADMIN/j2ee/home/admin_client.jar deployer:oc4j:localhost oc4jadmin welcome -CreateJDBCConnectionPool -applicationName default -name HrConnectionPool -factoryClass oracle.jdbc.pool.OracleDataSource -user hr -password hr -url jdbc:oracle:thin:@localhost:1521:orcl
Test the connectionpool:
java -jar $OC4J_ADMIN/j2ee/home/admin_client.jar deployer:oc4j:localhost oc4jadmin welcome
-testConnectionPool -connectionPoolName HrConnectionPool -sqlStatement “select
* from dual” And the datasource:
java -jar $OC4J_ADMIN/j2ee/home/admin_client.jar deployer:oc4j:localhost oc4jadmin welcome -createManagedDataSource -applicationName default -dataSourceName HrDataSource -jndiLocation jdbc/HrDataSource -connectionPoolName HrConnectionPool
After this we create the entities:
/home/broersa/work/CountryApp/CountryJPAEJB/src/com/bekijkhet/entity/Country.java:
package com.bekijkhet.entity;
import java.io.Serializable;
import javax.persistence.*;
@Entity
@Table(name="COUNTRIES")
public class Country implements Serializable {
private String id;
private String name;
private Region region;
@Id
@Column(name="COUNTRY_ID")
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
@Column(name="COUNTRY_NAME")
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@ManyToOne(cascade={CascadeType.ALL})
@JoinColumn(name="REGION_ID",nullable=false)
public Region getRegion() {
return region;
}
public void setRegion(Region region) {
this.region = region;
}
}
/home/broersa/work/CountryApp/CountryJPAEJB/src/com/bekijkhet/entity/Region.java
package com.bekijkhet.entity;
import java.io.Serializable;
import javax.persistence.*;
import static javax.persistence.CascadeType.*;
import static javax.persistence.FetchType.*;
import java.util.List;
import java.util.ArrayList;
@Entity
@Table(name="REGIONS")
public class Region implements Serializable {
private int id;
private String name;
private List<Country> countries = new ArrayList<Country>();;
@Id
@Column(name="REGION_ID")
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
@Column(name="REGION_NAME")
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@OneToMany(fetch=FetchType.EAGER, cascade={CascadeType.ALL}, mappedBy="region")
public List<Country> getCountries() {
return countries;
}
public void setCountries(List<Country> newValue) {
this.countries = newValue;
}
}
The EJB:/home/broersa/work/CountryApp/CountryJPAEJB/src/com/bekijkhet/country/CountryEJBLocal.java:
package com.bekijkhet.country;
import com.bekijkhet.entity.*;
import java.util.List;
public interface CountryEJBLocal {
public List<Country> getCountriesByRegion(String region);
public Region getRegionByCountry(String country);
}
/home/broersa/work/CountryApp/CountryJPAEJB/src/com/bekijkhet/country/CountryEJB.java:
package com.bekijkhet.country;
import java.util.List;
import com.bekijkhet.entity.*;
public interface CountryEJB {
public List<Country> getCountriesByRegion(String region);
public Region getRegionByCountry(String country);
public void addorchangeCountry(Country country) ;
public void addorchangeRegion(Region region) ;
public void removeCountry(Country country) ;
public void removeRegion(Region region) ;
public List<Region> getAllRegions() ;
public List<Country> getAllCountries();
}
/home/broersa/work/CountryApp/CountryJPAEJB/src/com/bekijkhet/country/CountryEJBBean.java
package com.bekijkhet.country;
import javax.ejb.Stateless;
import javax.ejb.Remote;
import javax.ejb.Local;
import java.util.List;
import com.bekijkhet.entity.*;
import javax.persistence.*;
@Stateless
@Remote(CountryEJB.class)
@Local(CountryEJBLocal.class)
public class CountryEJBBean implements CountryEJB,CountryEJBLocal {
@PersistenceContext
EntityManager em;
public List<Country> getCountriesByRegion(String region) {
Query q = em.createQuery("select r from Region r where r.name = :name");
q.setParameter("name",region);
Region r = (Region) q.getSingleResult();
// r.getCountries().size(); // get the LAZY relation
return r.getCountries();
}
public Region getRegionByCountry(String country) {
Query q = em.createQuery("select c from Country c where c.name = :name");
q.setParameter("name",country);
Country c = (Country) q.getSingleResult();
return c.getRegion();
}
public void addorchangeCountry(Country country) {
em.persist(country);
}
public void addorchangeRegion(Region region) {
em.persist(region);
}
public void removeCountry(Country country) {
Country c = em.merge(country);
em.remove(c);
}
public void removeRegion(Region region) {
Region r = em.merge(region);
em.remove(r);
}
public List<Region> getAllRegions() {
Query q = em.createQuery("select r from Region r");
List<Region> l = q.getResultList();
return l;
}
public List<Country> getAllCountries() {
Query q = em.createQuery("select c from Country c");
List<Country> l = q.getResultList();
return l;
}
}
We need a persistence descriptor : /home/broersa/work/CountryApp/CountryJPAEJB/src/META-INF/persistence.xml:
<persistence>
<persistence-unit name="MyPU">
<jta-data-source>jdbc/HrDataSource</jta-data-source>
</persistence-unit>
</persistence>
We need the build file:/home/broersa/work/CountryApp/CountryJPAEJB/build.xml:
<project name="CountryJPAEJB" default="dist" basedir=".">
<description>
simple example build file
</description>
<!-- set global properties for this build -->
<property name="src" location="src"/>
<property name="build" location="build"/>
<property name="dist" location="dist"/>
<path id="files-classpath">
<!--fileset dir="$HOME/oc4j_client_11110_preview/j2ee/home/lib" >
<include name="persistence.jar"/>
</fileset-->
<pathelement location="/home/broersa/oc4j_client_11110_preview/j2ee/home/lib/persistence.jar"/>
<pathelement location="/home/broersa/oc4j_client_11110_preview/j2ee/home/lib/ejb.jar"/>
</path>
<target name="init">
<!-- Create the time stamp -->
<tstamp/>
<!-- Create the build directory structure used by compile -->
<mkdir dir="${build}"/>
</target>
<target name="compile" depends="init"
description="compile the source " >
<!-- Compile the java code from ${src} into ${build} -->
<javac srcdir="${src}" destdir="${build}">
<classpath refid="files-classpath" />
</javac>
</target>
<target name="dist" depends="compile"
description="generate the distribution" >
<!-- Create the distribution directory -->
<mkdir dir="${dist}"/>
<copy todir="${build}/META-INF">
<fileset dir="src/META-INF">
<include name="*" />
</fileset>
</copy>
<!-- Put everything in ${build} into the MyProject-${DSTAMP}.jar file -->
<jar jarfile="${dist}/CountryBean.jar" basedir="${build}"/>
</target>
<target name="clean"
description="clean up" >
<!-- Delete the ${build} and ${dist} directory trees -->
<delete dir="${build}"/>
<delete dir="${dist}"/>
</target>
</project>
We can build and deploy the EJB:type “ant” to build the EJB.use the next command to deploy the ejb:
java -jar $OC4J_ADMIN/j2ee/home/admin_client.jar deployer:oc4j:localhost oc4jadmin welcome -deploy -file $HOME/work/CountryApp/CountryJPAEJB/dist/CountryBean.jar -deploymentName CountryBean
Now this is done we can create the client:
/home/broersa/work/CountryApp/CountryClient/com/bekijkhet/CountryClient.java:
package com.bekijkhet;
import javax.naming.*;
import com.bekijkhet.country.CountryEJB;
import com.bekijkhet.entity.*;
import java.util.Properties;
import java.util.List;
public class CountryClient {
public static void main(String[] args) {
try {
Properties props = new Properties();
props.put("java.naming.factory.initial","com.evermind.server.ApplicationClientInitialContextFactory");
props.put("java.naming.provider.url","ormi://localhost/CountryBean");
props.put("java.naming.security.principal","oc4jadmin");
props.put("java.naming.security.credentials","welcome");
InitialContext ctx = new InitialContext(props);
CountryEJB h = (CountryEJB)ctx.lookup("CountryEJBBean");
System.out.println("The Netherlands has region: " + h.getRegionByCountry("Netherlands").getName());
List<Country> l1 = h.getCountriesByRegion("Europe");
System.out.println("Europe has the following countries:");
for (Country c : l1) {
System.out.println(" " + c.getName());
}
System.out.println("All regions:");
List<Region> l2 = h.getAllRegions();
for (Region r : l2) {
System.out.println(" " + r.getName());
}
System.out.println("All countries:");
List<Country> l3 = h.getAllCountries();
for (Country c2 : l3) {
System.out.println(" " + c2.getName() + " - " + c2.getRegion().getName());
}
Region r3 = new Region();
r3.setName("MyRegion1");
r3.setId(1001);
Country c3 = new Country();
c3.setName("MyCountry1");
c3.setId("31");
c3.setRegion(r3);
r3.getCountries().add(c3);
h.addorchangeCountry(c3);
Region r4 = new Region();
r4.setName("MyRegion2");
r4.setId(1002);
Country c4 = new Country();
c4.setName("MyCountry2");
c4.setId("32");
c4.setRegion(r4);
r4.getCountries().add(c4);
h.addorchangeRegion(r4);
System.out.println("All regions:");
List<Region> l5 = h.getAllRegions();
for (Region r5 : l5) {
System.out.println(" " + r5.getName());
}
System.out.println("All countries:");
List<Country> l6 = h.getAllCountries();
for (Country c6 : l6) {
System.out.println(" " + c6.getName() + " - " + c6.getRegion().getName());
}
}
catch (Exception e) {
System.out.println(e);
e.printStackTrace();
}
}
}
/home/broersa/work/CountryApp/CountryClient/com/bekijkhet/META-INF/application-client.xml:
<?xml version="1.0" encoding="UTF-8"?>
<application-client version="1.4" xmlns="http://java.sun.com/xml/ns/j2ee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/application-client_1_4.xsd">
<display-name>CountryClient</display-name>
<description>test</description>
</application-client>
Compile the client:javac -cp $OC4J_HOME/j2ee/home/oc4jclient.jar:/home/broersa/work/CountryApp/CountryJPAEJB/dist/CountryBean.jar:. -d . CountryClient.javaAnd test the client:java -cp $OC4J_HOME/j2ee/home/oc4jclient.jar:/home/broersa/work/CountryApp/CountryJPAEJB/dist/CountryBean.jar:. com.bekijkhet.CountryClient
The Netherlands has region: Europe
Europe has the following countries:
Belgium
Switzerland
Germany
Denmark
France
Italy
Netherlands
United Kingdom
All regions:
Europe
Americas
Asia
Middle East and Africa
All countries:
Argentina – Americas
Australia – Asia
Belgium – Europe
Brazil – Americas
Canada – Americas
Switzerland – Europe
China – Asia
Germany – Europe
Denmark – Europe
Egypt – Middle East and Africa
France – Europe
HongKong – Asia
Israel – Middle East and Africa
India – Asia
Italy – Europe
Japan – Asia
Kuwait – Middle East and Africa
Mexico – Americas
Nigeria – Middle East and Africa
Netherlands – Europe
Singapore – Asia
United Kingdom – Europe
United States of America – Americas
Zambia – Middle East and Africa
Zimbabwe – Middle East and Africa
All regions:
Europe
Americas
Asia
Middle East and Africa
MyRegion1
MyRegion2
All countries:
MyCountry1 – MyRegion1
MyCountry2 – MyRegion2
Argentina – Americas
Australia – Asia
Belgium – Europe
Brazil – Americas
Canada – Americas
Switzerland – Europe
China – Asia
Germany – Europe
Denmark – Europe
Egypt – Middle East and Africa
France – Europe
HongKong – Asia
Israel – Middle East and Africa
India – Asia
Italy – Europe
Japan – Asia
Kuwait – Middle East and Africa
Mexico – Americas
Nigeria – Middle East and Africa
Netherlands – Europe
Singapore – Asia
United Kingdom – Europe
United States of America – Americas
Zambia – Middle East and Africa
Zimbabwe – Middle East and Africa
In this sample we will create a stand alone java app which uses the JPA (Toplink) api to connect to the HR sample schema in a Oracle database.
The hr schema must be unlocked and database must be accessible via the network.
We will start with the persistence.xml file.
/home/broersa/work/CountryApp/CountryJPA/build/META-INF/persistence.xml
<persistence xmlns="http://java.sun.com/xml/ns/persistence" version="1.0"> <persistence-unit name="default"> <provider> oracle.toplink.essentials.PersistenceProvider </provider> <exclude-unlisted-classes>false</exclude-unlisted-classes> <properties> <property name="toplink.logging.level" value="INFO"/> <property name="toplink.jdbc.driver" value="oracle.jdbc.OracleDriver"/> <property name="toplink.jdbc.url" value="jdbc:oracle:thin:centos.bekijkhet.com:l521:orcl"/> <property name="toplink.jdbc.password" value="hr"/> <property name="toplink.jdbc.user" value="hr"/> </properties> </persistence-unit> </persistence>
After this the entities which represent the database objects. I choose the regions and countries tables.
/home/broersa/work/CountryApp/CountryJPA/src/com/bekijkhet/entity/Region.java
package com.bekijkhet.entity;
import java.io.Serializable;
import javax.persistence.*;
import static javax.persistence.CascadeType.*;
import java.util.List;
import java.util.ArrayList;
@Entity
@Table(name="REGIONS")
public class Region implements Serializable {
private int id;
private String name;
private List<Country> countries = new ArrayList<Country>();;
@Id
@Column(name="REGION_ID")
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
@Column(name="REGION_NAME")
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@OneToMany(cascade={CascadeType.ALL}, mappedBy="region")
public List<Country> getCountries() {
return countries;
}
public void setCountries(List<Country> newValue) {
this.countries = newValue;
}
}
/home/broersa/work/CountryApp/CountryJPA/src/com/bekijkhet/entity/Country.java
package com.bekijkhet.entity;
import java.io.Serializable;
import javax.persistence.*;
@Entity
@Table(name="COUNTRIES")
public class Country implements Serializable {
private String id;
private String name;
private Region region;
@Id
@Column(name="COUNTRY_ID")
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
@Column(name="COUNTRY_NAME")
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@ManyToOne()
@JoinColumn(name="REGION_ID",nullable=false)
public Region getRegion() {
return region;
}
public void setRegion(Region region) {
this.region = region;
}
}
Compile the entities:
cd /home/broersa/work/CountryApp/CountryJPA
javac -cp /home/broersa/oc4j_client_11110_preview/j2ee/home/lib/persistence.jar:build -d build src/com/bekijkhet/entity/*.java
After this the client app:
/home/broersa/work/CountryApp/CountryJPA/src/com/bekijkhet/client/Client.java:
package com.bekijkhet.client;
import javax.persistence.EntityManager;
import javax.persistence.Persistence;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Query;
import java.util.List;
import com.bekijkhet.entity.*;
public class Client {
private static EntityManagerFactory emf;
private static EntityManager em;
public static void main(String[] args) {
emf = Persistence.createEntityManagerFactory("default");
em = emf.createEntityManager();
// Find a country with region
Query q1 = em.createQuery("select c from Country c where c.id = :id");
q1.setParameter("id","NL");
Country c1 = (Country) q1.getSingleResult();
System.out.println(c1.getName() + " - " + c1.getRegion().getName());
// Find a region with countries
Query q2 = em.createQuery("select r from Region r where r.id = :id");
q2.setParameter("id",1);
Region r2 = (Region) q2.getSingleResult();
System.out.println(r2.getName());
List<Country> l2 = r2.getCountries();
for (Country c2 : l2) {
System.out.println(" " + c2.getName());
}
// Add a region with two countries
em.getTransaction().begin();
Region r3 = new Region();
r3.setId(1001);
r3.setName("MyRegion1");
Country c31 = new Country();
c31.setId("31");
c31.setName("MyCountry31");
c31.setRegion(r3);
r3.getCountries().add(c31);
Country c32 = new Country();
c32.setId("32");
c32.setName("MyCountry32");
c32.setRegion(r3);
r3.getCountries().add(c32);
em.persist(r3);
em.getTransaction().commit();
// List the added region with countries
Query q4 = em.createQuery("select r from Region r where r.id = :id");
q4.setParameter("id",1001);
Region r4 = (Region) q4.getSingleResult();
System.out.println(r4.getName());
List<Country> l4 = r4.getCountries();
for (Country c4 : l4) {
System.out.println(" " + c4.getName());
}
// Change the country names
em.getTransaction().begin();
Query q5 = em.createQuery("select r from Region r where r.id = :id");
q5.setParameter("id",1001);
Region r5 = (Region) q5.getSingleResult();
List<Country> l5 = r5.getCountries();
for (Country c5 : l5) {
c5.setName(c5.getName()+"-changed");
}
em.getTransaction().commit();
// List the added region with countries
Query q6 = em.createQuery("select r from Region r where r.id = :id");
q6.setParameter("id",1001);
Region r6 = (Region) q6.getSingleResult();
System.out.println(r6.getName());
List<Country> l6 = r6.getCountries();
for (Country c6 : l6) {
System.out.println(" " + c6.getName());
}
// Remove samples
// Remove a single detail
em.getTransaction().begin();
Query q7 = em.createQuery("select r from Region r where r.id = :id");
q7.setParameter("id",1001);
Region r7 = (Region) q7.getSingleResult();
System.out.println(r7.getName());
List<Country> l7 = r7.getCountries();
em.remove(l7.get(1));
em.getTransaction().commit();
// Remove samples
// Remove the whole region
em.getTransaction().begin();
Query q8 = em.createQuery("select r from Region r where r.id = :id");
q8.setParameter("id",1001);
Region r8 = (Region) q8.getSingleResult();
System.out.println(r8.getName());
em.remove(r8);
em.getTransaction().commit();
em.close();
}
}
compile and run:
cd /home/broersa/work/CountryApp/CountryJPA
javac -cp /home/broersa/oc4j_client_11110_preview/j2ee/home/lib/persistence.jar:build -d build src/com/bekijkhet/client/*.java
java -cp /home/broersa/oc4j_client_11110_preview/j2ee/home/lib/persistence.jar:/home/broersa/toplink_11.1.1.0_071214_preview-3/lib/java/api/toplink.jar:/home/broersa/myclasses/ojdbc6.jar:build -javaagent:/home/broersa/toplink_11.1.1.0_071214_preview-3/lib/java/internal/toplink-essentials-agent.jar com.bekijkhet.client.Client
[TopLink Info]: 2008.01.28 08:09:38.718–ServerSession(9519074)–TopLink, version: Oracle TopLink Essentials – 2.0 (Build SNAPSHOT (06/04/2007))
[TopLink Info]: 2008.01.28 08:09:41.744–ServerSession(9519074)–file:/home/broersa/work/CountryApp/CountryJPA/build/-default login successful
Netherlands – Europe
Europe
Belgium
Switzerland
Germany
Denmark
France
Italy
Netherlands
United Kingdom
MyRegion1
MyCountry31
MyCountry32
MyRegion1
MyCountry31-changed
MyCountry32-changed
MyRegion1
MyRegion1
After an installation of a new Oracle Home and complete restore of the database it is nescesary to regenerate the Database Console repository to use the Enterprise Manager.
[oracle@centos ~]$ . oraenv
ORACLE_SID = [orcl] ? orcl
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 is /u01/app/oracle
create a password file:
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=<password>
and check the parameter remote_login_password:
[oracle@centos db_1]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 – Production on Thu Jan 10 19:15:47 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter password
NAME TYPE VALUE
———————————— ———– ——————————
remote_login_passwordfile string EXCLUSIVE
SQL>
if this is not exclusive, change it and restart the database. After this you can connect as sysdba over tns.
It is nescesary to recreate the repository because with the reintall of oracle we lost the repository key. which is located in the oracle_home. First thing to do is drop the repository:
[oracle@centos db_1]$ emca -deconfig dbcontrol db -repos drop
STARTED EMCA at Jan 10, 2008 8:12:36 PM
EM Configuration Assistant, Version 11.1.0.5.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: orcl
Listener port number: 1521
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: y
Jan 10, 2008 8:12:46 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/orcl/emca_2008_01_10_20_12_36.log.
Jan 10, 2008 8:12:48 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) …
Jan 10, 2008 8:13:01 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) …
Jan 10, 2008 8:20:33 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jan 10, 2008 8:21:04 PM
[oracle@centos db_1]$
Now we can recreate the repository.
[oracle@centos db_1]$ emca -config dbcontrol db -repos create
STARTED EMCA at Jan 10, 2008 8:24:25 PM
EM Configuration Assistant, Version 11.1.0.5.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: orcl
Listener port number: 1521
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional): <my mail>
Outgoing Mail (SMTP) server for notifications (optional): centos.bekijkhet.com
—————————————————————–
You have specified the following settings
Database ORACLE_HOME ……………. /u01/app/oracle/product/11.1.0/db_1
Local hostname ……………. centos.bekijkhet.com
Listener port number ……………. 1521
Database SID ……………. orcl
Email address for notifications …………… <my mail>
Outgoing Mail (SMTP) server for notifications …………… centos.bekijkhet.com
—————————————————————–
Do you wish to continue? [yes(Y)/no(N)]: y
Jan 10, 2008 8:24:50 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/orcl/emca_2008_01_10_20_24_25.log.
Jan 10, 2008 8:24:54 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) …
Jan 10, 2008 8:38:28 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Jan 10, 2008 8:38:57 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) …
Jan 10, 2008 8:42:16 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Jan 10, 2008 8:42:25 PM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
Jan 10, 2008 8:42:25 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives …
Jan 10, 2008 8:42:49 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
Jan 10, 2008 8:42:49 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) …
Jan 10, 2008 8:43:17 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Jan 10, 2008 8:43:17 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) …
Jan 10, 2008 8:45:47 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Jan 10, 2008 8:45:47 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://centos.bekijkhet.com:1158/em <<<<<<<<<<<
Jan 10, 2008 8:46:42 PM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************ WARNING ************************
Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: /u01/app/oracle/product/11.1.0/db_1/centos.bekijkhet.com_orcl/sysman/config/emkey.ora. Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.
***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jan 10, 2008 8:46:42 PM
[oracle@centos db_1]$
Be sure to backup your system including the emkey.ora file!
In my home situation I have a server (sandbox) which changes frequently. I had an oracle 10g database on it which was backup’d every day to the flash_recovery_area with the default settings from enterprise manager. Nothing special in it, so when I reinstalled the server the only thing I did was save a tar from the flash_recovery_area. Must be enough.
After reinstalling the server I used the following to restore the database.
First I installed a fresh 11g oracle home.
The commands I isued are the following:
export ORACLE_SID=orcl
rman target /
startup nomount
restore spfile from autobackup db_recovery_file_dest=’<restored flash recovery dir>’ db_name=’orcl’;
restore controlfile from autobackup db_recovery_file_dest=’<restored flash recovery dir>’ db_name=’orcl’;
copy the controlfile from the dbs directory to the destinations in the spfile.
create the $ORACLE_BASE/admin/orcl/adump directory.
create the $ORACLE_BASE/oradata/orcl directory.
copy the flash_recovery_area to the original location.
in rman:
startup force mount;
restore database;
recover database;
alter database open resetlogs;
now the database is up and running. Now it is time to configure and start the listener with netca.
After this we have to regenerate the enterprise manager repository. This will be covered in the next blog.
