Indizierung für LIKE-Operationen in postgres

Ein interessanter Hinweis von Daniel Westermann im DBI Services Blog: durch die Verwendung der pg_trim extension ist es in postgres möglich, (GIN oder GiST) Indizes zur Unterstüzung von Like-Einschränkungen mit führendem (oder auch im Vergleichsstring enthaltenen) Platzhalter(n) zu erstellen. Laut Dokumentation gilt:
The pg_trgm module provides GiST and GIN index operator classes that allow you to create an index over a text column for the purpose of very fast similarity searches. These index types support (more...)

ORA-22926 when using getClobVal to convert XMLType to CLOB

I ran into a problem the other day when moving some code from one database to another (both XE 11g). The code in question needs to convert an XMLType to a CLOB to do some (hacky) string manipulation on it, and then turn it back to an XMLType.

Here's the original code:

  l_xml := apex_web_service.make_request(...);
 
  -- little hack to remove bad empty namespace from result
  l_clob := l_xml.getClobVal();
(more...)

About using APEX_COLLECTIONS in a tabular form with INSTEAD OF triggers

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.   In case you dreamed of updating an Apex tabular form built on top of a view based on APEX_COLLECTIONS by means of INSTEAD OF triggers, you must be ready to grant the following rights to the schema user attached to the workspace, otherwise you'll get an ORA-01031: Insufficient privileges

Perth – Free 1/2 day Education Event

Thanks to Sage Computing Services, AUSOUG are hosting a free 1/2 day education event for it's members.

Join us Wednesday 19th August, 2015 at the Duxton Hotel from 2:30pm to be regaled with tales of tuning, APEX, SQL and PL/SQL.

  • Penny - Practical Tuning Tips for Developers
  • Kylie - Snazzy SQL: Titbits to advance your code
  • Katie - PL/SQL: Are you making the most of it?
  • Scott - APEX 5 isn't just a sexy (more...)

Tabular Form – set default values with jQuery

tabular-form-default-jquery4

I have a simple tabular form with a numeric “sort order” column. I want the value of this column to be defaulted automatically, based on the maximum value of the rest of the records on the screen. Unfortunately the builtin Apex default type for columns in a tabular form can only be based on an Item, or a PL/SQL expression or function. I didn’t want to make a database call to get the maximum value (more...)

“Bob the Builder: Build/Deploy of #ADF enterprise applications” – I’m a speaker at #DOAG2015:

csm_2015-Konferenz-Ausstellung-Logo_02_373181671d

Today my presentation is confirmed:  “Bob the Builder: Build/Deploy of ADF enterprise applications”.

I will speak over our experiences with following

  • build tools
  • deployment tools
  • architecture
  • versioning & branching
  • continuous integration
  • hot deployment in development
  • compile & runtime dependencies

If you speak german and you are interested in some of this, you should come and attend DOAG 2015.


Filed under: Deploying, Development Tagged: 11g, 12c, ADF, ADF Architecture, Branching, (more...)

Schlechtere Performance durch semi_to_inner-Transformation in 12c

Jonathan Lewis zeigt in seinem aktuellen Artikel 12c Downgrade einen Fall, in dem der Optimizer in 12c einen deutlich weniger effizienten Plan auswählt als in 11.2.0.4, weil er die Query besser versteht und erkennt, dass darin eine Transformation eines Semi-Joins zu einem Inner-Join möglich ist, was grundsätzlich eine sinnvolle Strategie sein sollte. Allerdings profitiert der semi-join nested loop von der gleichen Optimierung, die auch für das Caching der Ergebnisse skalarer Subqueries verwendet (more...)

Object Type with Optional Attribute: Extra Constructor Function

When you have to create stored procedures which need to be called from an Oracle Service Bus, the most covenient way (at least for the one creating the mapping between the incoming message and the stored procedure) is to use Object Types.
The "downside" is that you might need lots of Object Types and Nested Table Types to get the right structure.
If you are unfamiliair with this technique, there are some links at the (more...)

Installing Oracle XE, ORDS and Apex on CentOS – Part Four: Stress testing

This is part four in a series of blog posts about how to install Oracle 11g Express Edition (XE) with Oracle Application Express (Apex) on a CentOS Linux server, with Apex served by Oracle REST Data Services (ORDS) running on top of Tomcat and Apache.

After setting up a small CentOS server with Oracle Express Edition (XE), I wanted to stress test it to see how much load such a barebones installation can handle. In (more...)

Performance-Probleme beim Zugriff auf DBA_FREE_SPACE

Das Phänomen ist offenbar relativ bekannt, war mir aber bisher nicht begegnet (oder in Erinnerung geblieben): ein Icinga-Test zur Bestimmung des Füllgrads eines Tablespaces erreichte zuletzt Laufzeiten von über einer Minute, was zu Timeouts und Icinga-Fehlern führte. Schnell zu bestimmen war, dass das eigentliche Problem im Zugriff auf DBA_FREE_SPACE lag. Aber warum reagierte diese Dictionary-View so träge? Zur Prüfung habe ich zunächst einen Blick in die Definition in DBA_VIEWS geworfen, aber den hätte ich mir (more...)

Apex CSV Import: “Data Loading Failed”

If you are using the Apex built-in Data Loading feature to allow your users to upload CSV files, you may have encountered this error. It’s not a particularly useful error, and the apex logs don’t seem to shed much light on the problem either – reporting only “ORA-01403: no data found” with backtrace ORA-06512: at "APEX_040200.WWV_FLOW_DATA_UPLOAD", line 4115 ORA-06512: at "APEX_040200.WWV_FLOW_PROCESS_NATIVE", line 213 ORA-06512: at "APEX_040200.WWV_FLOW_PROCESS_NATIVE", line 262 ORA-06512: at "APEX_040200.WWV_FLOW_PLUGIN", (more...)

Developer Cloud Service and Oracle ADF

ADF Spotlight sessions demonstrate features of Oracle ADF. Recently I delivered a session providing a DevOps solution in the cloud for Oracle ADF developers. This was broadcast live on June 30, 2015. You can view the full recording on YouTube here.

My Kscope15 sessions

My Kscope15 sessions are now available on my presentations page.

I linked to the Prezi version as they don't translate well to PDF, as those of you who tried downloading off the Kscope mobile app may have found.

The supporting code from my deep dive demos can be downloaded here:
http://sagecomputing.com.au/workshops/jquery_workshop_code.sql

Thank you to all those who attended, and cheers for all the great feedback, I sincerely appreciate it. I wish I (more...)

Podcast recommendation: SGU

Another item on my backlog is getting up to date on my favourite podcasts. I've got a post drafted that lists my recommendations, but if you're looking for a fun, well produced science podcast then go no further than The Skeptics Guide to the Universe.

They are generally 90 mins an episode and if you're not into podcasts already that may seem daunting. To me they fill my commute with fun but thought provoking stuff (more...)

Exploring dynamic pivot options

In looking for information on pivoting variable number of columns, I stumbled across a question I once managed to AskTom, many moons ago.
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7644594042547

Adrian Billington has an interesting lead into an XML solution with pivot, but would need more digging to finalise conversation of XML data for APEX to use.
http://www.oracle-developer.net/display.php?id=506

Then I found Tom's answer using easy to understand dynamic SQL (properly asserted, no less)
https://asktom. (more...)

Back to work, post Kscope15

After landing late Wednesday night I'm officially back to work on Monday morning, greeted with this:

It's good to be back. There is an APEX 5 instance sitting on 12c ready for app regression testing once it has data, but work before play - got a small backlog (more...)

Installing Oracle XE, ORDS and Apex on CentOS – Part Three: Additional configuration

This is part three in a series of blog posts about how to install Oracle 11g Express Edition (XE) with Oracle Application Express (Apex) on a CentOS Linux server, with Apex served by Oracle REST Data Services (ORDS) running on top of Tomcat and Apache.

By now, you should have a working installation of CentoOS with Oracle XE, Apex, ORDS, TomCat and Apache. There are still some additional configuration and optimization we should do, which (more...)

OS-Analyse für Logical I/O-Operationen

Fritz Hoogland, dessen Blog eine der besten Quellen zu I/O-Fragen in Oracle ist, beschäftigt sich in seinem aktuellen Artikel mit der Performance von Logical-I/O-Zugriffen. Ausgangspunkt ist dabei, dass der Kernel aktueller Redhat-Versionen die Möglichkeit bietet, via systemtap die Performance einzelner OS-Routinen zu überprüfen. Aufgrund entsprechender Aussagen des Oracle Supports und eigenen Beobachtungen kann der Autor Consistent Reads der C-Funktion kcbgtcr() zuordnen, während Current reads wohl durch kcbgcur() abgebildet sind. Aufbauend auf diesen Voraussetzungen werden (more...)

Remove punctuation from string using Javascript

I’m a morning person, and my mind is usually sharpest on Monday or Tuesday mornings, so these are the best times for me to work on fiddly javascript stuff. Today was one of those mornings and here are the results, just in case I want to refer back to them later on.

I had many items dotted around an Apex application where the user is allowed to enter “Codes” – values that must be uppercase (more...)

Code Studio rocks; diversity does, too

If you want to quickly get some kids introduced to computer programming concepts, you could do a lot worse than using Code Studiofrom code.org. That's what I did the last couple weeks - took two hours to lightly shepherd the Dayton YWCA day camp through a programming intro.

It's really well-organized and easy to understand - frankly, it pretty much drives itself. It's based on block-dragging for turtle graphics and/or simple 2D games, (more...)