Weblog for the AMIS Technology corner
Devel. + PL/SQL tools
Looking back at ODTUG Kaleidoscope 2010
Jul 5th
In case you missed it (how is that possible?) last week was the annual ODTUG conference, Kaleidoscope in Washington DC. In this post I will share some of, what I consider, the highlights of this event.
I always enjoy going to this conference, it’s nice to see old friends and make some new ones. Read the rest of this entry »
Parsing a Microsoft Word docx, and unzip zipfiles, with PL/SQL
Jun 9th
Some days ago a collegue of mine asked if I could made something for him to unzip a Microsoft Word 2007 docx file. And of course in the database and without using Java. Read the rest of this entry »
ODTUG Preview: mini conferentie (APEX, Database en SOA)
May 18th
Voor het vierde achtereenvolgende jaar organiseert AMIS de “ODTUG Preview” sessie op 8 juni 2010 bij AMIS in Nieuwegein. ODTUG Kaleidoscope 2010 is een conferentie voor Oracle ontwikkelaars, dit jaar vanaf 27 juni in Washington DC. Deze conferentie trekt van oudsher veel Nederlandse deelnemers, waaronder een flinke afvaardiging van AMIS. Tijdens de ODTUG Preview sessie laten Nederlandse presentatoren hun presentatie voor de conferentie in Washington horen aan een ‘thuispubliek’, als een soort try out of sneak preview. Voor iedereen die niet in gelegenheid is er in Washington bij te zijn, is dit een mooie gelegenheid om een aantal interessante presentaties van topsprekers bij te wonen.
De ODTUG preview omvat negen presentaties, in drie verschillende tracks:
- APEX en Oracle Development
- Oracle Database & Middleware
- SOA en Fusion Middleware
De sprekers en presentatie-titels:
| Tijd | APEX / Oracle Development | SOA en Fusion Middleware | Oracle Database en Middleware |
| 16:30 – 17:00 | Ontvangst | ||
| 17:00 – 18:00 | Iloon Ellen-Wolf APEX 4.0 |
 Marco Gralike XML Database – Design Concepts for XML Applications that will Perform! |
Toon Koppelaars Semantic Query Optimization |
| 18:00 – 19:00 | Diner | ||
| 19:00 – 20:00 | Alex Nuijten Analytic Functions Revisited | Peter Ebell OSB meets Database – and they lived happily ever after |  Lucas Jellema Edition-Based Redefinition for Developers and DBAs |
| 20:00 – 21:00 | Anjo Kolk Accessing the Oracle Database from Google (Apps, App Engine, Spreadsheets) |
Lonneke Dikmans Portals: The Way to Realize User Experience in a Service-Oriented Architecture? |
Jacco Landlust Comparing Oracle Application Server (OAS/IAS/OC4J) and WebLogic Server |
Wil je bij deze sessie aanwezig zijn, meld je dan aan via http://www.amis.nl/de-agenda?eventId=5&task=event_register&type=reg_individual.
Utl_compress, gzip and zlib
Mar 13th
Oracle has a a supplied package utl_compress, which can be used to compress and decompress data with PL/SQL. According to the documentation it uses the “Lempel-Ziv compression algorithm”, and “The output of the UTL_COMPRESS compressed data is compatible with gzip”. That means it’s following the RFC 1952 specs, RFC 1952. And that may be very useful (but I have never used it), but I need compression (I’m working on a PDF-generator in PL/SQL) and decompression (unwrapping wrapped PL/SQL!) in the zlib-format, RFC 1950. Both formats use the same algorithm, RFC 1951, but have different headers and trailers. So can utl_compress be used to for compressing/decompressing data according to the zlib-specs. Yes! Read the rest of this entry »
Uploading and viewing photo’s with your mobile APEX application
Feb 11th
How to upload photographs on you mobile phone with an Apex application? The question was asked to me this morning.
At our company we all drive a company car. To invoice the fuel of our foreign trips we have to invoice this at the leasing company. How easy would it be to declare it during your foreign trip. Just make a photo of the receipt and sent it directly to the leasing company with a mobile Apex application .
Here’s is the datamodel:
CREATE TABLE "CARS"
( "ID" NUMBER(2,0),
"CAR_REGISTRATION" VARCHAR2(10),
"DRIVER" VARCHAR2(20),
CONSTRAINT "CAR_CON" PRIMARY KEY ("ID") ENABLE
)
/
CREATE TABLE "DECLARATION"
( "ID" NUMBER(3,0),
"CAR_ID" NUMBER(3,0),
"MILEAGE" NUMBER(6,0),
"DATE" DATE,
"AMOUNT" NUMBER(5,2),
"PHOTO_RECEIPT" BLOB,
"MIMETYPE" VARCHAR2(255),
"FILENAME" VARCHAR2(255),
"IMAGE_LAST_UPDATE" DATE,
CONSTRAINT "DECLARATION_PK" PRIMARY KEY ("ID") ENABLE
)
/
ALTER TABLE "DECLARATION" ADD CONSTRAINT "DECLARATION_FK" FOREIGN KEY ("CAR_ID")
REFERENCES "CARS" ("ID") ENABLE
/
CREATE SEQUENCE "CAR_SEQ" MINVALUE 1 MAXVALUE 999
INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE
/
CREATE SEQUENCE "DECLARATION_SEQ" MINVALUE 1 MAXVALUE 999
INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE
/
CREATE OR REPLACE TRIGGER "BI_DECLARATION"
before insert on "DECLARATION"
for each row
begin
if :NEW."ID" is null then
select "DECLARATION_SEQ".nextval into :NEW."ID" from dual;
end if;
end;
/
ALTER TRIGGER "BI_DECLARATION" ENABLE
/
ADF 10g Dynamic Columns: Or how to implement an updatable dynamic table
Jan 11th
Although it is all about ADF 11g these days there are still some challenges in ADF 10g projects that run at some of my customers. Today I finished a task in which I had to create an updatable table in which the number of shown columns wasn’t known at design time. There is the possibility to create an ADF read only dynamic table, that works more or less like the richfaces columns <rich:columns/> http://livedemo.exadel.com/richfaces-demo/richfaces/columns.jsf?tab=usage&cid=3305454 element. However, I needed an updatable table, but ADF doesn’t know such a component. I had to come up with a different solution. Read the rest of this entry »
SOA Suite 11g – Oracle BPEL Master and Detail process coordination using signals
Dec 9th
A BPEL process can communicate with another BPEL process just like it can communicatie with any Web Service – as BPEL processes expose WebService interfaces to the world – or at least to their fellow components in the same Composite Application. When one process – the master in tis discussion – calls another one – it can have several types of interaction and dependency on that other process – we will call it the detail process for the purpose of this article:
- it is not interested at all in the detail process – its call was a one-way fire and forget
- it is interested in the response and it will wait for the response before it can continue processing (synchronous calls will always do this, asynchronous calls could have some activity going on while the detail process is churning away)
- it is interested in the fact that the detail process has reached a certain stage – but it does not actually need a response (it wants a signal but no data)
The Signal and ReceiveSignal activities are Oracle extensions to BPEL – that only work on the Oracle BPEL engine – that help us to implement the third scenario.
As part of the Invoke activity from a BPEL process to another process, we can specify that the called process should be considered a Detail process (and therefore the calling process as the Master process). When we have established this Master-Detail relationship, we can next create a Signal-ReceiveSignal connection between the two. These connections can be created in both directions: the Master sends a signal to the Detail (and the Detail waits to receive the signal) and vice versa the Detail process sends a signal that the Master is waiting for. Unfortunately, as we will see in this article, we cannot have multiple such interactions between a Master-and-Detail pair.
Typical use cases for the signal pattern are situations where a master process can only proceed when detail processes have completed or at least reached a certain state (the master process should only send the email to the customer when the detail process has handed the order to the shipping department) or when a master process calls a detail process to start processing and then needs to do some additional work before the detail process(es) can continue to their next step (master process asks detail to start gathering quotes from car rental companies, than continues to establish the credit-worthiness of the customer and when that has been taken care of indicates to the detail process that it may continue processing).
Note: there is nothing signal .and receiveSignal can do that we cannot also achieve using asynchronous, correlation driven calls. However, when we can achieve our goals using signaling, it is usually much easier to implement and lighter-weight to execute than the full blown correlation based solution.
Oracle 11gR2 – alternative for CONNECT_BY_ISLEAF function for Recursive Subquery Factoring (dedicated to Anton)
Nov 14th
On our blog, we have been discussing the new hierarchical query functionality in Oracle Database 11g Release 2, using Recursive Suquery Factoring. Instead of using CONNECT BY and its close associates such as START WITH, PRIOR, LEVEL and more exotic comrades like SYS_CONNECT_BY_PATH, CONNECT_BY_ROOT and NOCYCLE this release gave us a new, less proprietary and eventually probably more intuitive and functionally rich approach. We have also written how though we have no straightforward alternatives for LEVEL, SYS_CONNECT_BY_PATH and CONNECT_BY_ROOT – in the new recursive approach they are fantastically easy to emulate.
Until recently I have been quite happy with the new hierarchical querying and telling the world how I felt. Then an esteemed colleague – a far more experienced SQL programmer than I am – came up to me and remined me how the recursive sub query syntax at the present does not have a replacement for the CONNECT_BY_ISLEAF function – the SQL function that tells us whether a node produced in an hierarchical query has any children or is at the bottom of the chain – i.e. a leaf node. For leaf nodes (child-less), the function returns a value of one and for parent nodes the value is zero.
Anton (my colleague) was right and unfortunately I did not have a quick retort. However, after giving it some thought I believe I have found a way of emulating the CONNECT_BY_ISLEAF as well, using the new DEPTH FIRST ordering capabilities of the recursive subquery. I hope this will satisfy Anton as well.
Sneak Peek and what can come of it
Oct 26th
Last Tuesday we did a Preview of the upcoming OPP (Oracle PL/SQL Programming) Conference which will be held in Atlanta, Georgia soon.
This preview took place in our office in Nieuwegein, The Netherlands. About 17 people attended this free preview. At regular intervals we do free "Knowledge Center" presentations. Keep a close eye on our Agenda to see future events.
AMIS Query – Verslag van Oracle Open World 2009 – dinsdag 27 oktober (uitnodiging)
Oct 23rd
Vorige week was in San Francisco het hoogtepunt van het Oracle jaar: Oracle Open World 2009, de grootste IT conferentie ter wereld. Tijdens deze conferentie ontvouwde Oracle haar strategie en visie voor de komende periode, lieten product managers de nabije toekomst zien van bestaande en nieuwe producten, deelden honderden specialisten hun ervaringen en toonden leveranciers en Oracle engineers op de demo-grounds de nieuwste snufjes. Ca. 40.000 bezoekers waren aanwezig in het hart van San Francisco om zich vijf dagen lang te laten overvoeren met Oracle weetjes.
Als je er bij was vind je het misschien leuk nog eens herinneringen op te halen en ervaringen te bespreken. Als je er niet bij was ben je misschien geinteresseerd in de belangrijkste aankondigingen, de mooiste demo’s en wetenswaardigste feiten. Dat kan, aanstaande dinsdag op de AMIS Query – Verslag van Oracle Open World 2009. In deze (gratis) sessie doet de AMIS-delegatie naar OOW (Marco Gralike, Peter Ebell en Lucas Jellema) verslag van de conferentie. Je bent van harte welkom om daarbij aanwezig te zijn. Vanaf 17.30 serveren wij een diner, om 18.30 start de sessie die duurt tot pakweg 21.00 uur. Om je aan te melden, ga naar: registreren AMIS Query OOW 2009.
In deze sessie komen onder andere de volgende onderwerpen aan bod:
