Database
Oracle database
Re-ordering without procedural code or using a sequence
5Question from one of my customers: “Please help me find an update statement that’s able to re-order a primary key after a delete.. and by the way.. you are not allowed to use PL/SQL, triggers or a sequence, because we cannot change anything in the app schema”. Updating a PK seemed a bit odd, but then I was told about a composite PK over 2 columns, with the second column a number, meant to always start with 0 and ascending per value in the first column, and – most importantly! – without any holes in de numbering.
At first I couldn’t see a way to use pure SQL for this request, and thought of PL/SQL loops and using a temporary table. But after some time I came up with a solution, using row_number() and rowid. I think it’s a nice example of being pressed to think in sets instead of row based processing, with the solution being short, fast, and rather elegant. Try it yourself!
– create test table
create table t1( name varchar2(100), seqnr number(4), note varchar2(30));
alter table t1 add ( constraint t_pk primary key ( name, seqnr));
– insert test data
insert into t1 values ('harry', 0, 'test record 100');
insert into t1 values ('harry', 1, 'test record 101');
insert into t1 values More >
OOW 2012: Is PL/SQL still alive?
0Yesterday Bryn Llewellyn, Distinguished Product Manager, did a session called “PL/SQL Enhancements brought by the latest generation of database technology”. The room was packed. For the upcoming release of the Oracle 12c database the enhancements were broken into three areas:
- Improved PL/SQL and SQL interoperability
In the current version of the Oracle database you needed a Nested Table or Varray Type defined at schema level in order to be able to use it in the TABLE operator. This is no longer necessary, it can be a PL/SQL declared type. The types that can be used as bind variables has been extended: Boolean, Nested Tables can be used as bindvariable in Dynamic SQL. Subquery Factoring (the With clause if you will) can hold private functions, the use case presented for this: pretty print an integer in K, M, G, T. Using userdefined PL/SQL functions in a SQL statement is not advisable, but if you really need to there is a new pragma: PRAGMA UDF. With this pragma the statement is almost as fast as pure SQL.
- Security
A new step has been taken to continue on “least privileges”, meaning that the user only gets the privileges that are absolutely necessary. A Role can be granted to a
More >
How to build an IM (XMPP) solution with Oracle APEX and Websockets
0Underneath the recipe for some nice IM functionality in Oracle Apex, assuming you have a running Apex instance and a JVM 1.5+ install) and moderate+ Apex skills:
- Scrape together the ingredients
- Install Kaazing Gateway XMPP edition and OpenFire
- Configure Kaazing and OpenFire
- Create an Apex app with IM client
- Create anApex Webshop support app
- Wire the Apex Webshop support app to the Apex app with IM client
- Final touch
The webshop side of the scenario without the shop :
OOW 2012 – Larry Ellison’s Keynote Announcements: Exa, Cloud, Database
0Last night, Oracle Open World 2012 kicked off with the keynote session by Larry Ellison. In just under one hour, and with a novelty for Larry watchers (no more “next slide please”),
Oracle’s CEO had a number of interesting announcements.
Oracle will provide cloud services (no surprise) on all three tiers: SaaS, PaaS and (the surprise) IaaS.
JavaOne 2012: Thinking Through Java Enterprise Performance (BOF 4712)
0Performance of Enterprise Java Applications is a requirement and usually a challenge. Business requirements on systems can be stiff, successful systems can easily be overloaded and complex application architectures can add a burden too. Improving performance by tuning the application after it has been built seldomly renders huge improvements. By taking a step back – or even two – and regarding the application and the performance from a distance, it becomes possible to really design and architect for performance according to the ISYITF-method: it is staring you in the face. Order of magnitude improvements are attainable through logical reasoning and careful application of multi-tier architecture principles and JEE platform facilities.
This is the abstract for the session Thinking Through Java Enterprise Performance that I will be presenting on Tuesday October 2nd at JavaOne 2012 (BOF 4712 4:30 PM – 5:15 PM – Parc 55 – Cyril Magnin I.
The Outline
OOW 2012 – It will be a great Conference…
0For me it all started out very stressful. I overslept yesterday, or the day before, and noticed that it already was 08.25 AM. My flight would leave at 09.50, so the rush to the airport was very painful. Seriously hope I won’t have too many speeding tickets in my attempt to checkin somehow anyway on my drive from Utrecht to Schiphol near Amsterdam.
By some miracle, I made it, just in time with 5 minutes before boarding. Another thing that helped was the fact, due to security checks, the plane was delayed by 30 minutes or so. In the plane I caught up with a lot of good old friends like Markus Eisele, Frits Hoogland and Roel Hartman besides Lucas and Alex. After a 10 hour flight, a 30 minute drive by bus, we all arrived at the Sofitel Hotel near Oracle HQ were, bitten by the time difference, I went to bed early.
Thomas Kurian passionately speaking about Oracle during the ACED briefing
The next day at Oracle HQ, yesterday, we got our first ACE Director product Oracle product briefing and besides all info released about what’s going to be hot during Oracle Open World this year and what’s not, you can imagine while reading the following news released yesterday via Bloomberg, some More >
Recent Comments