tag:blogger.com,1999:blog-63799040908583516762024-03-13T07:07:26.632+00:00Dancing and Wrestling with Oracle APEXAn Oracle Forms developer's journey into the "exciting new world" of Oracle Apex.David Njokuhttp://www.blogger.com/profile/11205924212052603994noreply@blogger.comBlogger59125tag:blogger.com,1999:blog-6379904090858351676.post-45997547440659888032014-07-30T00:08:00.000+01:002014-07-30T00:08:54.423+01:00Want a free Oracle textbook? It's yours (for a price)<div class="separator" style="clear: both; text-align: center;">
<a href="http://www.amazon.com/gp/product/B00M7T6CRC/ref=as_li_tl?ie=UTF8&camp=1789&creative=390957&creativeASIN=B00M7T6CRC&linkCode=as2&tag=indienewsletter-20&linkId=5BTMWKPBFXUDS2NU" target="_blank"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgKKc_fJUK6FkgOKF0KQGOqdxlSf40Yu46D53YLp0mFV3BdzK8BnF6pdxVmcMeG4gJdR4xzrQ8uGAqRCCk99H3HNAJyl9RWX38xkGjK2pk3oRS4wzXdsdgHz1LdLwJDhyphenhyphenkwgdNvEoe6v118/s1600/Oracle+for+Absolute+Beginners.jpg" height="640" width="500" /></a></div>
<br />
I have neglected this blog. I apologise.<br />
<br />
I have an excuse, though. Two. Firstly, I have been keeping busy contributing over at the <a href="http://www.allthingsoracle.com/" target="_blank">All Things Oracle</a> site. It's nice over there; you should check it out - but not just now. Secondly, the projects I have been working on, of recent, have not involved a lot of interesting Apex, and so I have suffered for topics to write on for this blog.<br />
<br />
However, I recently produced a series of articles for ATO called Oracle for Absolute Beginners. It was received enthusiastically - so much so that the ATO guys and I discussed making it available for purchase on Amazon. Which I have done.<br />
<br />
But you? I like you. And so I am making the book available for free here. Completely free. And there isn't a catch.<br />
<br />
Okay, you're right, there <i>is </i>a catch. I would appreciate it if you would <b><a href="http://www.amazon.com/gp/product/B00M7T6CRC/ref=as_li_tl?ie=UTF8&camp=1789&creative=390957&creativeASIN=B00M7T6CRC&linkCode=as2&tag=indienewsletter-20&linkId=5BTMWKPBFXUDS2NU" target="_blank">go to Amazon</a> after reading it and leave an honest review</b>. Please bear in mind as you do so, that if you're already an Oracle expert, the book is not necessarily aimed at you - so don't hold the fact that it doesn't contain a chapter on Oracle exadata or 12c cloud computing against it.<br />
<br />
Got that? Thanks. Now <a href="https://drive.google.com/file/d/0B0zLV0or7HaXSGZWOFZqem9ZV0U/edit?usp=sharing" target="_blank">download the book</a> and then <a href="http://www.amazon.com/gp/product/B00M7T6CRC/ref=as_li_tl?ie=UTF8&camp=1789&creative=390957&creativeASIN=B00M7T6CRC&linkCode=as2&tag=indienewsletter-20&linkId=5BTMWKPBFXUDS2NU" target="_blank">leave an Amazon review</a>.David Njokuhttp://www.blogger.com/profile/11205924212052603994noreply@blogger.com1tag:blogger.com,1999:blog-6379904090858351676.post-9266391044448741512014-03-13T22:17:00.000+00:002014-03-13T22:17:11.169+00:00Oracle for Absolute Beginners: Part 4 - Multi-table queriesRead: <a href="http://dancingwithapex.blogspot.co.uk/2014/03/oracle-for-absolute-beginners-part-1.html">Part 1</a>, <a href="http://dancingwithapex.blogspot.co.uk/2014/03/oracle-for-absolute-beginners-part-2-sql.html">Part 2</a>, <a href="http://dancingwithapex.blogspot.co.uk/2014/03/oracle-for-absolute-beginners-part-3.html">Part 3</a><br />
<br />
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
A wise man* once said: To build the Great Wall of China, you must start with a brick. In our previous articles we acquainted ourselves with our bricks; now it’s time to build. If we consider what we’ve learned so far – Select, Update, Insert, Delete – as unicellular organisms, what we’re about to do next is multicellular, big and beautiful – it’s like going from an amoeba to a bee, a butterfly, to Beyoncé.</div>
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
[*<i style="background-color: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">that wise man was me</i>]</div>
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
Consider a real-world requirement that we might have of our Addressbook database; since it contains a list of our friends and their phone numbers, we will naturally want to see a list of their names and their phone numbers. Ah, but that presents a problem. Our friends’ names are in the FRIEND_NAME table, while their phone numbers are in the PHONE_NUMBER table. And complicating things further, we can only tell which number belongs to which friend by looking in the FRIEND_PHONE table. <i style="background-color: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">Aaargh!</i></div>
<h3 style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
JOINS</h3>
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
We could, of course, get the information by running a series of queries: one select to find our friends’ names and their friend_id; a second to find the phone_id of the phone number linked to each friend in FRIEND_PHONE; and a third query to find the number from PHONE_NUMBER using the phone_id we identified in our second query. So yes, it <i style="background-color: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">can</i> be done. But hey, you can probably ride a unicycle across Siberia – but that didn’t stop them from inventing the car.</div>
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
What we need are joins. We need a select statement that can query multiple tables at the same time.</div>
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
The syntax for a multi-table select statement is as follows:</div>
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
<a href="http://allthingsoracle.com/oracle-for-absolute-beginners-part-4-multi-table-queries/" target="_blank">Continue reading...</a></div>
David Njokuhttp://www.blogger.com/profile/11205924212052603994noreply@blogger.com0tag:blogger.com,1999:blog-6379904090858351676.post-69315739115061113792014-03-13T22:10:00.000+00:002014-03-13T22:10:38.033+00:00Oracle for Absolute Beginners: Part 3 - Update, Insert, DeleteRead: <a href="http://dancingwithapex.blogspot.co.uk/2014/03/oracle-for-absolute-beginners-part-1.html">Part 1</a>, <a href="http://dancingwithapex.blogspot.co.uk/2014/03/oracle-for-absolute-beginners-part-2-sql.html">Part 2</a><br />
<br />
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
A wise man* once said: <i style="background-color: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">So no-one told you life was gonna be this way. Your job’s a joke, you’re broke, your love life’s DOA. It’s like you’re always stuck in second gear. And when it hasn’t been your day, you week, your month, or even your year.</i></div>
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
[*That wise man <em style="background-color: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">wasn’t</em> me; but stick with me, I promise to bring this back around to Oracle SQL imminently. ]</div>
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
So – since it hasn’t been your day, your week, your month, or even your year – you decide to go to Central Perk with your friends Ross, Rachel, Monica, Phoebe, Chandler and Joey. Oh, and that new friend you made that’s also named Ross Geller. The music is loud, the atmosphere is great, and a good time is had by everyone.</div>
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
You wake up the next morning with a bit of a sore head and try to recall everything that went on the night before: you’d discovered that Chandler’s middle name is Muriel, and that Ross’ is Eustace. Rachel gave you her new phone number. Phoebe gave you her new address. Oh, and you had a big fight with new Ross and decided you no longer want to be friends with him.</div>
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
You roll out of bed and groggily wipe your eyes: all this new information needs recording; you’d better fire up your database.</div>
<h3 style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
UPDATE</h3>
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
Since we thoroughly looked into SELECT statements in the previous part, we can now turn our attention to UPDATE statements. In SQL we use update statements to change existing records – not to <i style="background-color: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">create</i> new records or to delete them – just to change them.</div>
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
The syntax for update statements is as follows:</div>
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
<a href="http://allthingsoracle.com/oracle-for-absolute-beginners-part-3-update-insert-delete/" target="_blank">Continue reading...</a></div>
David Njokuhttp://www.blogger.com/profile/11205924212052603994noreply@blogger.com0tag:blogger.com,1999:blog-6379904090858351676.post-59038098344336965782014-03-13T22:03:00.002+00:002014-03-13T22:03:59.556+00:00Oracle for Absolute Beginners: Part 2 - SQL<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
<a href="http://dancingwithapex.blogspot.co.uk/2014/03/oracle-for-absolute-beginners-part-1.html">Read Part 1</a></div>
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
A wise man* once said, no one’s ever learned how to cook just by reading recipes. And so, since we painted in the background in <a href="http://allthingsoracle.com/oracle-for-absolute-beginners-part-1-databases/" style="background-color: transparent; background-position: initial initial; background-repeat: initial initial; border: 0px; color: #cc0000; margin: 0px; padding: 0px; text-decoration: none; vertical-align: baseline;" title="Oracle for Absolute Beginners: Part 1 – Databases">Part 1</a>, we are now going to roll up our sleeves and dive in. By the end of this article you will be reading and writing SQL, the lingua franca of databases.</div>
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
<em style="background-color: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">[* that wise man was me.]</em></div>
<h3 style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
<span style="background-color: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">SQL</span></h3>
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
SQL stands for Structured Query Language (pronounced <i style="background-color: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">ess-cue-ell</i> or <i style="background-color: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">sequel</i>) and is the programming language used in the management of relational databases. And not just Oracle RDBMS; the code we are about to learn will work just as well with Microsoft’s SQL Server, IBM’s Informix, MySQL and dozens of others. SQL is very much the English of the database world; it is spoken in many environments. This is one reason why the skills you are about to learn are very valuable; they are eminently transferrable.</div>
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
SQL consists of a data definition language (DDL) and data manipulation language (DML). What this means is that we use SQL not only to define the tables into which we plan to put our data, but to manipulate (query, edit, delete, stuff like that) the data once it’s in place.</div>
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
Manipulating data using SQL is easy, as the syntax isn’t a million miles from the way we speak. For instance, to select all the data from a table you would use the SELECT … FROM <i style="background-color: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">table_name</i> command. If, on the other hand, you wanted to update data, you’d use the UPDATE command; and the DELETE and INSERT commands pretty much do what you’d expect them to, too.</div>
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
<em style="background-color: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;"></em></div>
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
It’s easy. Let me show you.</div>
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
<a href="http://allthingsoracle.com/oracle-for-absolute-beginners-part-2-sql/" target="_blank">Continue reading...</a></div>
David Njokuhttp://www.blogger.com/profile/11205924212052603994noreply@blogger.com0tag:blogger.com,1999:blog-6379904090858351676.post-37015031414106210802014-03-13T21:55:00.001+00:002014-03-13T21:56:42.838+00:00Oracle for Absolute Beginners: Part 1 - Databases<span style="background-color: white; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px;">A wise man* once said, an expert is someone who uses big words and acronyms where simple phrases would do just as nicely. So stand back and listen to this: Database, Relational Database, DBMS, RDBMS, SQL, Sub-queries, normalisation.</span><br />
<span style="background-color: white; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px;"><br /></span>
<br />
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
[<i style="background-color: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">* that wise man was me</i>.]</div>
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
So now that I’ve established my credentials by bamboozling you with arcane words and capital letters, let me tell you what the purpose of this series of articles is. By the end of it, you will be able to re-read that first paragraph and understand every word; or, if you would prefer that in more practical terms, you will be able to read – and write – SQL, which is the programming language of databases.</div>
<h2 style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
Definitions</h2>
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
Let’s meet the main characters of our story: I’ll give you a couple of definitions; one building on the other.</div>
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
<i style="background-color: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">A <b style="background-color: transparent; background-position: initial initial; background-repeat: initial initial; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">Database</b> is an organised collection of data</i>. Not yet sure what that means? Well, do you own an address book, either on your phone or in a physical book? That’s a database. After all, the addresses and phone numbers are<i style="background-color: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">organised</i> – with all friends whose names start with <i style="background-color: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">A</i> being grouped separately from people whose names start with B or C or D.</div>
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
<i style="background-color: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">A <b style="background-color: transparent; background-position: initial initial; background-repeat: initial initial; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">Relational Database</b> is a database in which the data is organised according to type with the relationships being maintained between the differing types</i>. Okay, that sounds a bit like Greek (or Dutch, if you’re Greek; or German, if you’re Dutch; or Xhosa if you’re German…), but it makes sense if you let me explain.</div>
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
Dig out your address book again. Imagine all the names grouped together; and all the phone numbers grouped together in another list; and all the addresses in a third. On their own these individual lists might be interesting but not useful; but if we establish the <i style="background-color: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">relationship</i> between the lists – this address is where that person lives and that’s their phone number – then our database takes shape.</div>
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
Make sense? Don’t worry about it too much if it doesn’t; we’ll come back to it a little later. Let’s talk about Oracle now.</div>
<div style="background-color: white; border: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding: 0px; vertical-align: baseline;">
<a href="http://allthingsoracle.com/oracle-for-absolute-beginners-part-1-databases/" target="_blank">Continue reading...</a></div>
David Njokuhttp://www.blogger.com/profile/11205924212052603994noreply@blogger.com0tag:blogger.com,1999:blog-6379904090858351676.post-31019155559401017362013-11-05T21:02:00.000+00:002013-11-05T21:10:29.697+00:00Introduction to Instrumentation<div class="MsoNormal">
The forest is thick with menace. The sunlight barely breaks through the leaves and is sludgy like curdled milk; the shadows of the branches look like gnarly fingers reaching to grasp at the pair of frightened small children.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
As they wind down the path, Hansel leaves a trail of pebbles. “It will help us find our way home,” he says to Gretel, his voice quavering with fear.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
There is a parallel somewhere in there with the world of Oracle, believe me. I know that you’re unlikely to be eaten by an ugly, wicked witch while writing PL/SQL (I can’t give you the same guarantees with Sql Server), but think of Hansel’s pebbles as debug messages, think of it as instrumentation.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Instrumentation is the practice of measuring the progress of your application’s performance as it runs, and is usually pre-baked into the software at the time the code is being written. It is, of course, not a uniquely Oracle concept, but Oracle is all I know and all I will talk about in this article. So, if you got here by googling “Hansel and Gretel”, you may wish to leave now. </div>
<div>
<br /></div>
<div>
<h3>
<b><u>Do You Need Instrumentation?</u></b></h3>
</div>
<div>
If you’ve ever asked:<br />•<span class="Apple-tab-span" style="white-space: pre;"> </span>Why is my application suddenly performing so poorly?<br />•<span class="Apple-tab-span" style="white-space: pre;"> </span>Which of my procedures needs tuning?<br />•<span class="Apple-tab-span" style="white-space: pre;"> </span>Where exactly is my application crashing?<br />•<span class="Apple-tab-span" style="white-space: pre;"> </span>What exactly is my code doing?</div>
<div>
<br /></div>
<div>
<div>
Or if you’ve ever said:</div>
<div>
•<span class="Apple-tab-span" style="white-space: pre;"> </span><i>Aaaaargh!</i></div>
<div>
Then you probably do need instrumentation.</div>
<div>
<br /></div>
<div>
<h3>
<b><u>What Is Instrumentation?</u></b></h3>
</div>
<div>
Instrumentation, very simply put, is the practice of including your debugging into your code as the code is being written, and not after the fact, after the crash/issue/problem has happened. This way, when you need to understand what your code is doing, you can simply run your application and examine your logs.</div>
<div>
<br /></div>
<div>
<h3>
<b><u>What To Instrument?</u></b></h3>
</div>
<div>
<div>
No one understands your code as well as you do (I hope!) and so no one can supply you a full list of what information you want to capture in your instrumentation. However, the following are some practices that are generally accepted as wise.</div>
<div>
<br /></div>
<div>
•<span class="Apple-tab-span" style="white-space: pre;"> </span>Include instrumentation at the entry point of every procedure and function that you write. The entry point, obviously, would be the first line after the BEGIN.</div>
<div>
•<span class="Apple-tab-span" style="white-space: pre;"> </span>Include instrumentation at the exit point of every procedure and every function that you write. The exit point of your procedures is right before your exception handling . Secondly, I’m not your mother and so I don’t want to nag you, but I do hope that your functions generally have only one exit point – one RETURN – and are not littered with RETURNs within conditional statements and other digressions. However, you should include instrumentation right before every exit point.</div>
<div>
•<span class="Apple-tab-span" style="white-space: pre;"> </span>If your routine accepts any parameters, you should include instrumentation outputting the values passed to each of these parameters.</div>
<div>
•<span class="Apple-tab-span" style="white-space: pre;"> </span>You will want instrumentation within your exception handling. You will definitely want to know if your code has raised an exception, and you will want to know what it was. Some Oracle experts argue that you should never have WHEN OTHERS THEN exception handling in your code. However, it may be a good idea to include it, instrument the exception, and then re-raise it.</div>
<div>
•<span class="Apple-tab-span" style="white-space: pre;"> </span>Instrument any significant points within the body of your code. Got a conditional statement? Then instrument the various paths within it. Writing a cursor? Then instrument any parameters it takes and any output it spits out.</div>
<div>
•<span class="Apple-tab-span" style="white-space: pre;"> </span>You may also want to include a debug message logging the SQL%ROWCOUNT after significant DML.</div>
<div style="text-decoration: underline;">
<br /></div>
</div>
<div>
<a href="http://allthingsoracle.com/introduction-to-instrumentation/" target="_blank">Continue reading...</a></div>
<div>
<br /></div>
<div style="text-decoration: underline;">
<br /></div>
</div>
David Njokuhttp://www.blogger.com/profile/11205924212052603994noreply@blogger.com0tag:blogger.com,1999:blog-6379904090858351676.post-48542342579214198622012-12-29T11:58:00.000+00:002012-12-29T11:58:42.558+00:00Review of Oracle APEX Best Practices (or the 200 day shower)<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZryh5kCitn2kUaJ5Hgq4SWIV31V-6OJNabgHIh3RYvp3CXYbJ-LMUrNEz-Ddsy-U4kEuGZKs0QxVGZeaswmvgONW-g8FHOkZ8TIqMTm0qHF6HWa0RS07HxIF5S05eGR9rRvNhNbzDrQsc/s1600/book.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="" border="0" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZryh5kCitn2kUaJ5Hgq4SWIV31V-6OJNabgHIh3RYvp3CXYbJ-LMUrNEz-Ddsy-U4kEuGZKs0QxVGZeaswmvgONW-g8FHOkZ8TIqMTm0qHF6HWa0RS07HxIF5S05eGR9rRvNhNbzDrQsc/s400/book.jpg" title="Oracle Apex Best Practices" width="300" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://www.amazon.com/gp/product/B00A232HAQ/ref=as_li_ss_tl?ie=UTF8&tag=indautlan-20&linkCode=as2&camp=1789&creative=390957&creativeASIN=B00A232HAQ" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh__Ral22zYbevaQZAQoFc1_MYJeeMldx2Rnv45ULBMqf8XDth9ztXdokzfroxd65EQtne9xsOxRRTSAFI4cY-SGilK_K96Q-jR6a9F2SRoFndGqW7NMCXjp8Xdq1E6Q23EyAvLcSX-wHGs/s1600/AmazonCom.gif" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://www.amazon.co.uk/gp/product/B00A232HAQ/ref=as_li_ss_tl?ie=UTF8&tag=indautlan-21&linkCode=as2&camp=1634&creative=19450&creativeASIN=B00A232HAQ" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgcur-AJpdpD3TdsFPuDH4-xLiB2P4bUlPn9Prysyl1sfEgEg5nIB7KkLZBpb2IzIa_mhp7leF1EvIac8ji8wl-ile99Em39lK-1fVwq__YgKvrOU57SwHMqyVcpEwEAd-Ub9mrqG-WUrOY/s1600/AmazonCoUk.gif" /></a></div>
<br />
<div class="MsoNormal">
Over the past few days I have had two books on my bedside
table: on my Google Nexus I have been reading David Eagleman’s novel <a href="http://www.amazon.com/gp/product/0307389936/ref=as_li_ss_tl?ie=UTF8&tag=indautlan-20&linkCode=as2&camp=1789&creative=390957&creativeASIN=0307389936" target="_blank">Sum: FortyTales from the Afterlives</a>, and whenever I put that down, I’ve been burying my
nose in the dead-tree (i.e. non-ebook) version of <a href="http://www.amazon.com/gp/product/1849684006/ref=as_li_ss_tl?ie=UTF8&tag=indautlan-20&linkCode=as2&camp=1789&creative=390957&creativeASIN=1849684006" target="_blank">Oracle APEX Best Practices</a> by
Learco Brizzi, Iloon Ellen-Wolff and Alex Nuijten. <o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
This article is about Oracle APEX Best Practices, of course,
but let’s talk about the short story collection, <i>Sum </i>for a bit. One of the stories in the book hypothesises
that in the afterlife we all relive our lives – with one difference: all our
actions are grouped by an ‘order by’ statement. So instead of waking and
sleeping, waking and sleeping, we spend 30 unbroken years asleep and then 40 continuous years awake. We spend 7 straight months making love, and then 6 full days
clipping our toenails. We shower for 200 days. It’s a good book; read it.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<i>Oracle APEX Best Practices</i> is a good book too. I want to
make that clear. Two of the authors,
Learco Brizzi and Iloon Ellen-Wolff are new names to me, but I have visited Alex
Nuijten’s <a href="http://nuijten.blogspot.co.uk/" target="_blank">blog</a> many times in the past and have never been let down. So I
googled the other two authors, and what I read convinced me of their bona
fides. <o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Not that the book doesn’t speak for itself. At 200-odd pages
it is a good size; it is written in clear, precise English; and it covers Apex,
from top to tail – from installation, through security and debugging, to
deployment. It is mostly aimed at Apex beginners, but there is enough in-depth
explanation in it to make it invaluable to intermediate Apex-ers too. It is a
very good book.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
But… <o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
In the afterlife in <i>Sum</i>, you spend 14 minutes experiencing
pure joy, and then 27 long hours of intense pain. I have spent 2 full
paragraphs praising <i>Oracle APEX Best Practices</i>; it is time to highlight my
quibbles.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
The first annoyance is this: to keep the page count down,
the authors often ended sections with the url of some Oracle documentation or a
blogpost containing more information. This is perfectly fine in an ebook, but
what am I supposed to do with this in a paper book? I cannot type a 200-character url into my browser's address bar. Physical books cost more
money than ebooks; they should not provide a worse experience.<o:p></o:p></div>
<div class="MsoNormal">
<div class="separator" style="clear: both; text-align: center;">
</div>
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhzgHN9qNg8LOnbNPG4gKTcuLv5-YSAmh20QLEn0zD_E31O0hVE7X1poQhyphenhyphen7MmpkFSOssR4GYHlJpIXcyIbnKuP-YJKD-0uadPfQ1cuO1mcLgQ2MLYqn2SoH6wcTtJV8P4ndcfd8ouBV1Uz/s1600/longurl1.jpg" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="72" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhzgHN9qNg8LOnbNPG4gKTcuLv5-YSAmh20QLEn0zD_E31O0hVE7X1poQhyphenhyphen7MmpkFSOssR4GYHlJpIXcyIbnKuP-YJKD-0uadPfQ1cuO1mcLgQ2MLYqn2SoH6wcTtJV8P4ndcfd8ouBV1Uz/s320/longurl1.jpg" width="320" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><b>My kingdom for a bit.ly!</b></td></tr>
</tbody></table>
<br /></div>
<div class="MsoNormal">
I do also have some questions about the title. Best
practices? This is a very good Apex book, no doubt, but I wouldn’t call it a
best practices book. I expect best practices books to prescribe the way I
develop my application and not just explain the options. <o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Finally, the authors occasionally struggle to stay true to
their topic: Apex. In <b>Chapter 2: Leveraging the Database</b>, they spend 30 pages
explaining how to use analytic and aggregate functions in Oracle databases. I
was very grateful for the lesson – but what the hell is that doing an Apex book? What next - a fried rice recipe?<o:p></o:p></div>
<div class="MsoNormal">
<br />
<\criticism ><br />
<br /></div>
<div class="MsoNormal">
Do not be put off by my quibbles; this is a good book. It
was, unfortunately, released just on the eve of Apex 4.2 and therefore only
covers up to 4.1; however, I do not think that this is a problem. Best practices are not version-dependent.<o:p></o:p><br />
<br /></div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
In Chapter 4, the authors discuss application security,
advising on how to guard against SQL injections and URL tampering, and
explaining Access Control Lists and dealing with password complexity rules using APEX_INSTANCE_ADMIN. And
Chapter 5 is a 30 page essay on debugging and troubleshooting. Both these chapters
are excellent and, along with the chapter on printing (BI Publisher, Apache
FOP, Cocoon and JasperReports), are worth the cover price on their own (insofar as any software development book is worth its crazily-inflated price). <o:p></o:p><br />
<br />
However, more than the long pieces on complex subjects, what I found useful in this book were the little reminders of things that I know and overlook: set up your User Interface Defaults <i>before</i> diving in and building your application, for example.<br />
<br /></div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
And even though I have said that the section on analytic
functions, aggregate functions and index-organized tables should not be in an
Apex book, I must concede that it is packed with useful (non-Apex) information.
<o:p></o:p></div>
<div class="MsoNormal">
According to <a href="http://www.amazon.com/gp/product/0307389936/ref=as_li_ss_tl?ie=UTF8&tag=indautlan-20&linkCode=as2&camp=1789&creative=390957&creativeASIN=0307389936">Sum: Forty Tales from the Afterlives</a>, we spend 1 whole year of our lives reading
books. I do not regret the hours that I have spent on this one. <o:p></o:p><br />
<br />
<i>(Full disclosure: I received a complimentary copy of this book from the publishers, Packt Publishing, in return for an unbiased review)</i></div>
David Njokuhttp://www.blogger.com/profile/11205924212052603994noreply@blogger.com6tag:blogger.com,1999:blog-6379904090858351676.post-39523807036367941542012-08-24T11:25:00.000+01:002012-08-24T11:27:02.225+01:00Authors & Awards: Usain Bolt with a pen<div dir="ltr">
<span style="font-family: inherit;">This post has absolutely nothing to do with Oracle, and even less to do with Apex. Sorry - you can stop reading here and I won't hold it against you. </span><br />
</div>
<div dir="ltr">
<span style="font-family: inherit;">However, I recently turned my attention to my other love, literature, and did a spot of datajournalism for another blog. I'm reprinting my piece here.</span> <br />
<br />
I'll be back waltzing and wrestling with Oracle Apex very soon. Promise.</div>
<div dir="ltr">
<br /></div>
<h2 dir="ltr" style="text-align: center;">
<a href="http://missojikutu.wordpress.com/2012/07/24/africas-leading-literary-powerhouse-south-africa-or-nigeria/" target="_blank">Africa's Leading Literary Powerhouses</a></h2>
<div dir="ltr">
<br /></div>
<div dir="ltr">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEguyo60pAigT6s5avEmnsrwJncShyphenhyphenMrJ6Tac4ALcjB6HP29JtztRwb_5PYPGSIRYeWDrAYTdcvnAPqVUiFn2yN9OeL8sEYbTryzYJ2GgkVsb_nv6_n6Usj6HeovM8xjcXe3ViWGpu8fSZDG/s1600/word-cloud1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="433" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEguyo60pAigT6s5avEmnsrwJncShyphenhyphenMrJ6Tac4ALcjB6HP29JtztRwb_5PYPGSIRYeWDrAYTdcvnAPqVUiFn2yN9OeL8sEYbTryzYJ2GgkVsb_nv6_n6Usj6HeovM8xjcXe3ViWGpu8fSZDG/s640/word-cloud1.png" width="640" yda="true" /></a></div>
<div dir="ltr">
<span style="font-family: inherit;">Summer 2012 and the eyes of the world turn to the city of London, England. People across the globe sit in beer parlours, shebeens, pubs, washing unhealthy snacks down with tankards of beer and cheering as the healthiest specimens of our nations run, jump and swim faster, higher, stronger.</span><br />
</div>
<span style="font-family: inherit;">But why stop at sportsmen? Why not pitch our countries’ plumbers against the world’s, our street-corner hookers, our brain surgeons? Why don’t Liberians sneer at Sierra Leoneans: “The barefoot kids hawking peanuts in your Kroo Bay slums are nothing compared to the former child soldiers weaving through traffic selling groundnuts in the misery of our West Point”? And why not stand our writing ‘athletes’ up against each other in a sort of literary Olympic Games and see which nation ends up on the podium?</span><br />
<br />
<span style="font-family: inherit;">And the last was just what we did. The rules: We sourced our data by analysing the winners of major international literary prizes, filleting out all African winners and noting their country of origin. We limited our scope – and therefore our resultset – to awards for English language literature, with a deliberate bias towards prose fiction. Where a writer has dual nationality, as in the case of Zimbabwean-British author Doris Lessing, we favoured the African nation; with Mauritian-South African novelist, Lindsay Collen, we plumped for Mauritius, as this is the country she identifies with.</span> <br />
<br />
<a href="http://missojikutu.wordpress.com/2012/07/24/africas-leading-literary-powerhouse-south-africa-or-nigeria/" target="_blank">Continue reading...</a>David Njokuhttp://www.blogger.com/profile/11205924212052603994noreply@blogger.com0tag:blogger.com,1999:blog-6379904090858351676.post-899194245523432962012-07-04T12:00:00.000+01:002012-07-04T12:00:09.668+01:00Introduction to Oracle Flashback Technology (Part 3: Flashback Database)In
this series of articles, we are discussing the collection of tools that make up
the Oracle Flashback Technology suite. They are varied in their purpose and in
their implementation, but together they act very much like a morning-after pill
for your database, a means by which developers and administrators can protect
their database from immediate past errors and indiscretions. In the first part
of this series, I talked about the flashback tools that every developer should
have in his arsenal, and in the second we rummaged through the pile looking for
things that might be of interest to administrators. However, I have saved the biggie for last:
Flashback Database.<br />
<br />
<div>
<b><u>What is Flashback Database?</u></b><o:p></o:p></div>
<div>
</div>
<div>
For those times when minor surgery is not enough, Flashback Database can apply a defibrillator to the heart of your database, shout “Clear!” and proceed to shock it back to a happier time. This is because Flashback Database is a mechanism that allows administrators rewind an entire database to a past time or SCN. Flashback Database has most of the advantages of a point in time recovery, with only a fraction of the hassle and longwindedness. Flashback Database, unlike some of
the lesser members of the Flashback family, is a physical-level recovery
mechanism; it uses its flashback logs to access past versions of data blocks,
finessing the final result with information from the archived redo log where
necessary.<o:p></o:p></div>
<div>
<br /></div>
<div>
<b><u>Setting Up Flashback Database</u></b></div>
<div>
</div>
<div>
<span style="font-family: inherit;">Flashback Database must be enabled before you can use
it. This simple query will tell you
whether it is or not:</span></div>
<div>
<br /></div>
<pre>SELECT FLASHBACK_ON FROM V$DATABASE;</pre>
<br />
<div>
<span style="font-family: inherit;">The result will either be YES or NO. If Flashback Database is not enabled and you
wish to enable it, you will need to ensure that the database is in ARCHIVELOG
mode first. <o:p></o:p></span></div>
<div>
<span style="font-family: inherit;"><br /></span></div>
<div>
<span style="font-family: inherit;">There are two other
prerequisites for enabling flashback database.
You will need to instruct the database on where to store the flashback
logs that it will generate and you will need to tell it how much space it can
use up doing so. These are expressed in two instance parameters –
DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE. Remember to set the size before the location.</span><o:p></o:p><br />
<span style="font-family: inherit;"><br /></span></div>
<pre>ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 9G SCOPE=BOTH;
</pre>
<pre></pre>
<pre>ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = ‘/oracle/flash_recovery_area’;
</pre>
<br />
When Flashback is up and running, your control over the Flashback Recovery Area, which is what the location you specify in the DB_RECOVERY_FILE_DEST parameter is called, will be limited; the database will create and age out the flashback logs as necessary. One way in which you can attempt to manage the Flashback Recovery Area is to specify a retention target, telling the database how long you would like it to retain its logs before they are overwritten by newer ones. Whatever retention target you specify, however, is not set in stone; in practice, the length of time that logs are retained will depend on the intersection of the retention target and the space made available by the DB_RECOVERY_FILE_DEST_SIZE parameter. If the database runs out of space, it will kill off old logs, irrespective of the retention target.<br />
<span style="font-family: inherit;"><br /></span><br />
<div class="MsoNormal">
<span style="font-family: inherit;">The retention target has a default of 1 day and is set in
minutes, thusly:</span></div>
<div>
<br /></div>
<pre>ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET = 2880; /* 2 Days */</pre>
<br />
<div>
<span style="font-family: inherit;">This done, you are now ready to flick the switch on
Flashback Database. To do so you will need to shut down cleanly and mount the
database, and while in this mode run the following:</span><o:p></o:p></div>
<div>
<br /></div>
<pre>ALTER DATABASE FLASHBACK ON;
</pre>
<br />
<a href="http://allthingsoracle.com/introduction-to-oracle-flashback-technology-part-3-flashback-database/" target="_blank">Continue reading...</a>David Njokuhttp://www.blogger.com/profile/11205924212052603994noreply@blogger.com0tag:blogger.com,1999:blog-6379904090858351676.post-47242249995454874952012-07-03T12:00:00.000+01:002012-07-03T12:00:02.070+01:00Introduction to Oracle Flashback Technology (Part 2: Database Administrators)<br />
<div class="MsoNormal">
In the first in this series of articles on the tools in the
Oracle Flashback Technology armoury, I discussed what is available to
application developers, the various means by which they can rewind the clock
and correct recently-past data errors.
However, if this was a world in which the worst thing that could happen
was a small mix-up with data, what a happy world it would be. However, this is
not Disneyland, and data errors are not the big, bad wolf at the door; it is
errors that raze whole database objects that database administrators fear. But
fortunately, Oracle Flashback can come to our rescue in those situations too.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
First a quick recap: Oracle Flashback Technology is actually
a clutch of different Oracle tools that enable developers and administrators
reach into the immediate past of their database to recover from errors, without
having to resort to point-in-time recovery options. For this reason, they are
relatively fast and incur a relatively low logistical overhead (since the
database does not need to suffer any downtime to implement them).<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b><u>FLASHBACK TABLE:</u></b></div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
Flashback table enables you revert a table to its state at a
specified timestamp or SCN. Any rows of data created after the parameterized
timestamp disappear and any deleted rows zombie back to existence; in addition,
associated attributes such as triggers, indexes and constraints remain
unviolated. And all this magic occurs without even the slightest blip of
interference affecting the rest of the database; it stays up and available.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Flashback table is able to sidestep the unpleasantness of
restoring from backups by using information in the undo tablespace to restore
the table. <o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Imagine this scenario: some catastrophe has befallen the EMP
table and the developers are unable to unpick the data. All eyes turn to you:
Save us, they beseech, wave your wand and erase the past hour. This is where
you would find Flashback Table useful:<o:p></o:p></div>
<br />
<pre>FLASHBACK TABLE EMP TO SYSTIMESTAMP – INTERVAL '60' MINUTE;
</pre>
<span style="background-color: white;"><br /></span><br />
<span style="background-color: white;">And it’s done. Day saved. Simple.</span><br />
<span style="background-color: white;"><br /></span><br />
<br />
<div class="MsoNormal">
<o:p></o:p></div>
<div class="MsoNormal">
To use flashback table, however, there are a few prerequisites
that need to be in place:<o:p></o:p></div>
<div class="MsoNormal">
</div>
<ul>
<li>You must have the FLASHBACK privilege on the table. Or you must have the FLASHBACK ANY TABLE system privilege.</li>
<li>You must have the SELECT, INSERT, DELETE and ALTER privileges on the table.</li>
<li>Row movement must be enabled on the table.</li>
<li>Finally - and crucially - the information in the undo tablespace must extend back far enough to cover your timestamp or SCN.</li>
</ul>
<a href="http://allthingsoracle.com/introduction-to-oracle-flashback-technology-part-2-database-administrators/" target="_blank">Continue reading..</a>.<br />David Njokuhttp://www.blogger.com/profile/11205924212052603994noreply@blogger.com0tag:blogger.com,1999:blog-6379904090858351676.post-51035860763339348982012-07-02T12:00:00.000+01:002012-07-02T12:00:17.303+01:00Introduction to Flashback Technology (Part 1: Application Developers)<b id="internal-source-marker_0.97938286466524" style="font-weight: normal;"><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;">Imagine, if you will, that someone invented time travel. What would you do with it? Nip back into the past and fix that error you made? And maybe you’d want to do something about that silly, ill-advised haircut you had when you were 17. Well, the good news is that, in a manner of speaking, time travel </span><span style="background-color: transparent; font-family: Arial; font-size: 15px; font-style: italic; vertical-align: baseline; white-space: pre-wrap;">has</span><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;"> been invented. At least as far as Oracle databases go. </span><br /><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;"></span><br /><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;">And the bad news? There’s nothing it can do about your teenage haircut. </span><br /><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;"></span><br /><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;">Oracle Flashback Technology is a suite of features that enables Oracle application developers and database administrators to wind the clock back and examine their database objects and data in a previous state without having to resort to backup media. Simply put, Oracle Flashback Technology is time travel for databases. </span><br /><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;"></span><br /><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;">In this series of articles I intend to expound on the benefits of Oracle Flashback to application developers and to database administrators. This first installment will concentrate on the tools that will be of interest to application developers.</span><br /><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;"> </span><br /><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;">Oracle Flashback Technology is actually a collection of features that enables the developer to examine the database’s recent past in a number of ways:</span></b>
<br />
<b style="font-weight: normal;"><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;"><br /></span></b><br />
<b id="internal-source-marker_0.97938286466524"><span style="background-color: transparent; font-family: Arial; font-size: 15px; text-decoration: underline; vertical-align: baseline; white-space: pre-wrap;">Oracle Flashback Query:</span><br /><span style="background-color: transparent; font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">Flashback query is a neat trick. It allows you run a query against your data as it was at past time. Imagine you accidentally delete a number of rows </span><span style="background-color: transparent; font-family: Arial; font-size: 15px; font-style: italic; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">and commit your changes.</span><span style="background-color: transparent; font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;"> With flashback query you can easily reach into the past and retrieve those lost rows. Using the </span><span style="background-color: transparent; font-family: Arial; font-size: 15px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">SELECT... AS OF</span><span style="background-color: transparent; font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;"> clause you can examine the ghost of data past by referencing its timestamp.</span></b><br />
<span style="font-family: Arial;"><span style="font-size: 15px; white-space: pre-wrap;"><br /></span></span>
<br />
<pre>SELECT *
FROM emp AS OF TIMESTAMP TO_TIMESTAMP('2012-04-21 17:00:00','YYYY-MM-DD HH24:MI:SS');
</pre>
<pre></pre>
<pre></pre>
<b id="internal-source-marker_0.97938286466524" style="font-weight: normal;"><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;">And if you wanted to view those rows that you accidentally deleted 5 minutes ago?</span></b>
<br />
<b style="font-weight: normal;"><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;"><br /></span></b>
<br />
<pre>SELECT *
FROM emp AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '6' MINUTE)
MINUS
SELECT *
FROM emp;
</pre>
<br />
<br />
<a href="http://allthingsoracle.com/introduction-to-oracle-flashback-technology-part-1-application-developers/" target="_blank">Continue reading...</a>David Njokuhttp://www.blogger.com/profile/11205924212052603994noreply@blogger.com0tag:blogger.com,1999:blog-6379904090858351676.post-16948572576134188472012-02-16T13:30:00.000+00:002012-02-16T13:30:04.538+00:00Introduction to Materialized Views: Query Rewrite<br />
<div class="MsoNormal">
<span style="font-family: inherit;">In the fairy tale, <i>The
Shoemaker and The Elves,</i> a poor cobbler works to the best of his ability;
and somehow, when he isn’t looking, magical beings would sneak in and polish
and perfect his handiwork. <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: inherit;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: inherit;">It seems the people at Oracle are up on their fairy tales.
Perfectly-tuned queries are the Holy Grail to developers and DBAs; we are
constantly fiddling, forever optimising, shaving milliseconds from the total
run time. Well, since 8i, functionality has been in place that can take our DML
statements and magically improve them. <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: inherit;"><br /></span></div>
<div class="MsoNormal">
<span style="font-size: 11pt; line-height: 115%;"><span style="font-family: inherit;">Oracle
do not admit that this involves any elves. But I have my suspicions.</span></span></div>
<div class="MsoNormal">
<span style="font-size: 11pt; line-height: 115%;"><span style="font-family: inherit;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-size: 11pt; line-height: 115%;"><b><u><span style="font-family: inherit;">How It Works:</span></u></b></span></div>
<div class="MsoNormal">
<span style="font-family: inherit;">Here’s how it works: part of the reason queries involving
joins between data-heavy tables can take such a long time is that the engine
needs to compute aggregates and disentangle the joins between the tables before
it can run the query. However, materialized views, by their very nature, have
all that information precomputed. Therefore, if you can find the right
materialized view and match it to the right query, you can cut running times
dramatically.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: inherit;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: inherit;"><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: inherit;">This trick, called Query Rewrite, only works with SELECT
statements. However, those statements may be hidden away in a CREATE TABLE … AS
SELECT statement, or an INSERT INTO … SELECT statement, or they may be
squirrelled away in any type of subquery or sub-clause.<span style="font-size: 11pt; line-height: 115%;"> </span></span></div>
<div class="MsoNormal">
<span style="font-size: 11pt; line-height: 115%;"><span style="font-family: inherit;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-size: 11pt; line-height: 115%;"><span style="font-family: inherit;">Additionally,
Query Rewrite must be enabled at the initialization parameter level:</span></span></div>
<div class="MsoNormal">
<span style="font-family: Calibri, sans-serif; font-size: 11pt; line-height: 115%;"><br /></span></div>
<div class="MsoNormal">
<br /></div>
<pre class="sql" name="code">Alter session set query_rewrite_enabled = TRUE;
</pre>
<br />
<a href="http://allthingsoracle.com/introduction-to-materialized-views-query-rewrite/" target="_blank">Continue reading...</a>David Njokuhttp://www.blogger.com/profile/11205924212052603994noreply@blogger.com0Twickenham, Greater London, UK51.444581 -0.33524651.4247875 -0.374728 51.4643745 -0.29576399999999997tag:blogger.com,1999:blog-6379904090858351676.post-9721499454989423172012-02-07T15:00:00.000+00:002012-02-07T21:02:41.672+00:00Alter session set triggers disabled (or The personal anti-ninja assassin insurance policy)If your database was a city, your tables would be buildings, your constraints would be the streets and motorways that connect them, your records would be the citizens that populate them, your views the nightclubs and restaurants where like-minded records go to hang out.<br />
<div>
<br /></div>
<div>
And triggers? Well, they'll definitely be ninjas. Think about it: easily overlooked, moving with stealth, 'firing' on command, shrouded in mystery.</div>
<div>
<br /></div>
<div>
Triggers, of course, are immeasurably useful. You don't need me to tell you that. However, there are times when you wish you could flip a switch and turn them off: imagine you are inserting or updating a truckload of data and cannot afford the overhead, or you are tracking a simple statement and do not want it to take any unexpected detours. </div>
<div>
<br /></div>
<div>
Of course, you can knock up a quick anonymous pl/sql block to loop through all your triggers (or at least all the relevant ones) and dynamically disable them.</div>
<div>
<br /></div>
<pre class="sql" name="code">/*
** This block will disable all database triggers. Do NOT do this.
*/
begin
for i in (select trigger_name
from user_triggers) loop
execute immediate 'alter trigger '||i.trigger_name||' disable';
end loop;
end;
</pre>
<br />
<i>"Ladies and gentlemen, this is your pilot speaking. We will soon be flying across the Himalayas mountains. I will fly this section <u>with my eyes closed</u>. Because I can. Please, sit back and enjoy the complimentary peanuts and your in-flight movie, <b>Alive</b>."</i><br />
<br />
Just because you <i>can </i>do a thing, does not mean that you <i>should</i>. You wrote those triggers for a reason; so unless you can guarantee that no other users will be using your database, switching them all off for any length of time might be a tad unwise. What you need in those circumstances is the ability to disable triggers <i>for only your session.</i> It'll be like your personal anti-ninja assassin insurance policy.<br />
<br />
Unfortunately, <i style="font-weight: bold;">ALTER SESSION SET TRIGGERS DISABLED;</i> is not a valid Oracle command. So until it is, we are going to need to create a substitute for it.<br />
<br />
The easiest way to do this would be to create a session variable, a boolean that you can switch on or off, and then edit your triggers to only fire dependent on the state of this variable.<br />
<br />
The first bit is easy. A variable in the specification section of a database package makes a perfect session-specific boolean.<br />
<br />
<pre class="sql" name="code">create or replace package trg_pkg as
skip_trigger boolean := FALSE;
end trg_pkg;
</pre>
<br />
Now all we need do is add a line as the first bit of executable code in our triggers checking the state of our <b>trg_pkg.skip_trigger</b> variable. If it is TRUE, we exit the trigger without executing any code; if not, we fire the trigger as normal. Locate the triggers you may want to switch off and add the following to them:<br />
<br />
<pre class="sql" name="code">
...
BEGIN
if trg_pkg.skip_trigger then return; end if; -- do not execute the trigger.
...
</pre>
<br />
And that's it. Whenever you do not want triggers to fire in your session, all you need do is flip your trg_pkg.skip_trigger switch. The triggers won't fire for whatever commands you carry out, but will work as normal for everyone else. (For security reasons, you will probably want to control which users are granted execute permissions on your trg_pkg package.)
<br />
<br />
One last thing, if you want to add the clause to <i>all</i> the triggers in your schema - as I have needed to do recently - you might find the following code handy.<br />
<br />
<pre class="sql" name="code">/*
** This script will rebuild all triggers adding a clause which
** prevents the trigger from firing if trg_pkg.skip_trigger is TRUE.
** This code presumes that no trigger is longer than 32767 characters long.
** 04/02/2012 David Njoku * Created this script.
*/
declare
vSql varchar2(32767);
vBackup varchar2(32767);
vString user_source.text%type;
vCLAUSE CONSTANT varchar2(500) := ' if trg_pkg.skip_trigger then return; end if; -- Do not execute this trigger if trg_pkg.skip_trigger is set to true. ';
begin
-- Find all the triggers that relate to this schema's tables.
for i in (select at.trigger_name, at.owner
from all_triggers at, all_objects ao
where at.table_owner = USER
and at.status = 'ENABLED'
and at.trigger_name = ao.object_name
and at.owner = ao.owner
and ao.object_type = 'TRIGGER'
and ao.status = 'VALID'
and not exists (select 1
from recyclebin
where object_name = at.trigger_name)) loop
-- Now that we've got the trigger name and owner, we need to grab a hold of the existing code, in case we need to revert.
vBackup := ' CREATE OR REPLACE ';
for j in (select replace(text,chr(10),' ') text
from all_source
where owner = i.owner
and name = i.trigger_name
and type = 'TRIGGER'
order by line) loop
vBackup := vBackup||chr(10)||j.text;
end loop;
/*
** We now need to start building our new version of the trigger. The plan is to add our if clause immediately after the BEGIN
** Some triggers will contain more than 1 BEGIN. Since we cannot tell which is the correct one, we'll need to add our clause after
** all of them.
*/
vSql := ' CREATE OR REPLACE ';
for j in (select replace(text,chr(10),' ') text
from all_source
where owner = i.owner
and name = i.trigger_name
and type = 'TRIGGER'
order by line) loop
-- do we need to add our clause here?
if replace(upper(j.text),' ','') like '%BEGIN' then
vSql := vSql||chr(10)||j.text||chr(10)||vCLAUSE;
else
vSql := vSql||chr(10)||j.text;
end if;
end loop;
-- Now we've got our trigger we should try building it.
begin
execute immediate vSql;
exception
when others then
dbms_output.put_line(i.trigger_name||' '||sqlerrm);
end;
-- If the trigger was not built successfully for whatever reason we should revert to the orginal.
for j in (select 1
from all_triggers
where trigger_name = i.trigger_name
and owner = i.owner
and status = 'DISABLED') loop
begin
execute immediate vBackup;
exception
when others then
dbms_output.put_line('BACKUP '||i.trigger_name||' '||sqlerrm);
end;
end loop;
end loop;
end;
/
</pre>David Njokuhttp://www.blogger.com/profile/11205924212052603994noreply@blogger.com1tag:blogger.com,1999:blog-6379904090858351676.post-83843393558505180592012-01-18T22:21:00.000+00:002012-01-18T22:23:22.491+00:00Introduction to Regular Expressions in Oracle<span style="color: #404040; font-family: inherit; line-height: 115%;">Regular expressions – RegEx or RegExp to their
friends – were fashionably late in coming to the Oracle party. The seeds of
what we know today as regular expressions were formulated in the period
immediately following the Second World War in fields as diverse as formal
language theory and neurophysiology; but it wasn’t until the PL/SQL Web toolkit
arrived for 8i and 9i that they popped their heads over the Oracle parapet. By
this time they were already rock stars in the worlds of Unix and Perl.</span><br />
<div class="MsoNormal">
<span style="font-family: inherit;"><br /></span></div>
<div class="MsoNormal">
<span style="color: #404040; font-family: inherit; line-height: 115%;">But I’m being rude here; I should introduce you.
Regular expressions are a codified means to accomplish flexible
pattern-matching in strings of text.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: inherit;"><br /></span></div>
<div class="MsoNormal">
<span style="color: #404040; font-family: inherit; line-height: 115%;">And, if you’re rereading that definition and
thinking ‘huh?!’, that’s a feeling you might want to get used to, because,
while regular expressions are powerful and definitely useful, they can seem
very much like hieroglyphics to the uninitiated. But don’t worry, I’ll hold
your hand.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: inherit;"><br /></span></div>
<div class="MsoNormal">
<span style="color: #404040; font-family: inherit; line-height: 115%;">String pattern matching has always been possible in
Oracle sql and pl/sql, of course. With the LIKE condition and the simple
metacharacters of “%” and “_”, character patterns could be described and
integrated into queries. However, the moment you required a little complexity
these basic wildcards cowered in fear. An example: my name is David, but
friends tend to call me Dave when they have a beer or two in them. To match
either spelling of my name, not using regular expressions, you would have to
query the table for ‘Dav%’. But then your results might be flooded with
Davidsons and Davises and Davinas. And I’m definitely not pretty enough to be
called Davina!</span><o:p></o:p></div>
<div class="MsoNormal">
<span style="color: #404040; font-family: Arial, sans-serif; font-size: 10pt; line-height: 115%;"><br /></span></div>
<div class="MsoNormal">
<span style="color: #404040; font-family: Arial, sans-serif; font-size: 10pt; line-height: 115%;"><a href="http://allthingsoracle.com/introduction-to-regular-expressions-in-oracle/">Continue reading ...</a></span></div>David Njokuhttp://www.blogger.com/profile/11205924212052603994noreply@blogger.com0tag:blogger.com,1999:blog-6379904090858351676.post-1408824817760450762012-01-10T12:00:00.000+00:002012-01-10T12:00:03.314+00:00Introduction to Autonomous Transactions (Or If Obama And Merkel Had A Lovechild...)I don't know if you've been paying attention, but you may have noticed that there's a bit of a recession going on at the moment. Stock prices are crashing around the globe, and I'm pretty sure that the Euro is now worth less than Monopoly money. It's very much like the Great Depression of the 1930s all over again - except that, this time, the bankers don't have the decency to throw themselves out of windows.<br />
<br />
The solution, David Cameron, Angela Merkel and Barack Obama assure us, is for everyone to go out and spend more money. That'll kickstart our economies, they assure us. More transactions. A lot more.<br />
<br />
So they'll probably thank me for this article that I wrote over at <a href="http://allthingsoracle.com/" target="_blank">All Things Oracle</a> about autonomous transactions. Have a read; you may thank me too.<br />
<br />
<div style="text-align: center;">
<a href="http://allthingsoracle.com/autonomous-transactions-in-oracle/" target="_blank"><b>AUTONOMOUS TRANSACTIONS IN ORACLE</b></a></div>
<br />
<div style="background-attachment: initial; background-clip: initial; background-color: white; background-image: initial; background-origin: initial; border-bottom-width: 0px; border-color: initial; border-image: initial; border-left-width: 0px; border-right-width: 0px; border-style: initial; border-top-width: 0px; color: #404040; line-height: 19px; margin-bottom: 25px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; vertical-align: baseline;">
<span style="font-family: inherit;"><i>Autonomous transactions are a bit like that dark part of the forest, beyond the rickety bridge, where the shadows are shaped like witches and bats swarm soundlessly from the trees. We all know it’s there, but the wise are in no hurry to visit.</i></span></div>
<div style="background-attachment: initial; background-clip: initial; background-color: white; background-image: initial; background-origin: initial; border-bottom-width: 0px; border-color: initial; border-image: initial; border-left-width: 0px; border-right-width: 0px; border-style: initial; border-top-width: 0px; color: #404040; line-height: 19px; margin-bottom: 25px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; vertical-align: baseline;">
<span style="font-family: inherit;"><i>Here, however, is a definition: an autonomous transaction is a completely independent transaction that is started by temporarily suspending the parent/calling transaction, which is then resumed after the independent transaction is completed. Or, if you prefer a real-world analogy: an autonomous transaction is a little like getting married, but having an affair on the side.</i></span></div>
<div style="background-attachment: initial; background-clip: initial; background-color: white; background-image: initial; background-origin: initial; border-bottom-width: 0px; border-color: initial; border-image: initial; border-left-width: 0px; border-right-width: 0px; border-style: initial; border-top-width: 0px; color: #404040; line-height: 19px; margin-bottom: 25px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; vertical-align: baseline;">
<span style="font-family: inherit;"><i>Morality isn’t the reason autonomous transactions are frowned upon; they possess the potential to get out of hand. Autonomous transactions are completely self-contained transactions that commit independently, and so to start one is to juggle two transactions...</i></span></div>
<div style="background-attachment: initial; background-clip: initial; background-color: white; background-image: initial; background-origin: initial; border-bottom-width: 0px; border-color: initial; border-image: initial; border-left-width: 0px; border-right-width: 0px; border-style: initial; border-top-width: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; vertical-align: baseline;">
<a href="http://allthingsoracle.com/autonomous-transactions-in-oracle/" target="_blank">Continue reading...</a></div>
<br />
<br />
<br />David Njokuhttp://www.blogger.com/profile/11205924212052603994noreply@blogger.com0tag:blogger.com,1999:blog-6379904090858351676.post-22335038997185078212012-01-03T12:00:00.000+00:002012-01-03T12:00:10.866+00:00Should Your Next Project Be Written In Forms or Apex? (Or The Polygamist's Dilemma)<br />
<div class="MsoNormal">
<span lang="EN-US">The
polygamist pauses, his hand hovered above the doorknob. He looks back down the
hallway, blinking in the harsh light of the naked bulb he has just flipped on
to do war with the shapeless darkness of the night. A few paces away - no
further away than a firm decision - stands another
door: silent, inviting. His gaze returns to the door in front of him; also as
silent as the entrance to Aladdin's cave. Which door should he open? Which of
his beloved wives should he visit? <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span lang="EN-US">Okay,
I admit it, I know nothing about polygamy. I once caught an episode of <i>Big Love</i> on telly, and I enjoyed reading
Lola Shoneyin's <i>The Secret Lives Of Baba
Segi's Wives</i>, but that's about it. <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span lang="EN-US">I do,
however, know a bit about Oracle Forms and Oracle Application Express. Which is
kinda fortunate since I'm guessing you're not here for marital advice. A
question I do get asked from time to time - most recently on <a href="http://www.linkedin.com/in/davidnjoku" target="_blank">LinkedIn </a>- is
this: What technology should I choose for my new project - Oracle Forms or
Oracle Application Express? <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span lang="EN-US">At
the risk of sounding like a guru sat atop a Himalayan rock, there is only one
real answer to this question: look within your heart, the answer floats like an asteroid in the
galaxy of your inner space. Or something. Personally, if I was making the
decision a year ago I'd have said Apex cos it was new and exciting; today, I'd
probably say Forms, cos it's old and I better understand its foibles: the
polygamist's dilemma - the new, nubile wife, or the old wife who he fully
understands? <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span lang="EN-US">You
will probably want to base your decision on factors more concrete and
quantifiable than imaginary asteroids, so here are some things to consider. <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b><span lang="EN-US">Skills:</span></b><span lang="EN-US"> What skills do you have? What skills are
you willing to invest in? What skills are you capable of acquiring? I would
imagine that you have a greater wealth of Forms experience. This need not be
the deciding factor, but it should definitely count as a tick in that box. I
was able to retrain my team of Forms developers in Apex, but it took some time
and some expense. And, crucially, it was a challenge they were happy to face: I
still have nightmares about The Big Oracle ADF Disaster of 2008. My therapist
says I'll get over it one day, but I doubt it. <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b><span lang="EN-US">Platform:</span></b><span lang="EN-US"> A huge part of your choice must depend
on the demands of the project itself. For instance, what platform are you
hoping to run it on? If you are planning to take advantage of the current boom
in mobile computing, then Apex should be your choice. I must admit that I have
not yet tried to run a Forms application on a phone or a tablet, but I do not
suspect that that is an experiment that would end happily. <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span lang="EN-US">If,
on the other hand, you expect your application to be run only from PCs and laptops
using a myriad of browsers, with or without javascript enabled, then possibly
Forms with its browser-agnostic java applet might be worth a look-in. <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b><span lang="EN-US">Cost:</span></b><span lang="EN-US"> If you are a millionaire Saudi Arabian
prince who has only got to this page cos you googled "I am looking for the
oracle on polygamy", you can skip this bit. The rest of you, pay
attention. With its middleware costs,
Forms is undeniably the more expensive option. I am not allowed anywhere
near the money at my company, but I am assured that the difference in cost can
be considerable. <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b><span lang="EN-US">Audience:</span></b><span lang="EN-US"> Who is your application aimed at? If it
is to be published on the open Internet, then you only really have one choice -
Apex. I am not denying the work Oracle's Forms team has done to modernise the
old girl, but I doubt that it will ever be truly suitable for the world-wide
web. <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span lang="EN-US">However,
if you are building an enterprise application, then there is definitely
something to be said for the solidity and gravitas that Forms still exudes. And
if your application will be used mostly for data-entry, then Forms provides
speed that Apex, even with its clever javascript and ajax hooks, cannot match. <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b><span lang="EN-US">Extendability:</span></b><span lang="EN-US"> The answer to the question of
which is the more extendable technology may not be as obvious as it may first
seem. Being a web technology, Apex can more easily sup at the banquet of ajax
and javascript, can more easily flirt with jQuery and whatever the next big
thing will be. But with each iteration of Forms, its door is opened wider, with
Java pluggable components and javascript and CSS support. However, I believe
that Apex will always outpace Forms in this respect; the story of the tortoise
and the hare was only a fairy tale. <o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-US"><br /></span></div>
<div class="MsoNormal">
<b>Speed:</b> I cannot get my hands on the exact figures now, but I have read that for every tenth of a second that Amazon manages to shave off their website's response time, sales increase by many millions of dollars. Google experienced a similar increase in the stickiness of their site when they introduced Google Instant. If response times are as crucial to your project, then perhaps you should be leaning towards lightweight Apex. But do not dismiss Forms out of hand; from 11g, it is possible to slash load times by pre-starting runtime engines. The tortoise may have bought itself some roller skates. </div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b><span lang="EN-US">Deployability:</span></b><span lang="EN-US"> Our IT team are forever
engaged in a battle with Oracle Application Server. One of them - OAS or the IT
guys - isn't very good at their job. Since no one at Oracle has ever bought me
a drink at the pub, I'll assume it's their fault. Apex is easier to deploy than
Forms; indeed there is a growing number of companies that will host your Apex
application for you. <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b><span lang="EN-US">The Big Picture: </span></b><span lang="EN-US">You have to consider the wider context. In my case, the option of
building Forms applications is a receding one, since my company has taken the
decision to move towards Apex. We now have a cohort of new, young developers to
who Forms is Betamax. <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b><span lang="EN-US">Conclusion: </span></b><span lang="EN-US">The polygamist makes his decision and
quietly opens a door. He enters the room and closes the door behind him. <i>Are you awake?</i> he whispers into the
darkness. There is a crack in the curtains, and a shaft of milky moonlight is
lying across the bed like an abandoned sword.<i> Yes</i>, a voice whispers from beneath the covers. He walks towards
the sound, his pace quickens with excitement. <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span lang="EN-US">There
is no universally right or wrong answer; it depends on your project and the
factors I have named above (and the many I have not thought of - which is why
there is a comments section below). <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<i><span lang="EN-US">PS: I'd like to thank <a href="http://www.linkedin.com/pub/stephen-cairns/10/a00/513" target="_blank">Steve Cairns</a> for
his help with this post. He is one of the country's leading experts on
polygamy. Or Oracle technologies. I forget which</span></i><span lang="EN-US">. <o:p></o:p></span></div>David Njokuhttp://www.blogger.com/profile/11205924212052603994noreply@blogger.com2tag:blogger.com,1999:blog-6379904090858351676.post-32743286634019715802011-12-22T19:52:00.000+00:002011-12-22T20:15:57.851+00:00Of Comments And Panties...Code comments are like underwear: people can't see them, but they really should be there, and it's best if you keep them clean. And brief.<br />
<div>
<br /></div>
<div>
Oh, and it's never as funny as you think it is, if they contain references to Homer Simpson.</div>
<div>
<br /></div>
<div>
Here's an article I wrote for <a href="http://allthingsoracle.com/" target="_blank">All Things Oracle</a> about commenting in programming. (Un)fortunately, it contains no further references to underwear. I apologise.</div>
<div>
<br /></div>
<div>
<h2 class="post-title" style="background-attachment: initial; background-clip: initial; background-color: white; background-image: initial; background-origin: initial; border-bottom-width: 0px; border-color: initial; border-image: initial; border-left-width: 0px; border-right-width: 0px; border-style: initial; border-top-width: 0px; color: #303030; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; line-height: 28px; margin-bottom: 12px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; vertical-align: baseline;">
<a href="http://allthingsoracle.com/how-to-make-comments-the-most-important-code-you-write/" rel="bookmark" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; border-bottom-width: 0px; border-color: initial; border-image: initial; border-left-width: 0px; border-right-width: 0px; border-style: initial; border-top-width: 0px; color: #303030; line-height: 24px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; vertical-align: baseline;" title="How to make comments the most important ‘code’ you write"><span style="font-size: large;">How to make comments the most important ‘code’ you write</span></a></h2>
</div>
<div>
<div style="background-attachment: initial; background-clip: initial; background-color: white; background-image: initial; background-origin: initial; border-bottom-width: 0px; border-color: initial; border-image: initial; border-left-width: 0px; border-right-width: 0px; border-style: initial; border-top-width: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; vertical-align: baseline;">
<i>80% of all communication, it is often said, is nonverbal. The actual percentage is disputed – with experts quoting figures that range from 50% all the way up to 93% – but what is not in dispute is the fact that what is left unspoken is often as important as what is said.</i></div>
<div style="background-attachment: initial; background-clip: initial; background-color: white; background-image: initial; background-origin: initial; border-bottom-width: 0px; border-color: initial; border-image: initial; border-left-width: 0px; border-right-width: 0px; border-style: initial; border-top-width: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; vertical-align: baseline;">
<i>An almost direct parallel can be drawn with comments in code. Annotations aimed at the programmer but ignored by the compiler can be as important as actual functional code and should be treated with a comparable level of seriousness.</i></div>
<div style="background-attachment: initial; background-clip: initial; background-color: white; background-image: initial; background-origin: initial; border-bottom-width: 0px; border-color: initial; border-image: initial; border-left-width: 0px; border-right-width: 0px; border-style: initial; border-top-width: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; vertical-align: baseline;">
<i><span style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; border-bottom-width: 0px; border-color: initial; border-image: initial; border-left-width: 0px; border-right-width: 0px; border-style: initial; border-top-width: 0px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; vertical-align: baseline;">Should be</span> treated with seriousness; however, unfortunately, they almost never are. Most programmers will confess that their commenting is, at best, patchy and often non-existent. The excuses run from the threadbare to the clichéd; from “I do not have enough time to comment” to “Good code is self-explanatory”.</i></div>
<div style="background-attachment: initial; background-clip: initial; background-color: white; background-image: initial; background-origin: initial; border-bottom-width: 0px; border-color: initial; border-image: initial; border-left-width: 0px; border-right-width: 0px; border-style: initial; border-top-width: 0px; color: #404040; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 13px; line-height: 19px; margin-bottom: 25px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; vertical-align: baseline;">
<i>Fewer programmers will admit to this, but a lack of knowledge of the many uses – and the power – of commenting is widespread. That is what this article intends to address.</i><br />
<a href="http://allthingsoracle.com/how-to-make-comments-the-most-important-code-you-write/">Continue reading at All Things Oracle</a></div>
</div>David Njokuhttp://www.blogger.com/profile/11205924212052603994noreply@blogger.com0tag:blogger.com,1999:blog-6379904090858351676.post-87567432797746454652011-09-14T20:49:00.000+01:002011-12-22T20:15:39.242+00:00All Things Oracle (or LeBron James's Explain Plan)I don't know if you follow basketball, but if you do - and, perhaps, even if you don't - you will know that when, in 2010, the best basketball player in the world, LeBron James, decided to leave the Cleveland Cavaliers he organised a one-hour television show to announce which team he had chosen to join. <i>The Decision</i>, as the 'extravaganza' was called was broadcast on ESPN and got 10 million viewers.<br />
<br />
I was not one of them. But I'm going to assume that if 10 million people - more than the populations of Finland and Norway <i>put together</i> - watched it, it must have featured gorgeous cheerleaders, dressed in a blur of motion and very little else, dancing around, joyfully pumping the air with their pompoms as LeBron explained his plan to join a new team.<br />
<br />
No? No cheerleaders? Not even one? Well, in that case it's really lucky you're here, cos I've got a dozen cheerleaders whooping and dancing all around my tiny flat as I prepare to make this announcement.<br />
<br />
Hey, take your eyes off them! Focus on me.<br />
<br />
I have decided to join the <a href="http://www.allthingsoracle.com/">All Things Oracle</a> team. [Insert fanfare here.]<br />
<br />
This does not mean that I will be abandoning this blog. Quite the contrary, I am hoping that contributing to <i>All Things Oracle</i> will exercise my blog muscles and get me into the habit of blogging here more regularly. My plan is that I will continue to blog about my trials and triumphs with Apex here, while writing about the other facets of Oracle that I am interested in - Oracle Forms and the database in general - over at <i>ATO.</i> (I have already contributed pieces on <a href="http://www.allthingsoracle.com/introduction-to-regular-expressions-in-oracle/">regular expressions</a> and <a href="http://www.allthingsoracle.com/autonomous-transactions-in-oracle/">autonomous transactions</a>.)<br />
<br />
<i>ATO</i> is brought to you by the guys at Red Gate software, and their ambition is to turn it into a hub for Oracle developers and DBAs by publishing original content and by pointing readers in the direction of useful information elsewhere. To achieve this they have assembled a formidable <a href="http://www.allthingsoracle.com/experts/">team of experts</a>: I've studied that list and I cannot see one weak link ... oh, there I am! The fields covered include database tuning, Apex, system architecture, Forms, database administration, warehousing and much more.<br />
<br />
So make sure you add the <i>ATO</i> <a href="http://www.allthingsoracle.com/feed/">rss feed</a> to your reader and that you click on their links whenever you google stuff on bing or bing stuff on google.<br />
<br />
Now please go back to watching the cheerleaders. And enjoy the champagne - it, er, comes from France.David Njokuhttp://www.blogger.com/profile/11205924212052603994noreply@blogger.com1tag:blogger.com,1999:blog-6379904090858351676.post-317365474255018792011-02-27T16:51:00.010+00:002011-02-27T20:07:06.275+00:00Apex & Version Control (Or Dance like Google's watching)Sit back, young one, and listen. You may learn something.<div><br /></div><div>Just the other weekend I went to a party, one of those dance parties with loud, fast music, free-flowing alcohol and the type of beautiful women who wear short skirts, irrespective of the temperature outside. Y'know, just the average weekend for your typical Oracle Application Express blogger.</div><div><br /></div><div>The thing to do, when you find yourself in this situation, is sit back, have a sip of alcohol to soothe your nerves, and survey the scene. Step back into the shadows of evolution: it is 2000BC again and you are a hunter on the plains of the Serengeti overlooking your prey. Choose your quarry and then step onto the dancefloor, making gentle rhythmic moves. Circle her (or him) gradually and then move in, bringing your arms and your head into your dance. Whisper to them, "May I have this dance?"</div><div><br /></div><div>Guaranteed success, young one. Or your money back.</div><div><br /></div><div>Real life is nothing like Hollywood. When you're dancing with(/against) a female in real life you don't have a troupe of other dancers dancing behind you in formation, matching your every move perfectly, like the dancers in <i>Thriller, Step Up 3D</i> or any of <a href="http://www.quora.com/What-are-some-movies-where-dance-is-the-central-theme">these movies</a>. Real life does not naturally come with back up.</div><div><br /></div><div>Which is why, of course, we need to back up our Apex applications ourselves. The question you will have to answer is this: should you back up your full application in one file or back it up page by page?</div><div><br /></div><div>If the application you are building is a website that you publish to the internet then in many ways your challenge is an easy one. You will need a development environment, perhaps a test environment and a production environment. There's no reason for one environment to pollute any of the others.</div><div><br /></div><div>Export your full application after each major code change and back it up wholly. Backing up apex applications page by page is complicated; there's no need to take unnecessary risks.</div><div><br /></div><div>However if, like us, you are creating a huge system which spans multiple apex applications and hundreds of pages which you intend to sell to multiple customers who have divergent upgrade schedules and obliquely differing requirements, then the picture is slightly more complicated. Your application may never be in a state where you can export it fully. You will need to export each page and each shared component as each bit of development is completed. </div><div><br /></div><div>Application Express's versioning capabilities are simplistic at best; you can version the application, but not the page. To keep a record of the various versions of your pages you will need to use the page comments. </div><div><br /></div><div>If you choose to export your pages individually you will need to bear in mind that exporting the page does not export any shared components that you may use on that page. You'll need to do that separately. And, unfortunately, there's no built-in way to export application items. As usual, Apex forces you to dance a merry dance to achieve what you need.</div><div><br /></div><div>Talking about dancing, you'll want to forget that "<i>dance like no one's watching"</i> nonsense. You don't code like no one's going to use your software, do you? This is 2011, my friend; and <a href="http://www.youtube.com/watch?v=GZBf7JD0Sbg">Youtube has a very long memory</a>.</div><div><br /></div><div>Oh, and if you've already built your application and need help exporting your individual pages the following procedure should help. You'll need to run it in SQL*Plus and have set up a directory.</div><div><br /></div><br /><pre name="code" class="SQL"><br />/*<br />** This procedure will write out an apex page.<br />*/<br />create or replace procedure export_apex_pages( p_dir in varchar2, app_id number )<br />is<br />l_thePage htp.htbuf_arr;<br />l_output utl_file.file_type;<br />l_lines number default 999999999;<br />vWorkspace number;<br />vLength number := 0;<br />vFileName varchar2(100);<br />begin<br /><br />-- Find out what the workspace is.<br />for i in (select workspace_id<br /> from apex_applications<br /> where application_id = app_id) loop<br /> <br /> vWorkspace := i.workspace_id;<br />end loop;<br /><br />-- Loop through all the pages for the application.<br />for j in (select page_id<br /> from apex_application_pages<br /> where application_id = app_id ) loop<br /><br /> -- Determine the file name.<br /> vFileName := 'f'||app_id||'_page_'||j.page_id||'.sql';<br /> vLength := 0;<br /><br /> OWA.num_cgi_vars := 0;<br /> apex_util.export_application_page(p_application_id=>app_id,p_workspace_id=>vWorkspace, p_page_id=>j.page_id);<br /><br /> l_output := utl_file.fopen( p_dir, vFileName, 'w' );<br /> l_lines := 99999999;<br /> owa.get_page( l_thePage, l_lines );<br /><br /> for i in 1 .. l_lines loop<br /><br /> utl_file.put( l_output, l_thePage(i) );<br /> <br /> /*<br /> ** We need to calculate the length cos we can only write up to 32k.<br /> ** If it starts getting close to that we need to close the file, and then reopen it.<br /> */<br /> vLength := vLength + length(l_thePage(i));<br /> if vLength > 30000 then -- it's getting close to our limit. Look for a point where we can close it.<br /> if l_thePage(i) like '%'||chr(10) then -- the line ends with a page return. Perfect place to close and reopen the file.<br /> vLength := 0;<br /> utl_file.fclose(l_output);<br /> l_output := utl_file.fopen(p_dir,vFileName,'A');<br /> end if;<br /> end if;<br /> end loop;<br /><br /> utl_file.fclose( l_output ); <br />end loop;<br />end export_apex_pages;<br />/<br /></pre>David Njokuhttp://www.blogger.com/profile/11205924212052603994noreply@blogger.com1tag:blogger.com,1999:blog-6379904090858351676.post-51820525345198757742010-08-22T11:53:00.008+01:002010-08-22T14:56:21.610+01:00Learning Oracle Application Express (or teaching Angelina Jolie the MC Hammer dance)I'm a Londoner so, unsurprisingly, I think London is the greatest city in the world (feel free to disagree if you enjoy being wrong). However I do accept that London isn't always that friendly to foreigners. Other major European cities have street signs and directions in the local language <i>and</i> English, but London seems to say to you, "Speak English - or get lost. Literally."<div><br /></div><div>The Apex blogosphere can be a little like that; we often blog about advanced minutiae, perhaps to the exclusion of absolute beginners. This week 2 of my colleagues started learning Apex and so I've decided to put together a little Apex 101 study guide for their benefit and for that of other Apex padawans. (And if you say that this is only tangentially different from <a href="http://dancingwithapex.blogspot.com/2008/08/making-move-from-forms-to-apex-what.html">a post I wrote 2 years ago</a> my answer is that all blog posts are kinda related; it's like that 6 degrees of separation theory that says, as far as I understand it, that I and Angelina Jolie are practically best friends.)</div><div><br /></div><div><b><u>Where to start:</u><span class="Apple-style-span" style="font-weight: normal;"> There is only one place to start, and that's at the <a href="http://apex.oracle.com">Oracle Apex website</a>. Sign up for a workspace - it's free and easy. Have a look round, press buttons to see what they do: it's like developing with a condom on - safe, without commitment, and, hey, you can even do it drunk. However, the very best way to really dip your toes into the water is to work through Oracle's 2 Day Developers Guide [<a href="http://download.oracle.com/docs/cd/E17556_01/doc/appdev.40/e15516.pdf">PDF link here</a>]. When you're done with that you may wish to return <a href="http://www.oracle.com/technetwork/developer-tools/apex/documentation/index.html">here</a> to flirt with the other tutorials.</span></b></div><div><b><span class="Apple-style-span" style="font-weight: normal;"><br /></span></b></div><div><b><u>Internet resources:</u><span class="Apple-style-span" style="font-weight: normal;"> Did you know that you're only ever 6 clicks away from an Apex blogger? Fact. We're like LOLCATS, adverts for blue pills for men, and YouTube videos of Rick Astley. Fortunately, the <a href="http://www.apexblogs.info">Apex Blog Aggregator</a> has found the best blogs about Apex and, erm, aggregated them. Alternatively, you can go <a href="http://apex.oracle.com/pls/otn/f?p=24793:12:0">here</a> or even <a href="http://www.youtube.com/user/RickAstleytv?blend=2&ob=1">here</a>. </span></b></div><div><b><span class="Apple-style-span" style="font-weight: normal;"><br /></span></b></div><div><b><span class="Apple-style-span" style="font-weight: normal;">Another internet resource that you'll probably want to bookmark is <a href="http://forums.oracle.com/forums/forum.jspa?forumID=137">OTN</a> (which either stands for <i>a.</i> Oracle Technology Network or <i>b.</i> Orangutans Tangoing in the Netherlands. You decide.) The forum's search functionality is more Altavista than Google, but chances are that any question you've got has probably been asked and answered before. </span></b></div><div><b><span class="Apple-style-span" style="font-weight: normal;"><br /></span></b></div><div><b><u>Tutorials:</u><span class="Apple-style-span" style="font-weight: normal;"> The same way that with each regeneration since the first series in 1963, <a href="http://hubpages.com/hub/Doctor-Who-His-11-Faces">Dr Who</a> has come back as someone slightly younger and better looking, each regeneration of Apex has resulted in a slicker, more intuitive IDE. However, I attended the Oracle University's 5 day Apex course and found it very useful. You may also want to have a look at <a href="http://www.skillbuilders.com/oracle-apex/Application-Express-APEX-Consulting-Training.cfm">SkillBuilders</a>. I've attended some of their webinars and they've been excellent. Hey, why don't you put on a pair of geeky glasses, download some of <a href="http://www.skillbuilders.com/oracle-apex/Application-Express-APEX-Consulting-Training.cfm#tabs-4">their past webinars</a> and pretend you're me? Whatever pedals your bike, pal.</span></b></div><div><b><span class="Apple-style-span" style="font-weight: normal;"><br /></span></b></div><div><b><u>Books:</u><span class="Apple-style-span" style="font-weight: normal;"> If you are the type who prefers to learn from books there is a growing catalogue of Apex books out there. Here's <a href="http://www.oracle.com/technetwork/developer-tools/apex/overview/index.html#books">a list</a>. And here are <a href="http://dancingwithapex.blogspot.com/2010/07/oracle-application-express-32.html">two</a> <a href="http://dancingwithapex.blogspot.com/2008/09/pro-oracle-application-express-my-first.html">reviews</a> by a blogger who I respect immensely - and who Angelina Jolie is practically best friends with.</span></b></div><div><b><span class="Apple-style-span" style="font-weight: normal;"><br /></span></b></div><div><b><span class="Apple-style-span" style="font-weight: normal;">And that's it, my friend, all you need to get started. I'm not going to claim that this blog post has changed your life or that you came to me as a boy/girl and are leaving as a man/woman (unless it's taken you 20 years to read these 600 words), but hopefully you now got all the resources you need to hack your way through the Apex jungle. There's a lot more I can show you (have you seen my MC Hammer dance? No?) but my doorbell is ringing. It's probably Angelina.</span></b></div>David Njokuhttp://www.blogger.com/profile/11205924212052603994noreply@blogger.com1tag:blogger.com,1999:blog-6379904090858351676.post-25503799702133272182010-07-19T09:26:00.014+01:002010-07-19T16:41:41.653+01:00Oracle Application Express 3.2: The Essentials and More: A review (or Things I've learned from TV)<div>I have a nephew who is 10. Just the other day he was asking me about ancient Rome and I was halfway through a long description of the lives of gladiators before I realised that all the 'facts' I was giving him had come straight from the TV show, <strong><em>Spartacus: Blood and Sand </em></strong>and not from a history book at all. </div><br /><div>I realised, with shame, that almost everything I think I know comes from television. Ask me about psychology and I've got <strong><em>Lie to Me</em></strong>; ask me about forensic science and I've got <strong><em>CSI</em></strong>; ask me about the inner thoughts of women and I've got <strong><em>Sex and the City;</em></strong> ask me about Oracle Apex...</div><br /><div>A couple weeks ago the good people at Packt Publishers kindly sent me a copy of the newest Apex book on the market, <a href="https://www.packtpub.com/oracle-application-express-3-2/book?utm_source=dancingwithapex.blogspot.com&utm_medium=link&utm_content=blog&utm_campaign=mdb_003631">Oracle Application Express 3.2: The Essentials and More</a> by Arie Geller and Matthew Lyon to review.</div><br /><div>Here's what I think:</div><a href="https://www.packtpub.com/oracle-application-express-3-2/book?utm_source=dancingwithapex.blogspot.com&utm_medium=link&utm_content=blog&utm_campaign=mdb_003631"><img style="TEXT-ALIGN: center; MARGIN: 0px auto 10px; WIDTH: 268px; DISPLAY: block; HEIGHT: 400px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5495577095262636786" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh0mNe_uDW9l1CH3v21jfO6o8rE9BnyNKn1GWl1MnR1CEjJHhC0-NXbdlYsjJU4kGox6KYqQexY370bpZpAXX7UTX0EBjoxbDKOrU3YamBuUGaacO5BbyqK_6AxlFULfqwI69dmc5mN7rSy/s400/apex+book.jpg" /></a><br /><div><u><em>Don't write an expiry date into your name (or what I learned from <strong><a href="http://en.wikipedia.org/wiki/Space:_1999">Space 1999</a></strong>):</em> </u><br /></div><div>There's an elephant in the room; let's ignore it no longer. It is the most unfortunate coincidence of timing that this book on Apex 3.2 has come out the same week we've all been going crazy about Apex 4. Definitely embarrassing. But does this mean that the book, like Benjamin Button, is born already old and out-of-date? In some ways the answer, unfortunately, is yes; but until the market is flooded with Apex 4 books you should not let that put you off this book. The core of Apex remains unchanged, and this book covers that admirably.</div><br /><div><em><u>Sequence does not matter (or what I learned from <strong>Quantum Leap</strong>)</u></em> </div><div>I must admit that I had a serious issue with the sequence in which the authors chose to order their topics. I completely understand their thinking, but when I buy an Apex book I do not want to spend the first 40 pages reading about DOM objects, javascript, CSS and html before the first real mention of Apex, and <em>another</em> 40 pages before I get my first real look at the IDE!</div><br /><div>However, I understand that technical books are not necessarily meant to be read sequentially. If you buy this book feel free to skip straight to page 79; you can return to the earlier pages later. And you should, because a lot of it is actually informative (I, personally, needed to read the section on shortcuts on page 63). </div><br /><div><em><u>People talk about what they know (or what I learned from <strong>House</strong>)</u></em><br /></div><div>"If you go to an oncologist with a headache," Dr House says in one episode, "he'll diagnose cancer; but take that same headache to an optician and he'll recommend new glasses." </div><br /><div>This book is full of references to right-to-left languages and Apex's globalisation abilities. I'm guessing this is something one or both of the authors are interested in. If this is functionality that you require then you really must buy this book. </div><br /><div><em><u>The story is in the details (or what I learned from <strong>The Wire)</strong></u></em><br /></div><div>Reading this book I made a list of subjects I felt the authors covered very well, and of those capabilities of Apex that I - not a newbie in Apex but far from an expert - was discovering for the first time. And I'm pleased to report that this list was much longer than I expected it to be. From whole chapters like the very useful section on Best Practices (Chapter 24: a must-read for any newbie) and the chapter on Deployment (Chapter 20), to little things using <em>$v</em> in Apex Ajax instead of <em>$x(itemName).value</em>.</div><br /><div>There's more. Their work on the APEX_ITEM api was exhaustive (I did not know about APEX_ITEM.MD5_CHECKSUM). And, as an Oracle Forms developer, I was very interested in Chapter 23, which is about migrating applications from Forms. I got the impression that this was not necessarily something that interested the authors as much as it interested me, but it is good to find a book that covers the subject. </div><br /><div><em><u>Nothing's perfect (or what I learned from <strong>Lost</strong>)</u></em><br /></div><div>The chapter on migration wasn't the only time I felt that perhaps the authors were bored with a particular aspect of their subject. The whole section on the Apex IDE was often merely descriptive rather than explanatory. </div><br /><div>In addition, I understand that they needed to include a section on the Apex SQL Workshop for completeness, but if you're an Oracle developer and you don't use TOAD, PL/SQL Developer, SQL Developer or even SQL*Plus then you really shouldn't be over here playing with the big kids. Apologise to the rest of the class and then leave the room immediately.</div><br /><div><em><u>Conclusion</u></em> </div><div>As I said earlier we cannot ignore the fact that Apex 4 casts its wide shadow over this book. Only you can decide how important a factor this is to you. Outside of that fact, this is quite a good book: if you are a newbie it will not get you started on its own - for that you will need the Internet's resources (start here: <a href="http://apex.oracle.com/i/index.html">apex.oracle.com</a>) - however, the authors' approach to Apex is often quite theoritical (and not just brutishly practical as many technical books can be) and this will furnish you with the background information you will need if you wish to do more than merely dabble with this technology. </div><br /><div>If you are an experienced Apex developer the answer to the question "should I buy this book?" depends largely on your attitude to technical books. Personally, I like them. Google has pretty much killed off technical reference books, but I think there is still room for books like this that give you more information than a cursory web search can. </div><br /><div>I'd love to tell you more, but I've got to go watch <strong>Jerry Springer.</strong> My nephew might have some questions about the life of the average, normal American.</div>David Njokuhttp://www.blogger.com/profile/11205924212052603994noreply@blogger.com1tag:blogger.com,1999:blog-6379904090858351676.post-17627464855147301122010-06-24T17:38:00.015+01:002010-06-24T23:26:17.480+01:00Should you upgrade to Apex 4.0? (or Apex - with extra Katherine Heigl!)There comes a time in the life of every man (and be warned that I spend all my time outside of work watching cheesy Hollywood romcoms and this might have warped my sense of reality) when his wife comes to him and says these words:<br /><br />"Do you think I should get breast implants?"<br /><br />In that instant the man is faced with the most crucial of dilemmas: it's not that he doesn't love his wife (who, in a Hollywood romcom, will inevitably be played with cutesy kookiness by Katherine Heigl), but surely an upgrade is <span style="font-style: italic;">always</span> a good thing? After the implants she won't just be his darling Katherine Heigl, she'll be be darling Katherine Heigl - <span style="font-style: italic;">with extra Katherine Heigl!</span><br /><br />Developers all across Apexdom are waking up to the same dilemma this morning, because of the following bit of news:<br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgL3_ezAPZfIDOkN-xBeNiLNwBibLSdKp7RnFW_t6S1qv403wuvKSMoa7oqB3Alt0J_stCO47iQOifnN9sWzbimmSrN_AJlOUKv_rGW9bIeJnB6jZAvqzgvn4q_LQ_pLFoEWguvobgk283W/s1600/apex4.JPG"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 400px; height: 94px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgL3_ezAPZfIDOkN-xBeNiLNwBibLSdKp7RnFW_t6S1qv403wuvKSMoa7oqB3Alt0J_stCO47iQOifnN9sWzbimmSrN_AJlOUKv_rGW9bIeJnB6jZAvqzgvn4q_LQ_pLFoEWguvobgk283W/s400/apex4.JPG" alt="" id="BLOGGER_PHOTO_ID_5486387167879073378" border="0" /></a><br />That's right, Oracle Application Express 4.0 has abandoned the shy coyness of beta mode and is finally fluttering its flirty eyes at us as a full release. Come and get me, boys (and girls), it seems to be saying. We've all always loved Apex - but Application Express 4.0 is Apex, in a manner of speaking, <span style="font-style: italic;">with extra Katherine Heigl!<br /><br /><span style="font-style: italic;"><span style="font-style: italic;"></span></span></span>Over the past 2 years my company has strengthened our commitment to Apex. It is now the first solution we look to for every project we get and, despite the fact that Oracle still seem intent on marketing it as an lightweight means of pimping your Excel spreadsheets, it has been able to cope with everything we have thrown at it.<br /><div><br /></div><div>Apex 4.0 wears its bling with ease: its UI has been tarted up impressively; its <i>Dynamic Actions</i> is a new, simple declarative interface for the creation of javascript/jQuery; its <i>Plug-ins</i> is unquestionably the first step in bringing an iTunes-style 'app store' that will enable developers share clever bits of code to Apex; and its <i>Websheets</i> gives developers the ability to easily add Google Docs-style collaboration to their applications. Oh, and interactive reports, the crown jewel of Apex's previous incarnation has been spruced up too. All in all, Application Express 4.0 is definitely a huge step forward from version 3.2.</div><div><br /></div><div>So should we upgrade immediately? Should you?</div><div><br /></div><div>In our Hollywood romcom Katherine Heigl will probably <i>not</i> get those breast implants. Instead she'll learn, through a hilarious sequence of events triggered by an encounter with a wise, old Chinaman, to love who she is <i>inside.</i> But, gorgeous as she is, you should be ashamed of yourself - why are you letting Ms Heigl take your technical decisions for you? </div><div><br /></div><div>Here are the questions we are asking ourselves to help us decide whether to upgrade:</div><div><ol><li>Can we upgrade? <i>4.0 requires, at the very least, a 10.2.0.3 database. Our development environment is a 9.2 database (we choose to develop using a database version that matches that of our most backward customer). Before we upgrade we'll need to sort this out.</i></li><li>What are the risks of upgrading? <i>It is rarely wise to be the first guy to scream <a href="http://www.youtube.com/watch?v=LkCNJRfSZBU">Leeroy Jenkins!</a> and go rushing in to upgrade. It's usually wiser to let others make mistakes and then gingerly step over their bodies. </i></li><li>What are the risks of <b>not</b> upgrading? <i>Minimal, in the short run. In the long run, of course, you don't want to fall too far behind.</i></li><li>Do you need to upgrade? <i>Of course not. Apex 3.2 was a fantastic product and I assume that you are happy with the applications you have built with it. But 'need' is one thing, 'want' is another. The wheel was a great invention - but hey, wasn't it a sweet thing that Mr Dunlop improved it by wrapping it in rubber? Technology creates its own need: I bet that in a year you won't be able to imagine Apex development without plug-ins.</i></li><li>How easy is it to upgrade? <i>Having not upgraded myself I can only pass on hearsay. I have heard that it's as easy as pie. However, do remember that you will need to upgrade the environments of all your clients too if yours is an application that is deployed in multiple locations.</i></li></ol><div>So here's what I would advise: wait a little - not too long - and then update your development environment to 4.0. Play around with it (or, if you prefer, play around with it at <a href="http://apex.oracle.com">http://apex.oracle.com</a>) and discover how it can make your applications better. </div></div><div><br /></div><div>So I guess what I'm saying is this: Yes, Katherine Heigl, <i>do</i> get your implants - but perhaps, one breast at a time?</div>David Njokuhttp://www.blogger.com/profile/11205924212052603994noreply@blogger.com4tag:blogger.com,1999:blog-6379904090858351676.post-33466219800577756512010-02-28T23:33:00.003+00:002010-02-28T23:54:18.102+00:00The coming of Apex 4 (or 12 kilometres of features)Take me to Bloggers Square and have all the other Apex Bloggers hurl rotting vegetables at me, because I have barely had a moment to glance at Apex EA4. Work gets in the way, unfortunately. However, what I have managed to see of it so far is impressive. Actually, it's a lot more than merely <i>impressive</i>, it's ... exciting. And how often do we get to say that about technology that doesn't have a name that begins with a lowercase <i>i</i>?<div><br /></div><div>Anyway, I thought I should do my own little thing towards building up anticipation of this new version of Apex. And so, without further ado, as the vicar said to the actress: Here's my little thing...</div><div><br /></div><br /><object width="425" height="344"><param name="movie" value="http://www.youtube.com/v/S7cb263VaNc&hl=en_GB&fs=1&"><param name="allowFullScreen" value="true"><param name="allowscriptaccess" value="always"><embed src="http://www.youtube.com/v/S7cb263VaNc&hl=en_GB&fs=1&" type="application/x-shockwave-flash" allowscriptaccess="always" allowfullscreen="true" width="425" height="344"></embed></object>David Njokuhttp://www.blogger.com/profile/11205924212052603994noreply@blogger.com3tag:blogger.com,1999:blog-6379904090858351676.post-28749123533240967072010-01-19T19:54:00.008+00:002010-01-19T22:52:35.137+00:00Calling stored procedures from apex pages (or The Da Vinci PL/SQL Code)Hollywood director Ron Howard had a problem. He'd been given the bestselling book in the world to turn into a movie. But while <i>The Da Vinci Code</i> was undoubtedly a page-turner, it did not readily lend itself to celluloid. After all, the story was about a professor of religion - not about a wisecracking, shoot-first-ask-questions-later action hero who likes to blow stuff up and make love to beautiful women. How do you make that exciting? <div><br /></div><div>What Ron Howard did was this: he played loud, suspenseful mood music even in scenes where Tom Hanks is merely racing through cathedrals or reading books in the library. How else could he make a film about Roman catholic history seem exciting?</div><div><br /></div><div>Last week, a colleague of mine was heading to a client's site for an important demo of one of our Apex applications. Sitting in the back of a taxi, 10 minutes away from the meeting, he tested the application by pressing a button and then ...</div><div><br /></div><div><i>[insert loud suspenseful music here]</i></div><div><br /></div><div>- an error!</div><div><br /></div><div><b>HTTP 403</b></div><div><b>Forbidden</b></div><div><b>The requested operation is not allowed.</b></div><div><br /></div><div>He was now 7 minutes away from a demo which could make or lose our company half a million pounds... 6 minutes away ... 5 minutes...</div><div><br /></div><div><i>[more suspenseful music ... drums that sound like heartbeats ... ]</i></div><div><br /></div><div>Tom Hanks quickly consults Google; it spits back a million unhelpful results ("want to buy cheap meds from Canada?") <i>... 4 minutes ... 3 minutes...</i></div><div><br /></div><div>And then he read about the <i>wwv_flow_epg_include_mod_local</i> function. </div><div><br /></div><div>Here's the deal with this function. It is in your <i>Flows_xxxxx</i> schema and if you wish to execute a stored procedure directly from your URL (<i>http://.../apex/schemaName.procedureName</i>) you need to edit this function, explicitly naming the stored procedures that you wish to run. Specific details of how to do this are available <a href="http://bit.ly/92fFWT">here</a>.</div><div><br /></div><div><i>... 2 minutes to deadline ... 1 minute ... 30 seconds...</i></div><div><br /></div><div>Tom Hanks quickly edits the function. He comments out the apposite sections and adds his procedure name to the list. He executes the function. <i>[... 15 seconds ... 10 seconds ...]</i> </div><div><br /></div><div>It works.</div><div><br /></div><div><i>... 0 seconds ...</i></div><div><br /></div><div><b><i>The End.</i></b></div><div><br /></div><div><span class="Apple-style-span" style="text-decoration: underline;">Epilogue:</span> My colleague is happy to report that the demo went well and we are in with a good chance of winning the contract. His name is not really Tom Hanks. (It is Steven Seagal.)</div><div><br /></div><div>At fault for this problem, of course, is Oracle. Apex is undoubtedly their most developer-friendly tool, but it is mind-boggling that there isn't a declarative way of updating the <i>wwv_flow_epg_include_mod_local</i> function. Also since it resides in the Flows schema it is the easiest thing in the world to update it in the production environment but forget to make the same changes when deploying at a client site (after all, the function is not exported with your application). </div>David Njokuhttp://www.blogger.com/profile/11205924212052603994noreply@blogger.com6tag:blogger.com,1999:blog-6379904090858351676.post-84364162185131715902009-12-31T14:20:00.009+00:002010-01-05T13:58:05.829+00:00Oracle Forms v Apex (or Please Lean Forwards Jennifer Lopez)It is the 31st of December, 2009. The sun is setting on the year, the lifeforce is draining from the decade. Across the globe people are assessing the past and preparing for the future. And newspapers are overflowing with important newsstories like <span style="font-style: italic;">The 10 Best Celebrity Outfits of the Decade.</span><br /><br />At this time of year it's obvious what the author of an Oracle Apex blog will write about. Surely it'll be an indepth article on how Apex can take over the world in the next decade, replete with annotated footnotes. Or maybe I'll write exhaustively about Apex 4.0, outlining the bright future it heralds for all Apex developers or bemoaning it as an opportunity lost. Right? Right? Wrong.<br /><br /><span style="font-style: italic;">[By the way, in case there are any journalists reading, the</span><span style="font-style: italic;"> most important celebrity outfit of the decade was <a href="http://www.glamourvanity.com/images/jennifer-lopez-dress-42nd-grammy-awards-2000.jpg">the Versace dress</a> </span><span style="font-style: italic;">Jen</span><span style="font-style: italic;">nifer Lopez wore to the Grammy's in 2000 because it declared that the new decade would be one of daring, outrageous fashion. Trust me, I know these things (because I just read it in <span style="font-weight: bold;">Cosmopolitan</span><span style="font-style: italic;"> magazine).</span></span>]<br /><br />Instead, I'd like to talk about the technology that has bookended the decade for me. In 2000 I got my first job developing with Oracle Forms 4.5, and these past few months, instead of developing in Apex, I have been converting my company's (massive) Oracle Forms application from client/server to the web.<br /><br />I know I've been very disparaging of Oracle Forms in this blog - but you know what? It's been an utter joy. I and my team were able to transform the application from <span style="font-style: italic;">drab to fab</span> (I'm quoting <span style="font-style: italic;">Cosmo</span> again. Sorry).<br /><br />We've taken it from this:<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjlWL_GIhmaGadbH9evhbUThihzb2r1l9uLqTfB3hlQBhip9ERafjSUh75wkWyOOsFiL-CUzcz5Dm4PiNZVORSGAapkmC3PwZ6lv0WHLGu8hQO6R9TGVY-y2xXfFpPiY8BS-1TL8_6i-dV3/s1600-h/old+impulse.JPG"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 320px; height: 204px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjlWL_GIhmaGadbH9evhbUThihzb2r1l9uLqTfB3hlQBhip9ERafjSUh75wkWyOOsFiL-CUzcz5Dm4PiNZVORSGAapkmC3PwZ6lv0WHLGu8hQO6R9TGVY-y2xXfFpPiY8BS-1TL8_6i-dV3/s320/old+impulse.JPG" alt="" id="BLOGGER_PHOTO_ID_5421420044928376738" border="0" /></a><br /><br /><br />To this:<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgSMhqOMqik-lmRkd-0lwFqh6fIgcBFCafjVVe-dE_BHDJPqtt_eYYKAhLKKJCKcL6eGqjuY7AT42fL4BR3ceReyUXI0Fqp_ZCCCNpQIH2CpfYWXdnMh4nSkjtoqIMOVBXdivWwU0CuJ5HT/s1600-h/new+impulse.JPG"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 320px; height: 206px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgSMhqOMqik-lmRkd-0lwFqh6fIgcBFCafjVVe-dE_BHDJPqtt_eYYKAhLKKJCKcL6eGqjuY7AT42fL4BR3ceReyUXI0Fqp_ZCCCNpQIH2CpfYWXdnMh4nSkjtoqIMOVBXdivWwU0CuJ5HT/s320/new+impulse.JPG" alt="" id="BLOGGER_PHOTO_ID_5421419607324424434" border="0" /></a><br />Along the way we cursed Oracle (again) for their shambolic documentation (want to read up about <span style="font-style: italic;">set_custom_property</span>? Well, tough!). Eventually, we discovered that the internet (especially <a href="http://sheikyerbouti.developpez.com/forms-pjc-bean/LAF/doc/Oracle_Forms_Look_and_Feel_project.htm">The Forms Look and Feel Project</a>, <a href="http://forms.pjc.bean.over-blog.com/">the PJC Community</a> and <a href="http://www.degenio.com/frite/">FRITE</a>) was our best resource. Oftentimes we had to scale back our ambitions (giving our canvases a nice textured look made the form flicker unacceptably when loading). But eventually, we ended up with a product that we are proud of.<br /><br />I won't go into further detail because this is, after all, an Oracle Apex blog - and Tiger Woods has taught me that it's not wise to publicly cheat on your first love. However, if you are interested in modernising your Oracle Forms do feel free to drop me an email.<br /><br />So in conclusion, if I was given a choice between moving client/server forms to Apex or Oracle web forms, what would I choose? It depends. Moving them to Oracle web forms is definitely easier and you will end up with a product that you'll be proud of. But. But it'll still be an applet (<span style="font-style: italic;">uurgh!</span>) and I still believe that Apex is more future-proof. Yes, Oracle Forms has opened the door to the world of Java, but Oracle Apex opens the door to the whole world.<br /><br />Somehow I think that come 2019, I'm more likely to be writing about Apex. Forms will be forgotten.<br /><br />EDIT: <a href="http://www.guardian.co.uk/lifeandstyle/gallery/2010/jan/05/review-decade-fashion?picture=357646603">This story</a> appeared in the British Guardian newspaper a couple of days after I first wrote this post. Hmm, maybe fashion journalists <span style="font-style: italic;">do</span> read this blog after all!David Njokuhttp://www.blogger.com/profile/11205924212052603994noreply@blogger.com3