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

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, ]')

Oracle 10 XE install on OpenSuse 11.3

xxodd:/home/knilluz/Downloads # rpm -Uvh oracle-xe-10.2.0.1-1.0.i386.rpm
Preparing...                ########################################### [100%]
   1:oracle-xe              ########################################### [100%]
Executing Post-install steps...
insserv: warning: script 'oracle-xe' missing LSB tags and overrides
insserv: Default-Start undefined, assuming default start runlevel(s) for script `oracle-xe'
oracle-xe                 0:off  1:off  2:off  3:on   4:off  5:on   6:off
You must run '/etc/init.d/oracle-xe configure' as the root user to
configure the database.

The warning about the LSB is a worrying one. It seem that LSB the (Linux Standard Base) implementation in the newest version (11.3) of OpenSuse is more strict.

I found the solution in the opensuse forum .

[…] as of this new opensuse release, LSB implementation is stricter and thus does not run oracle’s post install script.
Particularly, the problem is with oracle password.
The simplest work around i found is
0- su % to login as root
1- install the RPM
2- su
3- passwd oracle % to change oracle password, choose any pwd you’d like
4- exit % return as root
5- /etc/init.d/oracle-xe configure % to configure xe db

[…]

So I uninstalled  and then reinstalled Oracle-Xe and then Done this:

xxodd:/home/knilluz/Downloads # passwd oracle
Changing password for oracle.
New Password:
Bad password: too simple
Reenter New Password:
Password changed.
xxodd:/home/knilluz/Downloads # cd /etc/init.d
xxodd:/etc/init.d # ./oracle-xe configure
Oracle Database 10g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 10g Express
Edition.  The following questions will determine whether the database should
be starting upon system boot, the ports it will use, and the passwords that
will be used for database accounts.  Press <Enter> to accept the defaults.
Ctrl-C will abort.
Specify the HTTP port that will be used for Oracle Application Express [8080]:
Specify a port that will be used for the database listener [1521]:
Specify a password to be used for database accounts.  Note that the same
password will be used for SYS and SYSTEM.  Oracle recommends the use of
different passwords for each database account.  This can be done after
initial configuration:
Confirm the password:
Do you want Oracle Database 10g Express Edition to be started on boot (y/n) [y]:y
Starting Oracle Net Listener...Done
Configuring Database...Done
Starting Oracle Database 10g Express Edition Instance...Done
Installation Completed Successfully.
To access the Database Home Page go to "http://127.0.0.1:8080/apex"
xxodd:/etc/init.d #

and it works.

oracle apex met fop

En zo was daar weer een verzoek:

Zou jij misschien voor pdf genereren de apache FOP server kunnen installeren.

Zie hiervoor deze link:
De eerste link waar het mee begon: http://carlback.blogspot.com/2007/03/apex-cocoon-pdf-and-more.html

Uiteraard ontstond er een groot vraagteken boven mij, want ik ga uiteraard niet zomaar op een server iets installeren. Want stel je voor zeg. 🙂 Heb maar wat tekst en uitleg gevraagd wat de bedoeling precies was. Het blijkt dus dat als je pdf rapportjes wil genereren vanuit APEX (oracle), dat je dan 3 methoden kunt gebruiken. Twee daarvan zijn commerciele (en dus schreeuwend dure) producten en en derde is de methode middel Apache objecten, aangestuurd via java.

Ik zal vanavond maar eens (of nee morgenavond vandaag  ben ik jarig) dit gaan uitzoeken. Alvast wat linkjes bij elkaargescharreld: APEX + Cocoon = PDF (and more). Waarbij ik volgens mij het volgende moet uitvoeren op onze Ubuntu server
Installing Cocoon on Ubuntu , maar dan wel eerst dit doen (maar dat werkt ook niet in 1 keer)….. ik heb daarvoor maar “deb http://archive.canonical.com/ lucid partner” toegvoegd aan /etc/apt/sources.list.d/java.list:

sudo echo “deb http://archive.canonical.com/ lucid partner” > /etc/apt/sources.list.d/java.list

sudo apt-get update

sudo apt-get install sun-java6-jdk

maven download en install (klik)

wget http://apache.mirror.versatel.nl/maven/binaries/apache-maven-2.2.1-bin.tar.bz2 (in de home dir)

tar -jxvf apache-maven-2.2.1-bin.tar.bz2

Nu is er in de home dir een nieuwe dir aangemaakt apache-maven-2.2.1

copieer deze in zijn geheel naar /usr/local/

sudo mv apache-maven-2.2.1 /usr/local/apache-maven

Vervolgens eentje om niet te vergeten (enviroment variabelen zetten op boot tijd). Dit is vooral belangrijk als het JanHagel (in de zin van canaille) de server weer gaat rebooten. Het gaat hierbij om de M2_HOME=/usr/local/apache-maven/apache-maven-2.2.1 en de M2=$M2_HOME/bin en daarna dit aan het pad toevoegen PATH=$M2:$PATH. (dat van die bin dat mist in het installatiedocument, die had ik zelf maar even erbij verzonnen).

export  M2_HOME=/usr/local/apache-maven/apache-maven-2.2.1

export M2=$M2_HOME/bin

export PATH=$M2:$PATH

Vervolgens heb ik stap 3 van dit document uitgevoerd. Daar wordt gesproken over iets in ~/cocoon$ maar dat moet zijn (tenminste op onze servert) ~/cocoon/cocoon/$

En niet vergeten. Datapump backup scripten.

Oracle database recovery error alter database open;

knilluz@d6p:~$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 – Production on Wed Jul 14 17:32:08 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 587202560 bytes
Fixed Size 1260196 bytes
Variable Size 171967836 bytes
Database Buffers 411041792 bytes
Redo Buffers 2932736 bytes
SQL> alter database mount;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01221: data file 1 is not the same file to a background process

SQL>

Ubuntu Server 10.04

Vanavond ‘even’ een Linux servertje voor het werk geinstalleer. Was at easy. Ubuntu Server 10.04. Cd’tje erin paar vragen beantwoorden. Klaar. Toen ff Oracle 10G XE geinstalleerd. Daarvoor moest eerst even een extra package worden geinstalleerd, maar dat was met: sudo apt-get install libaio1 ook snel klaar. Toen sudo dpkg -i oracle-xe_10.2.0.1-1.0_i386.deb gedaan. Daarna nog even /etc/init.d/oracle-xe configure. Waarbij de antwoorden achtereenvolgens waren: http port: 8080, listen 1521 en een wachtwoord. Bij wijze van spreken zou ik nu voetbal kunnen gaan kijken 🙂

Wat ik nu wil is als system of als sys: execute DBMS_XDB.SETLISTENERLOCALACCESS(TRUE) . En nu begon de ellende.

Verkeerd geinstalleerd (oooh had ik maar suse geinstalleerd).

Toch niet helemaal ok. dat sudo dpkg -i …… is niet helemaal goed als ik dit lees . En dit is ook een goeie lezert voor installatie onder Ubuntu.

Ik ga nu deze proberen

Het gaat in ieder geval om deze shitload:

Enter user-name: SYSTEM
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory

Vanavond maar es voor root een directe inlognaam maken en niet meer via su of sudo. Dat gaat altijd mis.