Na een vraag op het oracle forum over het converteren van valuta, heb ik besloten om hier een plug-in voor te schrijven.
De plug-in is niet echt heel spannend maar doet wel wat die moet doen.
Op basis van de koers die opgehaald wordt bij rate-exchange.appspot.com wordt de berekening uitgevoerd. Dit is een gratis webservice die de koers als JSON formaat terug geeft. Hier zat meteen de grootste uitdaging. Door de “same-origin policy” standaard mag je in javascript geen sites buiten het aanroepend domein benaderen. Wat je doet is een “Cross-origin resource sharing” aanroep en dat mag dus niet.
De aanroep resulteert in een “No ‘Access-Control-Allow-Origin’ header is present on the requested resource.” error.
Een uitzondering hierop wordt gemaakt indien de URL via JSONP kan worden aan geroepen. Door het toevoegen van “&callback=?” aan de URL werkt de site van rate-exange op die manier. Nu krijg je wel de JSON data binnen.
De code hiervoor ziet er dan alsvolgt uit
var url = “http://rate-exchange.appspot.com/currency?from=”+$v(action.attribute01)+”&to=”+$v(action.attribute02)+”&callback=?”;
jQuery.ajax({ type: “GET”,
url: url ,
dataType: “json”,
success: function(data) {
$s(action.attribute04,($v(action.attribute03)*data.rate).toFixed(2));
}
});
Waarbij action.attributeXX de parameters van de plug-in zijn.
Voor de selectlists van de valuta keuzes wilde ik ook een webservice gebruiken. Dit hoeft niet via javascript maar kan “gewoon” door een select die de (XML) data van de webservice kan vertalen. Er is een webservice met deze informatie beschikbaar bij de ISO organisatie via deze URL.
Dit werkt op apex.oracle.com echter niet, omdat je er een ACL voor moet aanmaken die toestemming geeft om de externe site te benaderen. In de demo gebruik ik dan ook STATIC selectlists.
Kijk op apex-plugin.com om de plugin te downloaden en hier voor een demo ervan.
Notification plug-in
Today I published a new version (1.1) of my notification plug-in.
In this version I added the possibility using free form text or predefined select lists for a few parameters.
Visit apex-plugin.com to get it.
Het gemak van xmltypeHeel af en toe krijgt een 3rd party applicatie waar wij hier beheer voor doen een fout item via een xml message binnen.
Hiervoor biedt de applicatie een beheerscherm waar de xml aangepast kan worden en daarna opnieuw aangeboden kan worden.
Dat voldeed prima tot we er op een dag ineens 90 foute berichten kregen.
Een blik op de onderliggende Oracle database liet zien dat de berichten met foutmelding in een tabel opgeslagen worden.
Zowel de errorstack als de message staan in een clob veld.
Wat we wilden automatiseren was een node uit de xml knippen waar het betreffende item uit de foutmelding in voor kwam.
Het wordt dus een update statement op de betreffende message tabel waarbij we de payload aanpassen:
update message_error_log lg set lg.payload = ? |
Eerst identificeren we de juiste berichten in de where clause. We weten het messagetype en de foutmelding:
where lg.message_type = 1234 and lg.exception_type = ‘com.exception.ItemNotFoundException’ |
Het vinden van het itemnummer in de foutmelding kan met ouderwets knippen en plakken:
to_char (substr (lg.stacktrace , instr (lg.stacktrace, ‘item does not exist:’) + 19 , ( instr (lg.stacktrace , ‘at com.backend.process’ ) – 2 ) – (instr (lg.stacktrace, ‘item does not exist:’) + 19) ) ) |
Eventueel zou bovenstaande ook met een reguliere expressie kunnen.
Om zeker te zijn dat het gevallen van dit specifieke probleem zijn controleren we of het itemnummer in de xml op de juiste plaats voorkomt:
and extractvalue (xmltype (lg.payload) , ‘/Message/Payload/Forecast/ItemDemand/itemNo[.=”‘ || to_char (substr (lg.stacktrace , instr (lg.stacktrace, ‘item does not exist:’) + 19 , ( instr (lg.stacktrace , ‘at com.backend.process’ ) – 2 ) – (instr (lg.stacktrace, ‘item does not exist:’) + 19) ) ) || ‘”]’ ) is not null |
Eerst wordt hier de clob in een xmltype gezet: xmltype (lg.payload)
Vervolgens wordt een xpath expressie gemaakt met het item uit de foutmelding:
‘/Message/Payload/Forecast/ItemDemand/itemNo[.=”<itemnummer>”]’
Hiermee wordt gecontroleerd of het betreffende nummer op de juiste plaats in de xml voorkomt.
Deze waarde wordt dan met extractvalue eruit geplukt.
Nu hebben we de juiste rijen te pakken en komt het knippen in de xml:
update message_error_log lg set lg.payload = deletexml (xmltype (lg.payload) , ‘/Message/Payload/Forecast/ItemDemand[itemNo=”‘ || to_char (substr (lg.stacktrace , instr (lg.stacktrace, ‘item does not exist:’) + 19 , ( instr (lg.stacktrace , ‘at com.backend.process’ ) – 2 ) – (instr (lg.stacktrace, ‘item does not exist:’) + 19) ) ) || ‘”]’ ).getclobval () |
Eerst weer converteren naar xmltype: xmltype (lg.payload)
Dan met een iets andere xpath expressie op zoek naar de node waarin het item voorkomt (let op de plaats van de blokhaken!):
‘/Message/Payload/Forecast/ItemDemand[itemNo=”<itemnummer>”]’
Met deletexml wordt de betreffende node eruit geknipt.
Wat rest is de xmltype weer terug in een clob te veranderen, dit kan met de methode getclobval van het xmltype: .getclobval ()
Het eindresultaat:
update message_error_log lg set lg.payload = deletexml (xmltype (lg.payload) , ‘/Message/Payload/Forecast/ItemDemand[itemNo=”‘ || to_char (substr (lg.stacktrace , instr (lg.stacktrace, ‘item does not exist:’) + 19 , ( instr (lg.stacktrace , ‘at com.backend.process’ ) – 2 ) – (instr (lg.stacktrace, ‘item does not exist:’) + 19) ) ) || ‘”]’ ).getclobval () where lg.message_type = 1234 and lg.exception_type = ‘com.exception.ItemNotFoundException’ and extractvalue (xmltype (lg.payload) , ‘/Message/Payload/Forecast/ItemDemand/itemNo[.=”‘ || to_char (substr (lg.stacktrace , instr (lg.stacktrace, ‘item does not exist:’) + 19 , ( instr (lg.stacktrace , ‘at com.backend.process’ ) – 2 ) – (instr (lg.stacktrace, ‘item does not exist:’) + 19) ) ) || ‘”]’ ) is not null |
In de applicatie konden we nu alle berichten opnieuw aanbieden en ze waren verwerkt.
Deze generieke oplossing kan vervolgens voor alle toekomstige gevallen gebruikt worden.
Dit bespaart tijd en is minder foutgevoelig dan het handmatige aanpassen.
Zoals zo vaak bij het schrijven van een blog item wordt deze meestal getriggerd door een probleem waar ik tijdens het werk tegen aanloop. Ook dit item is er weer een uit die categorie.
De foutmelding die ik doorkreeg was net zo simpel als kort “De zoek opdracht op pagina xx performed niet goed”.
Ik hou wel van dit soort meldingen omdat het altijd weer een leuke uitdaging is om een performance probleem op te lossen en het resultaat direct bemerkt wordt door de klant.
select ..
, ..
, its.its_oni_package.oni_radio_group_score(p_score_type =>ind.ind_score_type, .. )
from its_onderzoek_indicatoren oni
where ..
De query is voor de leesbaarheid versimpeld. De query heb ik naar sqldeveloper gekopieerd om wat makkelijker te kunnen testen. Als eerst maar eens die functie uit query gehaald en jawel hoor de query performde meteen prima. Weer een stapje dichter bij het probleem. Dan maar eens die functie uitpluizen. In de functie zit een cursor die gebruik maakt van de nv()functie.
select .. from its_onderzoek_indicatoren oni . . join ios_elementen elm on oni.oni_one_elm_id = elm.elm_id where oni.oni_one_ond_nr = nv('p10105_ond_nr') and elm.elm_id = nv('p10105_element')
Voor het gemak heb ik deze functies vervangen door bind variabelen in mijn sqldeveloper query en ik kon geen performance probleem vaststellen. huuh, hoe kan dat nu? Het blijkt dat ik het probleem al opgelost had voordat ik exact wist wat het was.
select .. from its_onderzoek_indicatoren oni . . join ios_elementen elm on oni.oni_one_elm_id = elm.elm_id where oni.oni_one_ond_nr = :p10105_ond_nr and elm.elm_id = :p10105_element
het is duidelijk dat het gebruik van nv() functie het performance probleem creëert.
select .. from its_onderzoek_indicatoren oni . . join ios_elementen elm on oni.oni_one_elm_id = elm.elm_id where oni.oni_one_ond_nr = (select nv('p10105_ond_nr') from dual) and elm.elm_id = (select nv('p10105_element') from dual)
Een eenvoudige methode om de performance van batches in Oracle systemen te verbeteren is het slim omgaan met de timing en volgorde van uitvoering. De peformance van jobs is vaak erg afhankelijk van de hoeveelheid fysieke schijfacties die uitgevoerd moeten worden wat weer volgt uit de inhoud van de buffer cache.
Vooral bij gepartitioneerde tabellen met lokale indexen is het handig om te zorgen dat partities op volgorde worden behandeld zodat de index blokken in cache maximaal hergebruikt worden.
Als er bijvoorbeeld een batch moet draaien voor item 100 t/m 120 voor elke dag van de afgelopen week op data die voornamelijk op datum is gepartitioneerd dan geeft is het het beste om op volgorde van dagen te schedulen.
Bij hash partities wordt het lastiger omdat daarbij in principe niet te achterhalen is van welke partities de job gebruik gaat maken.
Ik zeg in principe omdat de ongedocumenteerde functie tbl$or$idx$part$num daarbij kan helpen. Als je deze functie aanroept met de tabelnaam en de partition key dan levert dit de hashpartitie op waar de key in terecht komt.
Stel bijvoorbeeld dat de meeste data gepartitioneerd is op hash(item) dan kun je de batchaanroepen sorteren op tbl$or$idx$part$num(<tabelnaam>,0,0,0,item).
In specifieke situaties heb ik winsten in doorlooptijden gezien van tientallen procenten met alleen veranderen van de volgorde van aanroepen. Let wel dat ook bij dit soort aanpassingen een test vaak op zijn plaats is. Met name bij parallele uitvoering kunnen vanwege de toegenomen activiteit in dezelfde blokken locking of buffer cache waits optreden.
Signature mismatch bij het zetten van een custom SQL profile
Voor het hard vastzetten van een executieplan zijn er in Oracle verschillende methoden beschikbaar.
De meest toekomstvaste en stabiele voor 11g is nu het custom SQL profile. Dit is een variant van het door de tuning advisor aangemaakte SQL profile. Waar de tuning advisor een set hints met relatieve aanpassingen op statistieken oplevert geeft het custom profile een set directieven die een specifiek plan afdwingen. Dit is vergelijkbaar met een outline.
Oracle support levert hiervoor het script coe_xfr_sql_profile.sql (DOC ID 1487302.1), welke opgenomen is in de SQLT tool (DOC ID 215187.1).
Laatst liep ik hiermee tegen een probleem aan. Het script maakte een profile aan met de juiste hints, maar het profile werd niet gebruikt voor het betreffende statement.
Na onderzoek bleek dat de signature van het profile in DBA_SQL_PROFILES afweek van de signature zoals deze in V$SQL stond.
Hoe kon dit gebeuren? De profile was immers gemaakt vanuit hetzelfde SQL ID.
Het probleem zat hem in een truc die in het coe script gebruikt wordt om lange regels op te knippen:
:sql_text := REPLACE(:sql_text, ‘)’, ‘)’||CHR(10)); :sql_text := REPLACE(:sql_text, ‘,’, ‘,’||CHR(10)); |
In het betreffende statement stond een conditie in ‘–‘ stijl commentaar:
— AND to_char(a.kolom,’YYYY-MM-DD’) = b.kolom |
Het coe script maakt hier dit van:
— AND to_char(a.kolom, ‘YYYY-MM-DD’) = b.kolom |
Hierin worden de 2e en 3e regel niet meer als commentaar gezien en verandert dus de signature van het statement.
Om dit te omzeilen heb ik het door het coe script gegenereerde script handmatig aangepast en de signature was weer gelijk.
Het profile werd opgepakt en het plan veranderde in het gewenste.
Een signature mismatch valt op doordat de aangemaakte profile niet in het ‘plan control’ tabblad verschijn in de enterprise manager en niet te zien is in de notes bij het opvragen van een dbms_xplan.display_cursor.
Bij mijn huidige opdrachtgever hebben we een prachtig systeem vanaf scratch mogen opbouwen. Inmiddels zijn we in een stadium aanbeland dat we niet meer in een projectgroep werken maar volledig in productie. Dit verandert ook de werkwijze op veel vlakken. Één daarvan is de manier hoe we moeten omgaan met bug fixing terwijl er op hetzelfde moment ook gewerkt wordt aan een nieuwe release. Gelukkig heeft APEX hiervoor de Build Option.
Met deze optie kan je diversen applicatie onderdelen in- of excluden. Hoe je de Build Option nu moet gebruiken, zal ik hieronder stap voor stap uitleggen.
Dit doe je bij de Shared Components
Hier kan je een nieuwe Build Option aanmaken.
Je kunt de Build Option op verschillende plaatsen gebruiken.
Kortom op alles wat je op een pagina plaatst en de pagina zelf kan de de Build Option gebruiken.
Je geeft bij het betreffende object aan wanneer die moet worden gebruikt, wanneer de Build Option aan of uit staat.
Op de bovenstaande manier kun je dus nieuwe objecten aan een pagina toevoegen en daarbij aangeven dat die bij een bepaalde Build Option horen.
Dit is natuurlijk allemaal heel handig maar hoe kun je nu aangeven welke release wanneer actief is?
Ook daar is over nagedacht. Bij de Build Option in de shared components kun je de status en default on export opgeven.
Met de status geef je aan of deze Build Option in de applicatie moet worden gebruikt of niet (include/exclude).
met Default on Export geef je aan of de Build Option mee moet worden genomen tijdens een export of deploy (via sqldeveloper).
Hierdoor is het dus mogelijk om aan een release te werken en toch een pagina op te leveren om een bug op te lossen.
Wat nu als je in bijvoorbeeld in een LOV bepaalde regels wel of niet wil tonen, afhankelijk van de Build Option. Je zou dan kunnen kiezen om 2 verschillende item’s te maken met ieder zijn eigen LOV en het juiste item tonen afhankelijk van de Build Option. Dit is echter niet altijd even handig omdat je de 2 item niet dezelfde naam kan geven. dit kan weer problemen geven met de verdere afhandeling op de pagina.
Ik heb dit opgelost door de Build Option aan te roepen via een apex api. In deze api is de functie apex_util.get_build_option_status aanwezig.
Op deze manier kan ik dus 1 item gebruiken en in de query van de LOV de juiste gegevens selecteren op basis van de Build Option. De api kan natuurlijk op vele manieren worden gebruikt dit dient slechts als voorbeeld.
Je kunt met de standaard apex tools dus “gewoon” bugs oplossen terwijl er ondertussen wordt gebouwd aan een nieuwe release.
Reset pagination van een regioWaarom zou je de pagination willen resetten? Als je met APEX werkt zul je al snel een keer de volgende melding zien:
Als je in het voorbeeld in figuur 1 een nieuwe regel in de master selecteert, waarbij er minder dan 3 pagina’s detail gegevens zijn, dan zal je bij het detail rapport de eerder genoemde melding krijgen.
Figuur 1
———- ———-
| master | | detail |
——————————- ——————————-
| ……… | | ……… |
| ……… | | ……… |
| ……… | | ……… |
| ……… | | ……… |
| ……… | | ……… |
| | | |
| pag 5/6 | | pag 3/5 |
——————————- ——————————-
Deze melding kan je voorkomen door zelf de reset pagination te doen. Het resetten van de pagination kun je in APEX doen via de URL.
De URL voor een APEX applicatie ziet er als volgt uit
URL opbouw : f?p=1:2:3:4:5:6:7:8:9
1 – Application ID or alias
2 – Page ID or Alias
3 – Session &SESSION. :APP_SESSION
4 – Request (eg PRINT_REPORT=REPORT_1) &REQUEST. :REQUEST
5 – Debug (YES or NO) &DEBUG. :DEBUG
6 – Cache setting
APP – Clear cache for whole application
SESSION – Clear cache for current user session
RP – Reset pagination
x – Clear cache for page x
y – Clear cache for item y
7 – Comma separated list of page items
8 – Comma separated list of values
9 – Printer friendly mode (either YES or blank)
Zoals je ziet kun je via de URL veel sturing geven aan je pagina. Het gaat nu echter alleen om het “Cache setting” gedeelte waarbij je de tekst “RP” kunt zetten om een reset van de pagination te doen.
Handig? Ja dat wel, alleen wordt op deze manier de pagination gereset voor ALLE regio’s op de pagina. Als je meerdere rapporten in een master-detail constructie op je pagina hebt staan dan worden deze dus allemaal gereset.
Dit kan je voorkomen door in het “Request” gedeelte de tekst “pg_R_XXX” op te nemen, waarbij XXX staat voor het ID van de regio. Het is niet goed om dit ID hard te programmeren omdat het kan wijzigen als je een import van de applicatie doet. Ik heb dit opgelost door een hidden item op te nemen. Dit hidden item geef ik als “Source type” op dat het een “SQL Query” is. In de Source value or expression komt de volgende code:
Nu kan je dit hidden item op verschillende manieren gebruiken bij het request. Via een javascipt aanroep of link in een rapport.
xx
Oplossing voor ORA-02050 en ORA-02051Onlangs liep ik tegen een vervelend probleem aan: een in de ontwikkel- en testomgeving prima draaiend batchprogramma gaf in de acceptatieomgeving opeens allerlei foutmeldingen.
Wat was er aan de hand? Een aanzienlijke stored procedure (package) in een Oracle 11g database had tot doel data van een tweetal databases te converteren naar een derde. Dit gebeurde in een grote loop, met allerlei cursoren waarbij in 75% van de gevallen met databaselinks werd gewerkt. Na elke doorloop van de buitenste loop werd een commit uitgevoerd. Hier ging het mis: na de commit trad een foutmelding op: ORA-02050 (transaction string rolled back, some remote DBs may be in-doubt) gevolgd door ORA-02051 (another session in same transaction failed). De integriteit van de database die over de databaselink wordt benaderd ten opzicht van de opgehaalde dataset wordt betwijfeld en Oracle draait de hele transactie terug. Verschil tussen ontwikkel en test aan de ene kant en acceptatie was de hoeveelheid data-activiteit. In acceptatie waren behoorlijk veel transacties in de gelinkte databases, in ontwikkel en test vrijwel geen.
Belangrijk om te weten is dat Oracle ook bij het uitsluitend opvragen van data via een databaselink toch een transactie start. Zolang er geen commit wordt gegeven, blijven de opgevraagde rijen dus in de sessie gereserveerd.
De oplossing voor het probleem is derhalve, om direct na het opvragen van de rij(en) via een databaselink een commit uit te voeren. Meestal is het echter in een grote loop, waarin allerlei controles dienen te worden uitgevoerd niet wenselijk om tussentijdse commits uit te voeren. Vandaar dat ik er voor gekozen heb om alle cursoren te vervangen door functies, die via een autonomous_transaction pragma een aparte sessie starten om de data op te halen. Deze kan vervolgens afgesloten worden met een commit en het probleem is verholpen.
Een voorbeeld:
Oude situatie
[code type=”sql”]
declare
—
cursor c_tst(b_parameter in varchar2)
is
select *
from tst_tabel
where code = b_parameter;
—
begin
—
for r_tst in c_tst(‘FOO’) loop
…
end loop;
—
commit;
end;
[/code]
Nieuwe situatie
[code type=”sql”]
declare
type t_tst is table of tst_tabel%rowtype index by binary_integer;
l_tst t_tst;
—
function get_tst(p_parameter)
return t_tst
is
pragma autonomous_transaction;
l_tst t_tst;
—
cursor c_tst(b_parameter in varchar2)
is
select *
from tst_tabel
where code = b_parameter;
—
begin
open c_tst(p_parameter);
fetch c_tst bulk collect into l_tst;
close c_tst;
commit;
return l_tst;
end get_tst;
—
begin
l_tst := get_tst(‘FOO’);
for i in 1..l_tst.count loop
…
end loop;
commit;
end;
[/code]
Beetje meer code, maar wel opgelost.
Kalender deel 2: WerkdagenOracle SQL biedt standaard functionaliteit voor het berekenen van het aantal dagen of het aantal maanden tussen twee datums. Bijvoorbeeld:
SQL> select to_date('16112013','ddmmyyyy') - to_date('06052013','ddmmyyyy') "days" from dual;
days
----------
194
SQL> select months_between(to_date('16112013','ddmmyyyy'), to_date('16052013','ddmmyyyy')) "months" from dual;
months
----------
6
Ook het optellen van dagen of maanden bij een datum is eenvoudig met SQL te doen.
SQL> select to_date('06052013','ddmmyyyy') + 23 "date" from dual;
date
----------
29-05-2013
SQL> select add_months(to_date('06052013','ddmmyyyy'),3) "date" from dual;
date
----------
06-08-2013
Daarentegen is voor het bepalen van het aantal werkdagen tussen twee datums of het optellen van werkdagen bij een datum niet standaard in SQL voorzien. Dus zelf maar iets fabriceren. Het doel is de volgende twee functies:
Voor de flexibiliteit heb ik aan de functies de parameter p_no_business toegevoegd. Hiermee wordt aangegeven welke dagen GEEN werkdagen zijn. De parameter kan combinaties van de volgende dagnummers bevatten:
1 = maandag
2 = dinsdag
3 = woensdag
4 = donderdag
5 = vrijdag
6 = zaterdag
7 = zondag
0 = feestdag
De default waarde van p_no_business is “067”. In dit geval worden de zater-, zon- en feestdagen niet gekenmerkt als een werkdag.
De voor de hand liggende manier om de dagnummer van een datum te achterhalen is met: to_char(p_date,'d')
. Deze wijze is echter afhankelijk van de Oracle parameter NLS_TERRITORY. Ik gebruik daarom liever de formule: trunc(sysdate) - trunc(sysdate,'IW')
. Onderstaande query maakt dit duidelijk.
SQL> alter session set nls_territory = 'AMERICA';
SQL> select value nls_territory
, to_char(sysdate,'day') day
, to_char(sysdate,'d') no_1
, trunc(sysdate) - trunc(sysdate,'IW') + 1 no_2
from v$nls_parameters
where parameter = 'NLS_TERRITORY';
SQL> alter session set nls_territory = 'THE NETHERLANDS';
SQL> select value nls_territory
, to_char(sysdate,'day') day
, to_char(sysdate,'d') no_1
, trunc(sysdate) - trunc(sysdate,'IW') + 1 no_2
from v$nls_parameters
where parameter = 'NLS_TERRITORY';
NLS_TERRITORY DAY NO_1 NO_2
--------------- ------- ---- ----
AMERICA maandag 2 1
NLS_TERRITORY DAY NO_1 NO_2
--------------- ------- ---- ----
THE NETHERLANDS maandag 1 1
Indien parameter p_no_business “0” bevat, is het van belang de feestdagen te kunnen bepalen. Dit staat beschreven in mijn vorige blog; wat resulteert in de volgende code.
create or replace package calendar
/**********************
* 2013 www.orcado.nl *
**********************/
is
type holiday_rec_typ is record(holiday_name varchar2(25)
,holiday_date date);
type holiday_tab_typ is table of holiday_rec_typ;
end calendar;
/
create or replace function holidays
(p_year in pls_integer)
return calendar.holiday_tab_typ pipelined
/**********************
* 2013 www.orcado.nl *
**********************/
is
l_easter_sunday date := easter_sunday(p_year);
l_kingsday date := kingsday(p_year);
function set
(p_name in varchar2
,p_date in date)
return calendar.holiday_rec_typ
is
l_rec calendar.holiday_rec_typ;
begin
l_rec.holiday_name := p_name;
l_rec.holiday_date := p_date;
return l_rec;
end set;
begin
if p_year > 1952 then
pipe row(set('Nieuwjaarsdag',to_date('0101'||p_year,'ddmmyyyy')));
pipe row(set('1e Kerstdag',to_date('2512'||p_year,'ddmmyyyy')));
pipe row(set('2e Kerstdag',to_date('2612'||p_year,'ddmmyyyy')));
end if;
if p_year > 1989 then
pipe row(set('Bevrijdingsdag',to_date('0505'||p_year,'ddmmyyyy')));
end if;
if l_kingsday is not null then
pipe row(set('Koning' || (case when p_year < 2014 then 'inne' else 's' end) || 'dag',l_kingsday));
end if;
if l_easter_sunday is not null then
pipe row(set('Goede vrijdag',l_easter_sunday - 2));
pipe row(set('1e Paasdag',l_easter_sunday));
pipe row(set('2e Paasdag',l_easter_sunday + 1));
pipe row(set('Hemelvaartsdag',l_easter_sunday + 39));
pipe row(set('1e Pinksterdag',l_easter_sunday + 49));
pipe row(set('2e Pinksterdag',l_easter_sunday + 50));
end if;
return;
end holidays;
/
SQL> select holiday_name "Name"
, to_char(holiday_date,'dy dd-mm-yyyy') "Date"
from table(holidays(2013))
order by holiday_date;
Name Date
--------------- -------------
Nieuwjaarsdag di 01-01-2013
Goede vrijdag vr 29-03-2013
1e Paasdag zo 31-03-2013
2e Paasdag ma 01-04-2013
Koninginnedag di 30-04-2013
Bevrijdingsdag zo 05-05-2013
Hemelvaartsdag do 09-05-2013
1e Pinksterdag zo 19-05-2013
2e Pinksterdag ma 20-05-2013
1e Kerstdag wo 25-12-2013
2e Kerstdag do 26-12-2013
Het voorgaande over dagnummers en feestdagen verwerk ik in een functie, check_business_day, die kan achterhalen of een gegeven dag een werkdag is.
create or replace function check_business_day
(p_date in date
,p_no_business in varchar2 := '067')
return boolean
/**********************
* 2013 www.orcado.nl *
**********************/
is
l_day_no pls_integer := trunc(p_date) - trunc(p_date,'IW') + 1;
l_dummy pls_integer;
begin
if instr(p_no_business,l_day_no) > 0 then
return false;
end if;
if instr(p_no_business,'0') > 0 then
begin
select 1
into l_dummy
from table(holidays(to_char(p_date,'yyyy')))
where trunc(holiday_date) = trunc(p_date);
return false;
exception
when others then
null;
end;
end if;
return true;
end check_business_day;
/
SQL> set serveroutput on
SQL> declare
procedure p
(p_date in varchar2
,p_no_business in varchar2)
is
l_date date := to_date(p_date,'ddmmyyyy');
begin
dbms_output.put_line(to_char(l_date,'dy dd-mm-yyyy')
|| ' '
|| rpad(p_no_business,13)
|| (case when check_business_day(l_date,p_no_business)
then '+'
else '-'
end));
end p;
begin
dbms_output.put_line('Date No business Business day');
dbms_output.put_line('-------------- ------------ ------------');
p('09052013','067');
p('10052013','067');
p('11052013','067');
p('12052013','067');
p('09052013','07');
p('10052013','07');
p('11052013','07');
p('12052013','07');
p('09052013','7');
p('10052013','7');
p('11052013','7');
p('12052013','7');
end;
/
Date No business Business day
-------------- ------------ ------------
do 09-05-2013 067 -
vr 10-05-2013 067 +
za 11-05-2013 067 -
zo 12-05-2013 067 -
do 09-05-2013 07 -
vr 10-05-2013 07 +
za 11-05-2013 07 +
zo 12-05-2013 07 -
do 09-05-2013 7 +
vr 10-05-2013 7 +
za 11-05-2013 7 +
zo 12-05-2013 7 -
Een eenvoudige manier om nu het aantal werkdagen tussen twee datums te berekenen is gebruik te maken van een PL/SQL-loop en de functie check_business_day.
create or replace function business_days_between
(p_start_date in date
,p_end_date in date
,p_no_business in varchar2 := '067')
return pls_integer
/**********************
* 2013 www.orcado.nl *
**********************/
is
j pls_integer := 0;
begin
for i in 0..(trunc(p_end_date) - trunc(p_start_date)) loop
if check_business_day(p_start_date + i,p_no_business) then
j := j + 1;
else
if i = 0 then
j := 1;
end if;
end if;
end loop;
return j - 1;
end business_days_between;
/
SQL> def p_date = "13052013"
SQL> select to_char(to_date('&p_date','ddmmyyyy'),'dy dd-mm-yyyy') "start date"
, to_char(to_date('&p_date','ddmmyyyy') + level - 1,'dy dd-mm-yyyy') "end date"
, business_days_between(to_date('&p_date','ddmmyyyy')
,to_date('&p_date','ddmmyyyy') + level - 1
,'67') "business days"
from dual
connect by level <= 20;
start date end date business days
------------- ------------- -------------
ma 13-05-2013 ma 13-05-2013 0
ma 13-05-2013 di 14-05-2013 1
ma 13-05-2013 wo 15-05-2013 2
ma 13-05-2013 do 16-05-2013 3
ma 13-05-2013 vr 17-05-2013 4
ma 13-05-2013 za 18-05-2013 4
ma 13-05-2013 zo 19-05-2013 4
ma 13-05-2013 ma 20-05-2013 5
ma 13-05-2013 di 21-05-2013 6
ma 13-05-2013 wo 22-05-2013 7
ma 13-05-2013 do 23-05-2013 8
ma 13-05-2013 vr 24-05-2013 9
ma 13-05-2013 za 25-05-2013 9
ma 13-05-2013 zo 26-05-2013 9
ma 13-05-2013 ma 27-05-2013 10
ma 13-05-2013 di 28-05-2013 11
ma 13-05-2013 wo 29-05-2013 12
ma 13-05-2013 do 30-05-2013 13
ma 13-05-2013 vr 31-05-2013 14
ma 13-05-2013 za 01-06-2013 14
Op een vergelijkbare wijze is het ook eenvoudig een aantal werkdagen bij een datum op te tellen. Indien het aantal werkdagen is 0, dan wordt de eerst volgende werkdag geretourneerd.
create or replace function add_business_days
(p_date in date
,p_number in pls_integer
,p_no_business in varchar2 := '067')
return date
/**********************
* 2013 www.orcado.nl *
**********************/
is
i pls_integer := 0;
j pls_integer := 0;
begin
if p_number = 0 then
while not check_business_day(p_date + i,p_no_business) loop
i := i + 1;
end loop;
else
while j < abs(p_number) loop
i := i + sign(p_number);
if check_business_day(p_date + i,p_no_business) then
j := j + 1;
end if;
end loop;
end if;
return p_date + i;
end add_business_days;
/
SQL> def p_date = "13052013"
SQL> select to_char(to_date('&p_date','ddmmyyyy'),'dy dd-mm-yyyy') "start date"
, level - 1 "days"
, to_char(add_business_days(to_date('&p_date','ddmmyyyy')
,level - 1
,'67'),'dy dd-mm-yyyy') "end date"
from dual
connect by level <= 20;
start date days end date
------------- ---------- -------------
ma 13-05-2013 0 ma 13-05-2013
ma 13-05-2013 1 di 14-05-2013
ma 13-05-2013 2 wo 15-05-2013
ma 13-05-2013 3 do 16-05-2013
ma 13-05-2013 4 vr 17-05-2013
ma 13-05-2013 5 ma 20-05-2013
ma 13-05-2013 6 di 21-05-2013
ma 13-05-2013 7 wo 22-05-2013
ma 13-05-2013 8 do 23-05-2013
ma 13-05-2013 9 vr 24-05-2013
ma 13-05-2013 10 ma 27-05-2013
ma 13-05-2013 11 di 28-05-2013
ma 13-05-2013 12 wo 29-05-2013
ma 13-05-2013 13 do 30-05-2013
ma 13-05-2013 14 vr 31-05-2013
ma 13-05-2013 15 ma 03-06-2013
ma 13-05-2013 16 di 04-06-2013
ma 13-05-2013 17 wo 05-06-2013
ma 13-05-2013 18 do 06-06-2013
ma 13-05-2013 19 vr 07-06-2013
Hier komt de sidebar