Franck Pachot über SQL Plan Directives

In den letzten Wochen hat Franck Pachot eine Reihe interessanter Artikel zum Verhalten von SQL Plan Directives veröffentlicht, die ich an dieser Stelle grob vereinfachend zusammenfasse. Eine Einleitung zum Thema wollte ich mir eigentlich sparen, da ich etwas Derartiges hier bereits untergebracht hatte, aber der Herr Pachot hat es in Matching SQL Plan Directives and extended stats so schön zusammengefasst, dass ich mir seine Erklärung ausborge:
SQL Plan Directives in USABLE/MISSING_STATS state can create column (more...)

“Smart quotes” showing as “?” in emails

When some of my users were using my system to send emails, they’d often copy-and-paste their messages from their favourite word processor, but when my system sent the emails they’d have question marks dotted around, e.g.

“Why doesn’t this work?”

would get changed to

?Why doesn?t? this work??

Simple fix was to detect and replace those fancy-pants quote characters with the equivalent html entities, e.g.:

function enc_chars (m in varchar2) return  (more...)

APEX iFrame Security setting

At some point during APEX development you may find yourself putting an Oracle APEX page within an iFrame.
<iframe src="//myserver.com.au/ords/f?p=SAGE:1023:&SESSION."></iframe>

Just recently I did just that and came across an error I expected to see, but a little curious as to how it presented itself.

Refused to display '//myserver.com.au/ords/f?p=SAGE:1023:30559832045078' in a frame because it set 'X-Frame-Options' to 'DENY'.

Googling the last half of the message returns some interesting (more...)

A Greedy Algorithm using Recursive subquery factoring

Today is friday and I like the twitter-hashtag #FibonacciFriday,
so I tweeted

 

Don’t be afraid of having a look at the wikipedia-site, the math is not complicated at all ( you don’t need more than adding natural numbers smaller than hundred ), nevertheless the theorem is nice from a (more...)

Dom Brooks über Plan-Stabilität

Dom Brooks zählt zu den Blog-Autoren, die sich am intensivsten mit Fragen der Stabilität von SQL-Ausführungsplänen beschäftigt haben. In Strategies for Minimising SQL Execution Plan Instability liefert er einen recht umfassenden historischen Überblick zur Entwicklung des Optimizers und jener Ergänzungen, die eingeführt wurden, um seine Informationsgrundlagen und Entscheidungsmöglichkeiten zu erweitern. Dabei gilt natürlich, dass die größere Flexibilität und die Einführung adaptiver Strategien dazu führen, dass die Stabilität der Optimizer-Entscheidungen geringer wird. Die grundlegende Frage dabei (more...)

Bash Arrays & Oracle

Last week, I wrote about how to use bash arrays and the MySQL database to create unit and integration test scripts. While the MySQL example was nice for some users, there were some others who wanted me to show how to write bash shell scripts for Oracle unit and integration testing. That’s what this blog post does.

If you don’t know much about bash shell, you should start with the prior post to learn about (more...)

Kscope15 Presentations of Interest

Kscope15 is insanely close and I'm very excited to be making the journey this year.

A few months ago I made a list of presentations that piqued my interest to try encourage a business colleague to come along. Maybe this list will encourage someone to join what I understand to be the world's best conference for APEX developers.

The APEX topics listed in full here
http://kscope15.com/component/seminar/seminarslist?Itemid=57&topicsid=2
The links below go directly to the (more...)

APEX 5 alternative to gReport.data.view(‘DETAIL’)

I'm sure we've all done it at some point or another, used some "feature" in the database you're probably not meant to.

Interactive reports still have a few misgivings prior to APEX 5 so some tried to utilise gReport object, since it was available in the generated page but undocumented - which in Oracle speak means that they're more than entitled to change behaviour in future without telling or supporting you.

I understand htmldb_Get put (more...)

postgres Ressourcen

Mit den Links zu den - in der Regel sehr lesenswerten - Artikeln des dbi-services Blog habe ich manchmal nicht allzu viel Glück, weil sie bisweilen statt beim gewünschten Artikel auf der Latest Entry Seite ankommen (was laut Franck Pachot ein Joomla-Problem sein könnte - und demnächst erledigt sein sollte, da ein Umzug nach wordpress geplant ist). Trotzdem versuche ich es ein weiteres Mal mit einer Verlinkung: diesmal auf den Artikel "What will be coming (more...)

Better Data Modeling: 7 Differentiating Characteristics of Data Vault 2.0

Hard to believe that the 2nd Annual World Wide Data Vault Consortium (WWDVC15) is NEXT WEEK in beautiful Stowe Vermont. It promises to be an excellent event. The speakers include myself, Claudia Imhoff, Dan Linstedt (the inventor of Data Vault), Scott Ambler, Roelant Vos, Dirk Lerner and many more. The focus will be DV 2.0, […]

SQL Plan Baseline Manual Evolution

I’ve never really spent time on evolution because most of the time I use baselines for emergency sql plan management and not with the added value of controlled evolution with which the feature was conceived.

But some observations on sql plan baseline evolution originating from the questions:

  • What does evolution actually do?
  • What information does it use?
  • What happens with DML?

Starting point – one baselined plan

1. FTS plan in memory from SQL which (more...)

Upsert in postgres

In meiner universitären Vergangenheit hätte ich es vielleicht "ein Desiderat der Forschung" genannt: ein Kommando, mit dessen Hilfe sich in postgres ein INSERT oder UPDATE in Abhängigkeit davon durchführen lässt, ob ein gegebener Schlüssel bereits in der Zieltabelle vorliegt. Diese in anderen Datenbanken als MERGE oder UPSERT bezeichnete Funktionalität ist seit vielen Jahren die wahrscheinlich größte Lücke im ansonsten sehr mächtigen SQL-Dialekt von postgres. Mit Release 9.5 wird diese Lücke nun offenbar endlich geschlossen (more...)

Bash Arrays & MySQL

Student questions are always interesting! They get me to think and to write. The question this time is: “How do I write a Bash Shell script to process multiple MySQL script files?” This post builds the following model (courtesy of Download MySQL Workbench) by using a bash shell script and MySQL script files, but there’s a disclaimer on this post. It shows both insecure and secure approaches and you should avoid the insecure (more...)

With PL/SQL and LONGs (and PRODUCT_USER_PROFILE)

One use for the 12.1.0.2 addition of PL/SQL functions in the WITH clause is to get the HIGH_VALUE of a partition in a usable column format.

with
 FUNCTION char2000(i_tab in varchar2, i_part in varchar2) 
 RETURN VARCHAR2 IS
   v_char varchar2(2000);
 BEGIN
   select high_value into v_char
   from user_tab_partitions a
   where a.table_name = i_tab
   and a.partition_name = i_part;
   --
   if v_char like 
     'TO_DATE(''%'', (more...)

Add Gedit Plugins

Fedora comes with vim and gedit installed but the gedit installation is bare bones. You can update gedit to include supplemental Plug-ins with the following yum command as the root user:

yum install -y gedit-plugins

It generates the following log file:

Loaded plugins: langpacks, refresh-packagekit
mysql-connectors-community                                  | 2.5 kB  00:00     
mysql-tools-community                                       | 2.5 kB  00:00     
mysql56-community                                           | 2.5 kB  00:00     
pgdg93                                                      | 3.6 kB  00:00     
updates/20/x86_64/metalink                                  |  14 kB  00:00     
updates                                                      (more...)

AUSOUG: Perth Seminar May 28

Attention Perth Oracle technologists.

I'll be giving a dry run of my Kscope talk to the user group in Perth on May 28th, which happens to be the deadline for us to submit copies of our sessions to ODTUG.

So if you're in Perth, have any form of interest in APEX - come by the Oracle offices in near Kings Park by 8am, or come a bit earlier for a feed.

Evidence-Based APEX: Building Better Practices

Register here
http://www.ausoug.org.au/cms/rest/event/2125

Oracle Developer Cloud Service Integration with GitHub

Want to leverage and existing GitHub repository within a project in Oracle Developer Cloud Service? It's easy. Check out this short video to see how it's done.

Finally Some Oracle Forms Answers! Open Q&A with Michael Ferrante Oracle Forms PM

  • When is Forms 12c coming out?
  • Is Forms dead?
  • Will Forms ever run without a Java applet?
  • Will we be able to run Forms on Chrome?
  • How can I deploy my form to mobile devices?

The list of pressing questions about Oracle Forms seems endless, but we will finally get you some answers!

ODTUG and the Oracle Forms community have coordinated a free Q&A webinar hosted by Michael Ferrante, the Oracle Forms project manager himself. He (more...)

Degenerierte Indizes

Seit ich begonnen habe, mich mit Datenbanken zu beschäftigen, lautet eine der populärsten Fragen in Oracle-Foren: mit welcher Häufigkeit soll ich meine B*Tree-Indizes neu aufbauen lassen? Zu behaupten darüber hätten Kriege stattgefunden, wäre vielleicht ein wenig übertrieben - aber heftige Auseinandersetzungen waren es allemal. Inzwischen ist dieser Konflikt weitgehend zur Ruhe gekommen, was vermutlich damit zusammen hängt, dass die Antwort eigentlich unstrittig ist: Oracles Implementierung von Indizes macht Rebuilds weitgehend unnötig - sie sind nur (more...)

Eclipse on Fedora

While I previously blogged about installing Netbeans 8, some of my students would prefer to use the Eclipse IDE. This post shows how to install and configure Eclipse IDE, include the mysql-connector-java.jar, and write Java to access the MySQL.

You can download Eclipse IDE and then open it in Fedora’s Archive Manager. You can use the Archive Manager to Extract the Eclipse IDE to a directory of your choice. I opted to extract (more...)