Y-weblog

In een eerdere bijdrage schreef ik over het gebruik van PL/SQL-records en ref cursoren.

Omdat ik binnenkort aan de slag mag voor een klant waarbij de opdracht luidt een procedure te schrijven die een geneste recordstructuur ontvangt en deze wegschrijft naar de diverse onderliggende tabellen, ben ik aan de slag gegaan om uit te zoeken hoe dit zou kunnen gaan in een voorbeeldsituatie
Ik heb de volgende opzet bedacht:

• We hebben een bedrijf dat meerdere adressen (vestigingen) heeft en uit meerdere afdelingen bestaat
• Een afdeling heeft meerdere werknemers
• Een werknemer heeft een adres.

De bedoeling is, dat de verwerkende programmatuur 1 object ontvangt waarin het bedrijf + de onderliggende gegevens aanwezig zijn, en deze gegevens weer afzonderlijk beschikbaar maakt voor verdere verwerking.

Het bedrijf Ywemacom import/export heeft 2 adressen; een hoofdadres en een nevenadres
Hoofdadres: Hoofdstraat 1, Amersfoort
Nevenadres: Nevenstraat 1, Amersfoort
Het bedrijf heeft 2 afdelingen, elk met 2 werknemers. Iedere werknemer heeft 1 adres
Afdeling 1 :Verkoop
Werknemer 1: Jan de Groot
Adresgegevens: Hugo de Grootstraat, te Utrecht
Werknemer 2: Karen Veenstra
Adresgegevens: Winkelstraat, te Amsterdam
Afdeling 2: Transploft
Werknemer 1: Joop Schroefstra
Adresgegevens: Stadsring, te Amersfoort
Werknemer 2: Henk Woudenberg
Adresgegevens: Vinkenhof, te Scherpenzeel

In dit voorbeeld worden deze gegevens in de eerste procedure in de package body, ‘VUL’ , in regels 38 – 64 aan het object bedrijf_rec toegevoegd.
Hierna wordt dit object in regel 67 aan de tweede procedure, ‘TOON’ als parameter meegegeven.
Deze procedure pluist het object uit-elkaar en toont de inhoud dmv DBMS_OUTPUT.PUT_LINE. In de praktijk zal in plaats hiervan een serie inserts in tabellen plaatsvinden.

De stored-package BEDRIJF:

CREATE OR REPLACE package bedrijf as
--
   type adres_rec is record (straat varchar2(100)
                            ,nummer number
                            ,plaats varchar2(100)
                            ,adrestype varchar2(100)
                            );
   type adres_tab is table of adres_rec index by pls_integer;
   --
   type werknemer_rec is record (naam varchar2(100)
                                ,adres adres_rec
                                );
   type werknemer_tab is table of werknemer_rec index by pls_integer;
   --
   type afdeling_rec is record (naam varchar2(100)
                              , werknemer werknemer_tab
                              );
   type afdeling_tab is table of afdeling_rec index by pls_integer;
   --
   type bedrijf_rec is record (naam varchar2(100)
                          , adres adres_tab
                          , afdeling afdeling_tab
                          );
--
procedure vul;
end bedrijf;
/

CREATE OR REPLACE package body bedrijf as
--
procedure toon (p_bedrijf bedrijf_rec);
--
procedure vul is
-- vul een bedrijfsrecord
--
   l_bedrijf bedrijf_rec;
begin
   l_bedrijf.naam := 'Ywemacom import/export';  -- record-type
      l_bedrijf.adres(1).adrestype := 'Hoofdadres'; -- table-type
      l_bedrijf.adres(1).straat := 'Hoofdstraat';
      l_bedrijf.adres(1).nummer :=1;
      l_bedrijf.adres(1).plaats := 'Amersfoort';
      l_bedrijf.adres(2).adrestype := 'Nevenadres';
      l_bedrijf.adres(2).straat := 'Nevenstraat';
      l_bedrijf.adres(2).nummer :=1;
      l_bedrijf.adres(2).plaats := 'Amersfoort';
      l_bedrijf.afdeling(1).naam := 'Verkoop';
         l_bedrijf.afdeling(1).werknemer(1).naam := 'Jan de Groot';
            l_bedrijf.afdeling(1).werknemer(1).adres.straat := 'Hugo de Grootstraat';
            l_bedrijf.afdeling(1).werknemer(1).adres.nummer := 1;
            l_bedrijf.afdeling(1).werknemer(1).adres.plaats := 'Utrecht';
         l_bedrijf.afdeling(1).werknemer(2).naam := 'Karen Veenstra';
            l_bedrijf.afdeling(1).werknemer(2).adres.straat := 'Winkelstraat';
            l_bedrijf.afdeling(1).werknemer(2).adres.nummer := 2;
            l_bedrijf.afdeling(1).werknemer(2).adres.plaats := 'Amsterdam';
      l_bedrijf.afdeling(2).naam := 'Transploft';
         l_bedrijf.afdeling(2).werknemer(1).naam := 'Joop Schroefstra';
         l_bedrijf.afdeling(2).werknemer(1).adres.straat := 'Stadsring';
            l_bedrijf.afdeling(2).werknemer(1).adres.nummer := 15;
            l_bedrijf.afdeling(2).werknemer(1).adres.plaats := 'Amersfoort';
            l_bedrijf.afdeling(2).werknemer(2).naam := 'Henk Woudenberg';
            l_bedrijf.afdeling(2).werknemer(2).adres.straat := 'Vinkenhof';
            l_bedrijf.afdeling(2).werknemer(2).adres.nummer := 78;
            l_bedrijf.afdeling(2).werknemer(2).adres.plaats := 'Scherpenzeel';
--
-- roep de procedure aan met het bedrijfs-object als parameter
   toon(p_bedrijf => l_bedrijf);
end vul;
--
procedure toon (p_bedrijf bedrijf_rec) is
-- pluis het bedrijfs-object uit en druk de inhoud af
   l_bedrijf bedrijf_rec;
begin
   l_bedrijf := p_bedrijf;
   --
   dbms_output.put_line ('Bedrijf: '||l_bedrijf.naam); -- 1 bedrijf
   dbms_output.put_line ('Adresgegevens: ');
   for bedrijfsadressen in l_bedrijf.adres.first..l_bedrijf.adres.last loop
       dbms_output.put_line (l_bedrijf.adres(bedrijfsadressen).adrestype||': '||l_bedrijf.adres(bedrijfsadressen).straat||' '
                              ||l_bedrijf.adres(bedrijfsadressen).nummer||' te '
                              ||l_bedrijf.adres(bedrijfsadressen).plaats);
    end loop;
   -- afdelingen af
   for afdelingen in l_bedrijf.afdeling.first ..l_bedrijf.afdeling.last loop
      dbms_output.put_line ('Afdeling '||afdelingen||' is: '||l_bedrijf.afdeling(afdelingen).naam);
      -- werknemers af
      for werknemers in l_bedrijf.afdeling(afdelingen).werknemer.first..l_bedrijf.afdeling(afdelingen).werknemer.last loop
         dbms_output.put_line('Werknemer '||werknemers|| ' is: '||l_bedrijf.afdeling(afdelingen).werknemer(werknemers).naam);
         -- bijbehorende adresgegevens
         dbms_output.put_line('Adresgegevens: '||l_bedrijf.afdeling(afdelingen).werknemer(werknemers).adres.straat||' '
                                                  ||l_bedrijf.afdeling(afdelingen).werknemer(werknemers).adres.nummer||' te '
                                                  ||l_bedrijf.afdeling(afdelingen).werknemer(werknemers).adres.plaats);
      end loop;
   end loop;
end toon;
--

end bedrijf;
/

Aanroep in sql*plus en het resultaat

SQL> set serveroutput on
SQL> begin
2      bedrijf.vul;
3  end;
4  /

Bedrijf: Ywemacom import/export
Adresgegevens:
Hoofdadres: Hoofdstraat 1 te Amersfoort
Nevenadres: Nevenstraat 1 te Amersfoort
Afdeling 1 is: Verkoop
Werknemer 1 is: Jan de Groot
Adresgegevens: Hugo de Grootstraat 1 te Utrecht
Werknemer 2 is: Karen Veenstra
Adresgegevens: Winkelstraat 2 te Amsterdam
Afdeling 2 is: Transploft
Werknemer 1 is: Joop Schroefstra
Adresgegevens: Stadsring 15 te Amersfoort
Werknemer 2 is: Henk Woudenberg
Adresgegevens: Vinkenhof 78 te Scherpenzeel

PL/SQL procedure successfully completed.

SQL>

Ik had de volgende uitdaging:
ik wil met SQL*Loader de data van een csv-bestand inlezen maar heb voor 1 kolom de waarde nodig van een veld in de database (de huidige waarde van een sequence).
Een query doen binnen een SQL*Loader-controlfile is niet mogelijk net als het meegeven van een custom-parameter aan het script.
In de documentatie wordt gesproken van het toepassen van functies op de kolommen, maar dit houdt in de praktijk in het upper- of lowercase zetten of anderszins formatteren van de gegevens.
Na lang zoeken kwam ik een relevant voorbeeld tegen in de voorbeeld-bestanden van het boek Oracle SQL*Loader: The Definitive Guide ( April, 2001) geschreven door Jonathan Gennick en Sanjay Mishra waarin gebruik werd gemaakt van een stored function. Bingo!
Om niet aan de copyrights te tornen van dit boek is de implementatie hieronder van mijzelf.

brondata data.csv

Code;Omschrijving
10;Mutatie
20;In onderzoek
30;Administratieve wijziging
40;Afgevoerd

doeltabel

CREATE TABLE TEST
( ID              NUMBER,
  SEQ_ID          NUMBER                        NOT NULL,
  CODE            VARCHAR2(4 BYTE),
  OMSCHRIJVING    VARCHAR2(150 BYTE),
  CONSTRAINT TEST_PK PRIMARY KEY (ID));

database-sequence

CREATE SEQUENCE TEST_SEQ
  START WITH 1
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  NOCACHE
  NOORDER;

stored function

CREATE OR REPLACE FUNCTION get_seq_id
RETURN NUMBER
IS
/*******************************************************************************
Naam   : get_seq_id
Auteur : Louis Ywema
Datum  : 07-01-2009
Doel   : Deze functie retourneert de huidige waarde van sequence TEST_SEQ
Opmerkingen :

Wijzigingen
Datum      Naam          Beschrijving
---------- ------------- -------------------------
22-01-2009 Patrick Barel Error handling toegevoegd
*******************************************************************************/
–
l_return NUMBER := 0;
BEGIN
   BEGIN
      SELECT last_number - 1 — last_number is blijkbaar de waarde van nextval
      INTO l_return
      FROM user_sequences
      WHERE sequence_name = 'TEST_SEQ';
   EXCEPTION
      WHEN OTHERS THEN
         l_return := 0;
   END;
–-
   RETURN NVL( l_return, 1 );
END;
/

sql*loader-controlfile test.ctl

OPTIONS ( ERRORS=0, SKIP=1, DIRECT=TRUE)
load data
TRUNCATE INTO TABLE TEST
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
(CODE,
 OMSCHRIJVING,
 SEQ_ID "get_seq_id",   -- functie get_seq_id haalt het nummer op
 ID   SEQUENCE(MAX,1)   -- genereert een volgnummer
 )

commandscript inlezen_test.cmd om sql*loader-controlfile op te starten

@ECHO OFF
set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
set oracle_home=C:\oracle\product\9.2.0
%ORACLE_HOME%\bin\SQLLDR USERID=/
@ CONTROL=test.ctl LOG=test.log, BAD=test.bad, DATA=data.csv , DISCARD=test.dis,  DISCARDMAX=5

uitvoering

C:\TEMP\blog>inlezen_test.cmd

SQL*Loader: Release 9.2.0.6.0 - Production on Thu Jan 8 19:54:03 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Load completed - logical record count 4.

C:\TEMP\blog>

resultaat

SQL> select * from test;

        ID     SEQ_ID CODE OMSCHRIJVING
---------- ---------- ---- -----------------------------------------------------
         1          1 10   Mutatie
         2          1 20   In onderzoek
         3          1 30   Administratieve wijziging
         4          1 40   Afgevoerd

SQL>

conclusie
Door het gebruiken van een stored PL/SQL-functie is het mogelijk om reeds aanwezige data in de database te gebruiken met SQL*Loader

Een enkele keer komt het voor dat ik een MS-DOS command-script wil gebruiken dat weer een Oracle-sql-script aanroept dat een stored function uitvoert. Hoe krijg je dan de uitkomst van die functie terug naar je command-omgeving?
1. Een simpele functie

create or replace function optellen (p_getal1 number
,p_getal2 number)
return number is
begin
return (p_getal1 + p_getal2);
end;
/

2. Het sql-script

-- optellen.sql
var  l_uitkomst number
begin
:l_uitkomst :=optellen(&1,&2);
end;
/
print :l_uitkomst;
exit;

3. Het command-script

rem optellen.cmd
@ECHO OFF
for /f %%D in ('sqlplus -s hr/hr@xe @optellen.sql %1 %2') do (
set res=%%D)
echo uitkomst is %res%

4. Uitvoering
Functie laden in de database en het command-script uitvoeren

C:\Documents and Settings\Administrator\Desktop>optellen.cmd 1 2

C:\Documents and Settings\Administrator\Desktop>rem optellen.cmd
uitkomst is 3

Ik heb de afgelopen dagen (30 november t/m 2 december) de FME-gebruikersconferentie in Münster bezocht en heb daar een aantal interessante presentaties bijgewoond.
Eerst iets over FME.

Eerlijk gezegd zou ik erg graag eerder met dit programma gewerkt willen hebben! Ik ben inmiddels meer dan 10 jaar professioneel bezig met Oracle-databases, en omdat je niets hebt aan een lege database, ook met het vullen ervan. FME is Haarlemmerolie voor je data. Ieder dataformaat dat redelijkerwijze gebruikt wordt kun je er mee lezen en schrijven. Wanneer ik een Excel of csv-bestand de database in wil krijgen schrijf ik sinds FME geen sql*loader meer, maar maak een FME-workbench; veel sneller en overzichtelijker,en: meerdere malen toepasbaar!

De FME-gebruikersdagen begonnen op maandag, maar de avond ervoor zijn wij al met de directeur van Safe software (de maker van FME), Don Murray uit Toronto, Canada op stap geweest; een bijzonder aardige man en razend enthousiast over zijn eigen spullen! Wat ik eigenlijk wel vreemd vond (maar ook wel aardig) is, dat hoewel FME nog maar zo’n 7500 gebruikers wereldwijd heeft, deze man ze gewoon opzoekt (en ze daardoor bijna alle persoonlijk moet kennen).

Dag 1

Na de registratie (waarbij de onvermijdelijke badge en het tasje) werden de presentaties geopend door de Duitstalige dagvoorzitter Christian Heisig, waarna Don de ochtend vulde met aardige presentaties over de vernieuwingen in FME2009 (waaronder de quick-connect mogelijkheid wat erg handig is wanneer de transformers erg ver uit elkaar liggen; je klikt op de ene en daarna op de andere en ze zijn verbonden, je kunt nu eindelijk van de source-dataset de attributen wijzigen, de workbench kun je nu uitrusten met een soort van generic source en destination waarbij je het datatype en de dataset pas op moment van uitvoeren kiest en waarbij de bronattributen toch als geheel worden meegenomen, erg sterk!), en daarna een presentatie over FME-server waarbij de de gemaakte workbench uploadt naar een FME-server waarna iedereen met een webbrowser de workbench kan uitvoeren nadat de parameters zijn gevuld en de brondata is ge-upload (deze presentatie had ik eerder bij Vicrea al gezien).

Daarna heb ik de presentaties van Klaas Dijkstra van NAVTEQ bijgewoond, waarbij hij vertelde hoe FME wordt ingezet bij het vervaardigen van digitaal kaartmateriaal (in mijn Garmin-GPS zit Navteq-kaartmateriaal), de presentatie van Hans van der Maarel, die vertelde hoe hij voor Rijkswaterstaat en de ANWB het kaartmateriaal helpt te maken met FME, en de presentatie van de Engelsman James Bowler die liet zien hoe met FME een website voor de Engelse verkiezingen (met kiesdistricten) is gemaakt. Als laatste de presentatie van Wibold Jongsma van het Ontwikkelingsbedrijf van de Gemeente Amsterdam (de presentatie waaraan ik ook input heb geleverd) die vertelde hoe FME wordt ingezet bij grondexploitatie-berekeningen bij de ontwikkeling van stedelijke ontwikkelingen. Gelukkig geen moeilijke vragen voor mij, hoewel ik de complete omgeving op mijn laptop bij me had.

‘s Avonds hebben we de kerstmarkt bezocht en was er een diner in restaurant A² aan het AA-meer.

Hier raakte ik meteen aan de praat met Barry Masterson, een Ierse medewerker van Con Terra (het bedrijf dat deze conferentie heeft georganiseerd) die net als ik een enthousiaste bierdrinker en thuisbrouwer is. ’s Avonds beloofde hij mij de volgende dag een paar brouwsels mee te geven.

Dag 2

De tweede dag begon met de presentatie van mijn collega Erik Jan Bodewitz waarin hij dieper inging op het gebruik van Oracle Spatial (en de uitdagingen die je daar tegen kunt komen); hij had slechts een tiental bijwoners verwacht maar de zaal zat vol om te luisteren naar deze goede presentatie. Hierna bezocht ik de presentatie van Wolfgang Hausch over de installatie van FME Server. Helaas deed de internetverbinding het niet waardoor hij geen live-voorbeelden kon geven. De laatste gast-presentatie werd gegeven door Rick Klooster van de gemeente Apeldoorn waarbij hij virtueel Apeldoorn liet zien; een mooie toepassing van hoe ruimtelijke data de burgers van Apeldoorn kan dienen.

De middag begon met de presentatie van Brett Madsen en Jeff Konnen waarbij ze vertelden hoe je een echte FME-guru kunt worden (lees de bijdragen van de FME Evangelist, doe mee met de FME Talk discussiegroep) en met welke opgaven ze vorig jaar bij de FME Idol-wedstrijd werden geconfronteerd. Hierna nam Don Murray het weer over met een blik in de toekomst (FME 2010 en verder) en een inventarisatie van wensen van gebruikers.

Hierna vond ik het welletjes en zijn we richting huis vertrokken.

De niet-techneuten onder de bezoekers hadden het maar moeilijk met al die transformers (maar wat moet je ánders verwachten op een gebruikers-onferentie…). Ik vond het een heel zinvolle en leuke gebruikersbeurs!
Lees hier de terugblik van de organisatoren.

FME-Idols op YouTube

De komende paar dagen (30 november t/m 2 december) ben ik voor mijn werk op de FME User Meeting (FME Anwendertreffen) in Münster (die van de vrede in 1648).
Ik heb als ontwikkelaar meegewerkt aan een presentatie (als ik het goed heb gedaan hoef ik mijn mond niet open te doen) met als onderwerp “Improving quality of Urban Development Design and Budgetting by using FME” (het verbeteren van de kwaliteit van plantekeningen voor stedelijke ontwikkeling), gedaan met Wibold Jongsma van het Ontwikkelingsbedrijf van de Gemeente Amsterdam.
Mocht er gelegenheid en aanleding zijn dan laat ik iets van me lezen.

In het weekend van 2/3 februari was het goed mis met de server die mijn website host; om de haverklap was de site offline of zelfs de hele server niet bereikbaar.
‘s Zondags in de avond werd dan ook aangeboden om de websites te verhuizen naar een nieuwe server en uiteraard greep ik deze mogelijkheid met beide handen aan.
Op de oude server draaide de site op PHP 5 en Mysql 4.1; op de nieuwe server ook PHP 5, maar met MySQL versie 5.0.15. Leuk, er zitten een hoop nieuwe features in MySQL 5 die ik zeker wil gaan uitproberen!

Alles draaide, behalve de pagina’s van mijn weeroverzicht (zelfgebouwd, net als de overige delen, behalve dan de weblog natuurlijk). De queries zagen er goed uit, dus ik besloot er maar eens eentje te echoën naar het scherm en in PHPMyAdmin eens uit te voeren. Niks, Naks, nada, noppes . Gevonden dus!
Op die pagina’s kun je voor een bepaald jaar een maand aanklikken waarvoor dan per dag de gemiddelde waarden van het weer worden getoond. De parameters zijn dus jaar en maand. Om in MySQL de bijbehorende gegevens op te halen vergeleek ik de maand-parameter met de maand-component van de weergegevens. Om geen gedoe te hebben met hoofd- en kleine letters maak ik ze in de vergelijking allebei het zelfde; dus lower(maand-parameter) = lower (maand weergegevens). Dit werkt uitstekend in MySQL 4, maar niet meer in 5. Een bug dus (en officieel erkend), maar wel flauw.

select lower(monthname(now()))

Ik zou verwachten dat hier february uit zou komen, het resultaat is echter February (dus met een hoofdletter, ondanks de lower).
De upper-functie doet het ook niet zoals verwacht;

select upper(dayname(now()))

geeft als resultaat Thursday in plaats van de verwachte THURSDAY.

SELECT upper('Louis')

geeft ook keurig LOUIS als resultaat, alleen bij de datumvelden gaat het dus niet goed.

Ik heb mijn scripts aangepast zodat ze met de bug kunnen leven, het is wel jammer.

Ik loop al weer even mee in het Oracle-wereldje als ontwikkelaar en dba, maar had het hele object-georienteerde ontwikkelen een beetje gemist. Laatst moest ik dan toch aan de bak; de dotNet-ontwikkelaar had een result-set (een tabel met waardes) nodig.
Wat hij bedoelde is: in de applicatie wordt een Oracle stored-function of –procedure aangeroepen die een resultaat-tabel teruggeeft.
Ik moest het tóch uitzoeken en heb meteen een voorbeeld gebouwd op het bekende SCOTT-schema, op de tabellen EMP
(employees) en DEPT (departments) (downloadlink). Een department heeft meerdere employees, een employee werkt voor één department (zie ook het begin van mijn post over rowlevel-security voor de inhoud en structuur van deze tabellen).

Het was mij in eerste instantie niet helemaal duidelijk wat er werd bedoeld met ‘result-set’. Ik heb daarom het volgende gebouwd:

1. Er wordt een pl/sql-tabel (ook wel collection genoemd) in de vorm van een table-type teruggegeven als out-parameter van een procedure.
2. Er wordt een ref cursor teruggegeven als return-waarde van een functie.

Omdat er eigenlijk meer mee te doen is heb ik ook een aantal voorbeelden gebouwd die voorzien in:

• Het opbouwen van een pl/sql-tabel in een eenvoudige loop
• Het opbouwen van een refcursur na wat aanpassingen op de oorspronkelijke data
• Het zoeken binnen een samengesteld object.

Er zijn de volgende database-objecten nodig:

• Een database table-type (wordt gebruikt in het voorbeeld met de refcursur)
• Een database-package waarin alle voorbeelden staan uitgewerkt
• Een eenvoudig tabelletje om een output in weg te schrijven

Het database table-type; eerst wordt een record-type aangemaakt waarna een table-type wordt aangemaakt op dit record.

create or replace type emp_dept_rec as object (empno       number
                                              ,ename       varchar2(10)
                                              ,job         varchar2(9)
                                              ,deptno      number
                                              ,dname       varchar2(14)
                                             );

create or replace type emp_dept_tab is table of emp_dept_rec;

Het test-tabelletje

create table log(naam varchar2(1024));

De database-package-header

CREATE OR REPLACE package pck_object_test as
/* versie 1.0 11012008                                                        */
/*******************************************************************************
  Naam        :   pck_object_test
  Auteur      :   Louis Ywema
  Datum       :   11-01-2008
  Doel        :   Package voor werken met collections
  Opmerkingen :

Wijzigingen
Datum       Naam           Beschrijving
----------  -------------  -----------------------------------------------------

*******************************************************************************/
--
-- declaratie gebruikte types
type empdept_rec is record (empno       emp.empno%type
                           ,ename       emp.ename%type
                           ,job         emp.job%type
                           ,deptno      dept.deptno%type
                           ,dname       dept.dname%type
                           );
type empdept_tab is table of empdept_rec index by pls_integer;
--
type t_refcursor is ref cursor;
--
-- declaratie public procedure & functie
/*******************************************************************************
  Naam        :   object_tab
  Doel        :   in: p_deptno, p_ename
                  out: p_return; datatype pl/sql-collection (pl/sql-table)
                  We gebruiken hier een pl/sql object-type
*******************************************************************************/
--
procedure object_tab (p_deptno  in dept.deptno%type
                     ,p_ename   in emp.ename%type
                     ,p_return  out pck_object_test.empdept_tab
                     );
--
/*******************************************************************************
  Naam        :   get_refcursur
  Doel        :   in: p_deptno, p_ename
                  return: ref cursur
                  We gebruiken hier een database object-type
*******************************************************************************/
--
function get_refcursur (p_deptno dept.deptno%type
                        ,p_ename emp.ename%type)
return pck_object_test.t_refcursor;
--
end pck_object_test;
/

De database-package-body
Hierin staan de volgende procedures en functies:

• procedure object_tab p_deptno en p_ename als in-parameters; de pl/sql-tabel empdept_tab als out-parameter.
  In deze procedure wordt nog een query gedaan in de pl/sql-tabel.
• function get_refcursur p_deptno en p_ename als in-parameters; ref cursor t_refcursor als return-waarde.
  In deze function wordt gebruikgemaakt van het database-object-type empdept_tab en wordt de ref cursur pas in tweede instantie gevuld.

CREATE OR REPLACE package body pck_object_test as
/* versie 1.0 11012008                                                        */
/*******************************************************************************
  Naam        :   pck_object_test
  Auteur      :   Louis Ywema
  Datum       :   11-01-2008
  Doel        :   Package voor werken met collections
  Opmerkingen :

Wijzigingen
Datum       Naam           Beschrijving
----------  -------------  -----------------------------------------------------

*******************************************************************************/
procedure object_tab (p_deptno  in dept.deptno%type
                     ,p_ename   in emp.ename%type
                     ,p_return  out pck_object_test.empdept_tab
                     )
is
/*******************************************************************************
  Naam        :   object_tab
  Doel        :   in: p_deptno, p_ename
                  out: p_return; datatype pl/sql-collection (pl/sql-table)
                  We gebruiken hier een pl/sql object-type
*******************************************************************************/
--
cursor c_empdept (b_deptno dept.deptno%type)
is
   select emp.empno
   ,      emp.ename
   ,      emp.job
   ,      dept.deptno
   ,      dept.dname
   from   emp
   ,      dept
   where  emp.deptno  = dept.deptno
     and  dept.deptno = b_deptno
   ;
--
   l_empdept_tab pck_object_test.empdept_tab;
   l_row         number;
--
begin
   -- vullen pl/sql-collection
   for r_empdept in c_empdept(p_deptno) loop
      l_row := nvl (l_empdept_tab.last, 0) + 1;
      l_empdept_tab(l_row) := r_empdept;
   end loop;
   --
   -- alle records in de pl/sql-collection waarvoor geldt dat ename=p_ename
   --   worden weggeschreven naar een externe tabel
   for i in 1..l_empdept_tab.last loop
      if l_empdept_tab(i).ename = p_ename then
         insert into log (naam)
         values (l_empdept_tab(i).ename||'-'||l_empdept_tab(i).empno);
         commit;
      end if;
   end loop;
   -- zetten out-parameter
   p_return :=l_empdept_tab;
end object_tab;
--
function get_refcursur (p_deptno dept.deptno%type
                        ,p_ename emp.ename%type)
return pck_object_test.t_refcursor
is
/*******************************************************************************
  Naam        :   get_refcursur
  Doel        :   in: p_deptno, p_ename
                  return: ref cursur
                  We gebruiken hier een database object-type
*******************************************************************************/
--
   l_emp_dept_tab         emp_dept_tab:= emp_dept_tab();
   l_empdept              pck_object_test.t_refcursor;
   l_row                  number;
   l_reverse_dept         dept.dname%type;
   l_empno                emp.empno%type;
   l_deptno               dept.deptno%type;
begin
   for i in
      (select emp.empno
      ,      emp.ename
      ,      emp.job
      ,      dept.deptno
      ,      dept.dname
      from   emp
      ,      dept
      where  emp.deptno  = dept.deptno
        and  dept.deptno = p_deptno
      )
   loop
      -- doe iets met de data (de naam van het department wordt omgedraaid)
      select reverse(i.dname)
      into l_reverse_dept
      from dual;

      l_row := nvl (l_emp_dept_tab.last, 0) + 1;
      l_emp_dept_tab.extend;
      l_emp_dept_tab(l_row):= emp_dept_rec(i.empno
                                          ,i.ename
                                          ,i.job
                                          ,i.deptno
                                          ,l_reverse_dept
                                          );
   end loop;
   --
   -- alle records in het database-objecttype waarvoor geldt dat ename=p_ename
   --   worden weggeschreven naar een externe tabel
   begin
      select empno, deptno
      into l_empno, l_deptno
      from
      table ( select cast(l_emp_dept_tab as emp_dept_tab)
              from dual
            )
      where ename = p_ename;
      --
      insert into log (naam)
      values (l_empno||'-'||l_deptno);
      commit;
   exception
      when no_data_found then
        insert into log (naam)
        values ('no data');
        commit;
   end;
   --
   -- samenstellen ref cursur met de inhoud van het database-objecttype
   open l_empdept for
      select empno
      ,      ename
      ,      job
      ,      deptno
      ,      dname
      from   table(cast(l_emp_dept_tab as emp_dept_tab))
   ;
   -- teruggeven ref cursur
   return l_empdept;
end get_refcursur;
--
end pck_object_test;
/

Resultaten
Nadat de package is gecompileerd geef ik voorbeelden waarmee de stored-functions en procedure met SQL*Plus worden aangeroepen en de geretourneerde data tonen mbv DBMS_OUTPUT.

procedure object_tab, hier wordt de inhoud van het pl/sql-type getoond. We legen eerst de tabel LOG

truncate table log;
declare
l_empdept pck_object_test.empdept_tab; --> definitie van een local variable
                                       --   met als datatype die van het table-object
--
begin
   pck_object_test.object_tab(p_deptno  => 20
                             ,p_ename   => 'ADAMS'
                             ,p_return  => l_empdept
                             );
   --> pck_object_test.object_tab is een stored procedure
   -- in local variabele l_empdept is nu de gehele resultset opgehaald
   -- onderstaande code toont de inhoud voor deptno = 20
   for i in 1..l_empdept.count loop
      dbms_output.put_line ('empno :'  ||l_empdept(i).empno
                          ||' ename: ' ||l_empdept(i).ename
                          ||' job: '   ||l_empdept(i).job
                          ||' deptno: '||l_empdept(i).deptno
                          ||' dname: ' ||l_empdept(i).dname);

   end loop;
end;

Resultaat:
Table truncated.
empno :7369 ename: SMITH job: CLERK deptno: 20 dname: RESEARCH
empno :7566 ename: JONES job: MANAGER deptno: 20 dname: RESEARCH
empno :7788 ename: SCOTT job: ANALYST deptno: 20 dname: RESEARCH
empno :7876 ename: ADAMS job: CLERK deptno: 20 dname: RESEARCH
empno :7902 ename: FORD job: ANALYST deptno: 20 dname: RESEARCH
PL/SQL procedure successfully completed.

De inhoud van de tabel LOG is:
ADAMS-7876

functie get_refcursor, hier wordt de inhoud van de refcursur getoond. We legen eerst de tabel LOG

truncate table log;
--
set serveroutput on size 1000000
declare
   l_empno       number;
   l_ename       varchar2(10);
   l_job         varchar2(9);
   l_deptno      number;
   l_dname       varchar2(14);
   l_empdept     pck_object_test.t_refcursor;
begin
   l_empdept := pck_object_test.get_refcursur(p_deptno => 10
                                             ,p_ename => 'CLARK');
   loop
      fetch l_empdept into l_empno
                      ,    l_ename
                      ,    l_job
                      ,    l_deptno
                      ,    l_dname
                      ;
      exit when l_empdept%notfound;
      -- output what's in
      dbms_output.put_line ('empno:'||l_empno
                          ||' ename: '||l_ename
                          ||' job: '||l_job
                          ||' deptno: '||l_deptno
                          ||' dname: '||l_dname
                          );
   end loop;
end;

Resultaat:
Table truncated.
empno:7782 ename: CLARK job: MANAGER deptno: 10 dname: GNITNUOCCA
empno:7839 ename: KING job: PRESIDENT deptno: 10 dname: GNITNUOCCA
empno:7934 ename: MILLER job: CLERK deptno: 10 dname: GNITNUOCCA
PL/SQL procedure successfully completed.

De inhoud van de tabel LOG is:
7782-10

Ik hoop hiermee duidelijk gemaakt te hebben hoe mbv PL/SQL een data-object samengesteld kan worden en hoe deze data in een aanroepend programma ontsloten kan worden.
Voor .Net-ontwikkelaars: het artikel waarin beschreven wordt hoe de gegevens van een ref cursor gebruikt kunnen worden is te vinden in het Microsoft-artikel How To Use a DataReader Against an Oracle Stored Procedure in Visual Basic .NET

Ik dacht dat ik het vak van PL/SQL-programma’s schrijven nu toch wel aardig onder de knie had, maar het blijkt toch dat je nooit te oud bent om te leren.
Vorige week kreeg ik eindelijk de tweede uitgave van Steven Feuerstein’s boek met Best Practices thuisbezorgd (ik schreef eerder over hem) en ben meteen begonnen te lezen. Het boek(je, slechts 270 pagina’s en dat is voor Mister PL/SQL toch wel erg weinig, hij schrijft dat het voor hem een persoonlijke triomf is geworden om het zo kort te houden…) leest als een roman. Hij beschrijft de lotgevallen van een PL/SQL-team dat voor het bedrijf My Flimsy Excuse Inc. software schrijft waarbij het nodige misgaat. Het boekje bárst werkelijk van de goede tips en waar nodig wordt verwezen naar (ook gratis) software (zoals de Quest CodeGen Utility en de Quest Error Manager), die het schrijven van betere programma’s ondersteunt. Ik ga er zeker mee aan de gang!
Dit boek is een echte aanrader voor iedere Pl?SQl-ontwikkelaar en zijn/haar projectleider! En nee, je bent nooit te oud om te leren, het kan altijd beter!

Vandaag ben ik in de AmsterdamArena geweest bij de Quest Nederlandse Toad Gebruikersdag. Naast de kennismaking met de laatste versie van TOAD (wat overigens staat voor Tool for Oracle Application Developers, oorspronkelijk ontwikkeld door de Toadman, nu in het bezit van Quest), was dit een uitgelezen moment om de PL/SQL-evangelist Steven Feuerstein weer eens te horen spreken. Dus uitgedost in mijn TOAD t-shirt (natuurlijk niet, ik ben geen nerd) heb ik mij weer uitstekend vermaakt.  Na de aftrap door de sales-mannen van Quest kon je kiezen uit een track voor ontwikkelaars of een track voor beheerders. Samen met een collega werd het van allebei iets; eerst de Best Practices van Steven Feuerstein (ik krijg meteen zin om dingen weer eens anders, maar dan beter en gestructureerder aan te gaan pakken en de herziene uitgave van zijn Best Practices staat al op mijn verlanglijstje). Na de break (waaronder een groepsfoto met de kikkert van TOAD op het heilige gras van de Arena)  een presentatie door Thomas Klughardt over performance-onderzoek met Toad. Jammergenoeg kwam wat hij wilde vertellen niet helemaal uit de verf. Daarna werd de grote Oracle PL/SQL en SQL-quiz van AMIS gespeeld (heb ik eindelijk Oracle ACE Lucas Jellema  – maar dan zonder bril – eens in het echt gezien), maar ik viel net buiten de prijzen. Na afloop was er een borrel in de Koninklijke Loge met vooral veel lekkere snacks, ook niet te versmaden. Helemaal goed zo!

Sinds de installatie van WordPress 2.3 zijn er wat leuke mogelijkheden bijgekomen. Nieuw is de opsomming met tags aan de rechterkant in de zogenaamde sidebar. Aan ieder stukje ken je één of meer kenmerkende zoekwoorden (tags) mee. De software toont in een tag-cloud de voorkomende zoekwoorden, de grootst afgebeelde zijn het meest gebruikt. Wanneer je op zo’n woord klikt opent zich een pagina met de stukjes waarin die woorden zijn gebruikt.
Wat verder ook aardig is, is het tonen van widgets. Dat zijn stukjes programma die ook in de sidebar kunnen worden opgenomen waarin eigen programmeerwerk binnen de lay-out van WordPress kan worden getoond.
Laatst heb ik een eigen widget geschreven die bovenaan de sidebar een afbeelding toont die past bij het huidige seizoen. Deze widget is hier te downloaden voor de liefhebber. De installatie is eenvoudig; plaats de widget in de plugins-directory van WordPress (binnen de wp-content directory), activeer de plugin in de WordPress-adminapplicatie en plaats de widget op de gewenste plaats. Zie de code in de widget voor eventuele aanpassingen.