Currency Converter APEX plug-in

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.

select distinct extractvalue(value(x), ‘CcyNtry/CcyNm/text()’) ccynm
, extractvalue(value(x), ‘CcyNtry/Ccy/text()’)   ccy
from table(xmlsequence(extract(sys.xmltype.createxml(sys.urifactory.geturi(‘http://www.currency-iso.org/dam/downloads/table_a1.xml’) .getclob()), ‘//ISO_4217/CcyTbl/CcyNtry’))) x
order by ccynm

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 xmltype

Heel 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.

v() en nv() mooie functies maar….

Inleiding

 

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.

Elementen betrokken bij het probleem

En dan begint het zoeken…

 

De verwachting is dat het probleem niet in de hoofdpagina met de “iframe container”, waarin de tabbladen worden geopend, zou zitten want die werkt voor de overige tabbladen prima. Daarbij komt nog dat een performance probleem “meestal” in een query zit.  Uiteindelijk de query opgezocht die gebruikt wordt.
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.

En dan nog oplossen…

 

Nu had ik het probleem dat ik wel wist waar het probleem zat maar hoe ga ik het nu oplossen en toch gebruik maken van deze nv() functie? Wat dacht je van een scalar subquery? De scalar subquery expression is een subquery die exact één kolom waarde van één record terug geeft. Om dit te testen is de query als volgt aangepast
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)
Dit heb ik voor het gemak direct in het package gedaan zodat het wat makkelijker te testen is. Vervolgens dezelfde zoekopdracht gegeven als waar ik mee begonnen ben en jawel hoor het probleem is opgelost!

Conclusie

 

– Indien mogelijk gebuik dan in je apex applicatie altijd bind variabelen ipv de n() en nv() functies.
– Scalar subqueries zijn een zeer handige oplossing als je de n() en nv() functies wel wil/moet gebruiken.
– Nu nog even mijn eigen applicaties controleren op het gebruik van de n() en nv() functies en aanpassen!
Buffer cache hit ratio verbeteren door tactisch schedulen

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.

Apex bug fixing tijdens applicatie release

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.

 

Creëer Build Option.

Dit doe je bij de Shared Components
Hier kan je een nieuwe Build Option aanmaken.

buildoption1

 

Gebruik van de Build Option.

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.

buildoption3

buildoption4.

 

Productie of niet?

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.

buildoption2

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.

 

Niet declaratief

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.

select case when apex_util.get_build_option_status(p_application_id    => 200,
p_build_option_name => ‘IBIS 2.4 (Signalen)’) = ‘EXCLUDE’
then ‘(Signaal)’
else ‘Signaal’
end
,
case when apex_util.get_build_option_status(p_application_id    => 200,
p_build_option_name => ‘IBIS 2.4 (Signalen)’) = ‘EXCLUDE’
then ‘SIG_exclude’
else ‘SIG’
end
from dual

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.

 

Conclusie

Je kunt met de standaard apex tools dus “gewoon” bugs oplossen terwijl er ondertussen wordt gebouwd aan een nieuwe release.

Reset pagination van een regio

Waarom zou je de pagination willen resetten? Als je met APEX werkt zul je al snel een keer de volgende melding zien:

Pagination error

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:

select ‘pg_R_’||to_char(region_id)
from apex_application_page_regions
where application_id = :app_id
and page_id        = :app_page_id
and static_id      = ‘static value opgegeven bij regio’

Nu kan je dit hidden item op verschillende manieren gebruiken bij het request. Via een javascipt aanroep of link in een rapport.

top.location.href=’f?p=&APP_ID.:125:&APP_SESSION.:&P125_HIDDEN_ITEM_WITH_ID.:&DEBUG.::’

Column linkxx

Oplossing voor ORA-02050 en ORA-02051

Onlangs 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: Werkdagen

Oracle 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:

 

Dagnummers

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

 

Feestdagen

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

 

check_business_day

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            -

 

business_days_between

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

 

add_business_days

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

Volg ons op

© Orcado B.V. | 1999 - 2013