Toon de foreign-key’s en de betrokken wederzijdse tabellen, kolommen en constraints

SELECT LOWER(cons1.constraint_name) constraint_name
,      LOWER(cons1.table_name||'.'||conc1.column_name) tab_column
--,      conc1.position
,      LOWER(cons2.table_name||'.'||conc2.column_name||'   ('||cons2.constraint_name||')')  r_tab_column
--,      conc2.position
FROM   all_constraints  cons1
 JOIN  all_cons_columns conc1
   ON  cons1.constraint_name = conc1.constraint_name
  AND  cons1.owner           = conc1.owner
 JOIN  all_constraints  cons2
   ON  cons2.constraint_name = cons1.r_constraint_name
  AND  cons2.owner           = cons1.owner
 JOIN  all_cons_columns conc2
   ON  conc2.constraint_name = cons2.constraint_name
  AND  conc2.position        = conc1.position
  AND  conc2.owner           = cons2.owner
WHERE  cons1.owner           = '<OWNER>'
AND    cons1.constraint_type = 'R'
ORDER  BY cons1.table_name
,         conc1.position

Toon tabellen en kolommen met hun pk’s en fk’s uit een schema.

Ok ik zie dat de code hier en daar wat netter kan. Maar ‘t doet ‘t wel, voor als je wil weten hoe het hele referentiele plan is opgesteld in je schema. (Zie ook de vervolg post).

SELECT TAC.table_name
,      TAC.column_name
,      tac.data_type||' ('||CASE WHEN data_precision IS NOT NULL
                              THEN    data_precision|| CASE WHEN data_scale > 0
                                                         THEN ',' || data_scale
                                                         ELSE NULL
                                                       END
                              ELSE TO_CHAR (data_length)
                           END || ')' datatype
,      CASE WHEN tac.nullable='N'
         THEN 'NOT NULL'
       END nullability
,      CASE WHEN pk.position IS NOT NULL
         THEN '(PK)'
       END pk
,      CASE WHEN fk.constraint_name IS NOT NULL
         THEN '(FK)'
       END fk
FROM   all_tab_columns tac
LEFT OUTER JOIN (SELECT  cons.constraint_name
                 ,       cons.table_name
                 ,       conc.column_name
                 ,       conc.position
                 FROM    all_constraints cons
                   LEFT OUTER JOIN all_cons_columns conc
                     ON  cons.constraint_name = conc.constraint_name
                    AND  cons.owner = conc.owner
                   WHERE cons.owner = '<OWNER>'
                   AND   constraint_type = 'P') pk
  ON pk.column_name = tac.column_name
 AND pk.table_name = tac.table_name
LEFT OUTER JOIN (SELECT  cons.constraint_name
                 ,       cons.table_name
                 ,       conc.column_name
                 ,       conc.position
                 --,      cons.*
                 FROM    all_constraints cons
                   LEFT OUTER JOIN all_cons_columns conc
                     ON  cons.constraint_name = conc.constraint_name
                    AND  cons.owner = conc.owner
                   WHERE cons.owner = '<OWNER>'
                     AND constraint_type = 'R') fk
  ON fk.column_name = tac.column_name
 AND fk.table_name  = tac.table_name
WHERE  tac.owner   = '<OWNER>'
ORDER BY  tac.table_name
        , pk.position
        , tac.column_name

relatief absolute padnamen

Gister heb ik de home dir’s verplaatst van /home naar /home2/users. Dit omdat de partitie waar /home op stond nog geformateerd was met reiserfs (al meer dan 6 jaar te zien aan de timestamps van bestanden). Reiserfs was ‘vroeger’ het standaard filesysteem van suse linux. Maar tegenwoordig is dat ext4. Reiserfs is ook een beetje dubieus omdat de ontwikkelaar in de gevangenis zit vanwege moord op zijn vrouw. Er wordt dus ook niet meer aan doorontwikkeld. Reiserfs versie 3 staat wel te boek als heel stabiel. Maar volgens de geleerden is ext4 even snel dan wel sneller. Dus ik ga formateren.

Maar daar gaat het niet om. Wat me opviel is hoeveel programma’s gebruik maken van absolute padnamen. Amarok bijvoorbeeld knalde er gelijk in met een foutmelding dat /home/knilluz/mp3 niet bestond. Nee klopt dat is nu /home2/users/knilluz/mp3. Als Amarok zo slim was geweest om ~/mp3 in de padverwijzing naar mijn muziek te plaatsen. Dan was alles goed gegaan.

Gebruik dus waar mogelijk een relatief pad (~/mp3) en niet absoluut (/home/knilluz).

dolphin, dbus en opensuse 11.3

Zo nu en dan is mijn Dolphin zo traag als een dikke poep in een trechter. Dan doe ik klik en dan slaat het vast om 20 seconden later weer response te geven. Niet handig. volgens opensuse fora blijkt dit te liggen aan de dbus versie welke wordt geleverd met opensuse 11.3. Volgens de geleerden zou ik moeten overstappen op versie 1.3.
Vanavond maar es dit gaan proberen. [link]

Oracle external table (met utf-8 coded file)

Om snel een csv bestand te koppelen een Oracle database, gebruik ik external tables. Hier een quick note hoe dit aan te maken:

Bepaal op de server een directory waar de te koppelen bestanden terecht gaan komen. In mijn geval is dat /var/oracle/import01. Zorg dat deze dir voldoende lees/schrijf rechten heeft. Alles wordt uitgevoerd als oracle:dba op mijn systeem. (OpenSuse 11.3 met Oracle 10XE).

Log nu in als iemand die rechten heeft om Oracle directory’s aan te mogen maken. En maak de directory en grant deze aan diegene die het schrijven en lezen gaat uitvoeren (knilluz in mijn geval dus).

create or replace directory import01 as '/var/oracle/import01';
grant read, write on directory import01 to knilluz;

Plaats nu het bestand in de dir (/var/oracle/import01/ext_tab_test.txt in mijn geval).
En creëer de tabel:

create table ext_table_test
( voornaam       varchar2(255)
, achternaam     varchar2(255)
, voorvoegsels   varchar2(255)
, geboortedatum  varchar2(255)
, straat         varchar2(255)
, huisnummer     varchar2(255)
, postcode       varchar2(255)
, plaats         varchar2(255)
)
organization external
( default directory import01
access parameters
 ( records delimited by newline
 characterset 'UTF8'
 fields terminated by ';'
 missing field values are null
 )
 location ('ext_tab_test.txt')
 );

Let er goed op dat de layout van het bestand de volgorde heeft van de volgorde van de kolommen in het create table gedeelte. Omdat ik lui ben neem ik voor het creeeren van de velden ‘onzinnig’ grote datatypes allen van varchar2(255). In het verwerkingsproces ga ik hier wel string functies op los laten. Omdat het bestand in UTF8 is gecodeerd, heb ik dit als characterset clausule opgenomen. Omdat anders lettertekens malle dingen gaan doen met namen als Nedžija, Henriëtte of José

Zo zit in dit bestand alleen maar de voornamen van de personen. En daar zitten juwelen tussen Hermina Dirkina Maria Barbara Geertruida Bernardina  bijvoorbeeld. Hier wil ik echter alleen voorletters van hebben (H.D.M.B.G.B.). Daar had ik het volgende stukje sql voor geschreven (met uitleg, want :

— voorletters van voornamen maken (voorletters is van datatype varchar2(10))
— 1. vervang alle niet hoofdletters door ‘niks’ [REGEXP_REPLACE(voornamen,'([^A-Z])’,”)] (Hermina Dirkina Maria Barbara Geertruida Bernardina wordt dan (HDMBGB)
— 2. neem alleen de eerste 5 voorletters [substr(‘[result stap 1]’,1,5)] (HDMBGB wordt dan HDMBG)
— 3. vervang iedere occurrence van de huidige string (.) door zichzelf met een puntje erachter [REGEXP_REPLACE(‘result stap 2′,'(.)’,’\1.’)]. (HDMBG wordt nu H.D.M.B.G.

REGEXP_REPLACE(SUBSTR(REGEXP_REPLACE(voornamen,'([^A-Z])',''),1,5),'(.)','\1.') voorletters

Oh en nog een laatste vingeroefeningetje. Zoek alle voornamen waar malle tekens in voorkomen (dus sluit die namen uit waar alleen maar a-z’en,  A-Z’en en spaties in voorkomen.

SELECT * FROM table WHERE REGEXP_LIKE(voornamen,'[^a-z,A-Z, ]')