Most of us started programming using the Edit -> Compile -> Link -> Test cycle. Whether we edited our source code in a simple editor like vi or in a development environment like Eclipse, we always had a source code file that we could print, or share with co-workers, or save in a repository. The source code was a record of exactly how we solved a particular problem.
In declarative programming environments like APEX, we don't have any source code. We develop pages by working through the APEX wizards in a browser window. Yes, we could export a page; but the resulting exported code isn't intended for mere human readers, and it's certainly not a road map to developing a similar page. Someone reading the export could not easily see what choices the developer made, or when the developer took the defaults.
So, how can we document what the APEX developer sees as they work through the widgets? We could take a lot of screen shots and paste them into an MS Office or OpenOffice document. Many of the APEX wizards display a page that would take four or five screenshots to document; pasting all the snapshots into another document is a pretty clumsy process. We need a way to capture the whole browser page in fell swoop.
Printing the browser page does a good job of document everything on the page, but rather than printing to paper, we can print to the Microsoft XPS printer. XPS is a device-independent document format describing the layout and properties of a document using XML. The Microsoft XPS printer prompts the user to save the XML document in a file, and these files can be viewed using a XPS viewer.
The XPS files aren't source code, but in a declarative programming environment, they're the next best thing. Now you never need to wonder again how you defined a control, configured security, or defined a region. Just print the definition as an XPS file. Be sure any text areas are large enough to display all code that you entered, and then print the page, choosing XPS as your printer. Save the XPS file with a descriptive name, and you will have saved a record of how you defined the object. Now we have file that we can view later, share with co-workers, or save in a repository.
Showing posts with label Oracle APEX. Show all posts
Showing posts with label Oracle APEX. Show all posts
Sunday, February 9, 2014
Monday, November 25, 2013
Favicons
Many years ago, I posted pictures of our bulldog Petunia and her daughter Cactus on the web. I hosted the site on my Linux PC running Apache, PHP, and MySQL. One of the things I did for this site was create a favicon using a head shot of Petunia. It was pretty cute.
Fast-forward ten years, and it's time to create a favicon again. In the meantime, I've forgotten where to put the favicon in the HTML code; in fact, I've forgotten what it is called. A quick search on the Wayback Machine wasn't much help; the venerable internet archive didn't capture a lot of the database-generated content of my old website.
After a bit googling and poking through code on other people's web pages, I'm back in business. So, to start: it's called a favicon, short for "favorite icon". It's the little image file that appears to the left of the page's title in a browser tab, or to left of the page's title in the browser's bookmarks. Look for the "LL" to the left "Lost Learnings" in the title to see this blog's favicon.
Next, where do we put it? The html header has the favicon location. The image should be called favicon.ico, and depending where your images are located, the code will look something like this, assuming our images are loading in the the images directory:
If we're working on our personal website, then we can use an image editing program like GIMP to create a favicon. GIMP is a very powerful image editor with lots of features. If you use GIMP to create a favicon, there are a couple of things to keep in mind. First, the favicon.ico file should be small and square. I have found that 32x32 pixels works well; it's small, but has enough detail to avoid ragged edges. Second, before we save our image, we need to flatten it so that the image does not have multiple layers. Finally, export the file as favicon.ico. This is the required name, and GIMP will convert the file during the export process.
I would like to add a favicon to my pages on Oracle's APEX demo site, so let's see how we would do this in APEX. First, we need to upload the favicon.ico image file:
Now we're ready to view our pages with their favicons. If the favicon does not appear, clear the browser's cache or use shift-refresh to see the favicon.
Fast-forward ten years, and it's time to create a favicon again. In the meantime, I've forgotten where to put the favicon in the HTML code; in fact, I've forgotten what it is called. A quick search on the Wayback Machine wasn't much help; the venerable internet archive didn't capture a lot of the database-generated content of my old website.
After a bit googling and poking through code on other people's web pages, I'm back in business. So, to start: it's called a favicon, short for "favorite icon". It's the little image file that appears to the left of the page's title in a browser tab, or to left of the page's title in the browser's bookmarks. Look for the "LL" to the left "Lost Learnings" in the title to see this blog's favicon.
Next, where do we put it? The html header has the favicon location. The image should be called favicon.ico, and depending where your images are located, the code will look something like this, assuming our images are loading in the the images directory:
<head> <!-- Other head tags --> <link href="images/favicon.ico" type="image/ico" rel="shortcut icon" /> </head>Where do we get a favicon? If we're working on a corporate website, there's usually a corporate favicon that we use. Browsing the HTML header of our corporate pages with Firebug or Chrome's developer tools will quickly reveal the location of the corporate favicon.
If we're working on our personal website, then we can use an image editing program like GIMP to create a favicon. GIMP is a very powerful image editor with lots of features. If you use GIMP to create a favicon, there are a couple of things to keep in mind. First, the favicon.ico file should be small and square. I have found that 32x32 pixels works well; it's small, but has enough detail to avoid ragged edges. Second, before we save our image, we need to flatten it so that the image does not have multiple layers. Finally, export the file as favicon.ico. This is the required name, and GIMP will convert the file during the export process.
I would like to add a favicon to my pages on Oracle's APEX demo site, so let's see how we would do this in APEX. First, we need to upload the favicon.ico image file:
- From Shared Components, click on Images link under Files
- Next, click on the Create button
- Select No Application Associated to make the image available to all applications within the workspace, or select a specific application ID.
- Use the Browse button to find the favicon.ico on your computer
- Click the Upload button
<link href="#APP_IMAGES#favicon.ico" rel="shortcut icon" type="image/ico" /> <link href="#WORKSPACE_IMAGES#favicon.ico" rel="shortcut icon" type="image/ico" />Finally, let's add the favicon to our blog on blogspot.com. From the blog's main page, click on the Layout link. In the upper left corner of the layout, find the region labeled Favicon. Click the Edit link, upload the favicon.ico file using the Browse button, then click the Save button.
Now we're ready to view our pages with their favicons. If the favicon does not appear, clear the browser's cache or use shift-refresh to see the favicon.
Sunday, November 17, 2013
Commitment: Commits in APEX
Commitment is such a loaded word these days. Maybe we humans can't quite get it right, but certainly we can count on our computers to get it right. After all, they're binary machines, 0 or 1, false or true: absolute logic. Or can we?
While debugging a PL/SQL block in APEX, I noticed that the code was marking the input data as processed, even if the procedure terminated with a raise_application_error. I was focused on debugging the error causing the raise_application_error, so I didn't care that the input was marked as processed. After fixing the problem, then it occurred to me -- wait, a minute -- why was the input marked as processed when the transaction terminated in error?
When we do transaction processing, we expect the transaction to be atomic. Either all parts of the transaction goes in, or none of the transaction goes in. Imagine going to the ATM machine, moving $50 from savings to checking, and the move fails in the middle of the financial transaction. That would leave the savings debited $50, nothing credited to checking, and $50 floating in the bit-ether. If we do transaction processing correctly, then either the money is successfully moved, or the money is left in savings.Nothing is left half-done.
Back to Apex. I clearly did not have a correctly-working transaction. There was nothing in my procedure that would do a commit. But, somewhere, somehow, something was committing the database changes, even though I didn't expect it to.
There's a lot going on here. Partly it's semantics and syntax. My APEX code was not doing anything I hadn't done in COBOL/DB2 with host variables, and the COBOL/DB2 transactions always worked correctly. Partly, it's an understanding, or misunderstanding of what's happening in Apex. Let's start with syntax and semantics first.
Imagine we have COBOL/DB2 program and some PL/SQL in an Apex block. Both code snippets will do the same thing. Here's the COBOL:
This isn't an Oracle/DB2 issue. Login to an Oracle SQLPLUS session, and try the PL/SQL code snippets in SQLPLUS. The SQLPLUS session yields the same results as the COBOL test.
Syntax and semantics: in an ideal world, there's a tight correlation between syntax and semantics. We would like things that look the same to behave the same. Our variables, :WS-DUMMY and :p1_dummy, look like two ordinary "host variables". An astute reader of Apex documentation will call :p1_dummy a "session variable", and that's our first clue. Session variables may look like host variables (syntax is the same), but session variables do not behave like host variables (semantics differ). Mostly they do behave the same, with an important exception: changing a session variable causes APEX to take a commit.
Back to "adds a row": only one row is added. If we run our code snippet in Apex a few times, and examine the table, we find there is one row. Why aren't there a few rows? After all, didn't we select data into :p1_dummy each time? That's the second point: changing a session variable causes APEX to take a commit. If we move the same value into a session variable, if the value does not change, then APEX does not take a commit. This means that our code will behave differently depending on the data!
For gremlins, this is a good thing. For developers, this is not so good. Here are a few tips to avoiding obscure errors caused by buggy transaction processing:
The Oracle forums are another good source of information. According to an Oracle employee responding to a question in the Oracle forums:
Our pages often include some simple items(text boxes, select lists, date pickers, etc.) plus a tabular form for repeating elements. If we're going to process the page as one transaction properly, we need to know if there are any implicit commits in the APEX Page Processing post-submit processes. If we create a tabular region, we find that Apex adds two post-submit processes: ApplyMRU and ApplyMRD.
Ideally, we should be able add our post-submit process and have all of the post-submit process run as one transaction. Either everything is committed, or none of it committed. We do not want half a transaction.
In the absence of good documentation to guide us, let's do some testing. We'll test in APEX 4.2, the results may differ in other versions. We can test for implicit commits by building a one-page applicaton with a tabular region on the DEMO_CUSTOMER table. For our first test, let's try some inserts and updates. Our three post-submit processes will be:
Click the Submit button, wait for the page to redisplay, and the envelope, please: Any updates or added rows are committed. The rollback has no effect; any updates or added rows are committed. This is not the behavior we expect. We expect a rollback to undo any inserts or updates from ApplyMRU.
For our second test, let's try some deletes. We'll move our rollback step after the ApplyMRD. Our three post-submit processes will look like this:
Check a few delete boxes, click the Delete button, confirm that we want to delete the rows, wait for the page to refresh, and the envelope, please: APEX displays a message saying the rows are deleted, but in fact, the deleted rows are still in the table. The rollback worked as we expected.
There are two important points: First, ApplyMRU takes an implicit commit. Second, ApplyMRU and ApplyMRD do not behave the same way! ApplyMRU and ApplyMRD are not executed at the same time, their process is driven by either the Submit button or Delete button, so we don't need to worry about running them together if the tabular form is the only updatable elements on the page.
But we do need to be careful if we run other processes after the submit button is clicked. If ApplyMRU runs first and commits changes, and if a second process runs and fails, then we have an half-completed transaction. Clearly, that's a bad thing. So, to the list of suggestions above, let's add two more:
While debugging a PL/SQL block in APEX, I noticed that the code was marking the input data as processed, even if the procedure terminated with a raise_application_error. I was focused on debugging the error causing the raise_application_error, so I didn't care that the input was marked as processed. After fixing the problem, then it occurred to me -- wait, a minute -- why was the input marked as processed when the transaction terminated in error?
When we do transaction processing, we expect the transaction to be atomic. Either all parts of the transaction goes in, or none of the transaction goes in. Imagine going to the ATM machine, moving $50 from savings to checking, and the move fails in the middle of the financial transaction. That would leave the savings debited $50, nothing credited to checking, and $50 floating in the bit-ether. If we do transaction processing correctly, then either the money is successfully moved, or the money is left in savings.Nothing is left half-done.
Back to Apex. I clearly did not have a correctly-working transaction. There was nothing in my procedure that would do a commit. But, somewhere, somehow, something was committing the database changes, even though I didn't expect it to.
There's a lot going on here. Partly it's semantics and syntax. My APEX code was not doing anything I hadn't done in COBOL/DB2 with host variables, and the COBOL/DB2 transactions always worked correctly. Partly, it's an understanding, or misunderstanding of what's happening in Apex. Let's start with syntax and semantics first.
Imagine we have COBOL/DB2 program and some PL/SQL in an Apex block. Both code snippets will do the same thing. Here's the COBOL:
EXEC SQL
INSERT INTO MYTABLE(MY_COLUMN) VALUES(1)
END-EXEC.
EXEC SQL
SELECT DUMMY
INTO :WS-DUMMY
FROM SYSIBM.DUMMY1
END-EXEC;
EXEC SQL
ROLLBACK
END-EXEC.
Now lets look at the PL/SQL code in APEX. Each of these anonymous blocks runs in its own APEX process in the same page after the page is submitted.
begin insert into mytable (my_column) values(1); end; begin select dummy into :p1_dummy from dual; end; begin rollback; end;Well, that's pretty simple. Except for wrapping the PL/SQL inside an anonymous block and wrapping the COBOL up for the DB2 pre-processor, these code snippets should do the same thing. But they don't. Run each piece of code several times. The COBOL example will never add a row to MYTABLE, while the ORACLE APEX page adds a row to MYTABLE. Notice I said "adds a row", but more on that later.
This isn't an Oracle/DB2 issue. Login to an Oracle SQLPLUS session, and try the PL/SQL code snippets in SQLPLUS. The SQLPLUS session yields the same results as the COBOL test.
Syntax and semantics: in an ideal world, there's a tight correlation between syntax and semantics. We would like things that look the same to behave the same. Our variables, :WS-DUMMY and :p1_dummy, look like two ordinary "host variables". An astute reader of Apex documentation will call :p1_dummy a "session variable", and that's our first clue. Session variables may look like host variables (syntax is the same), but session variables do not behave like host variables (semantics differ). Mostly they do behave the same, with an important exception: changing a session variable causes APEX to take a commit.
Back to "adds a row": only one row is added. If we run our code snippet in Apex a few times, and examine the table, we find there is one row. Why aren't there a few rows? After all, didn't we select data into :p1_dummy each time? That's the second point: changing a session variable causes APEX to take a commit. If we move the same value into a session variable, if the value does not change, then APEX does not take a commit. This means that our code will behave differently depending on the data!
For gremlins, this is a good thing. For developers, this is not so good. Here are a few tips to avoiding obscure errors caused by buggy transaction processing:
- During development, turn on DEBUG. Examine the debug logs, looking for text like Session State: Saved Item "P1_DUMMY" New Value="". Anytime we see this, APEX has taken a commit.
- Use local variables declared in the anonymous block rather than session variables. Use session variables to store values that will be displayed on the web page, or to save values that you need between page submits. Otherwise, use local variables.
- If you pass session variables as IN OUT arguments to an external procedure, changing the parameter values in the procedure is the same as changing the session variables.
- APEX only performs commits after a process is run. If you find an error, rollback the transaction and set an error before your process exits. This is true for both anonymous blocks in APEX and external procedures called by APEX.
- If you need a commit, explicitly code the commit. Do not assume that APEX will take a commit just because the procedure set a session variable. If the value of the session variable didn't change, APEX won't take a commit.
- Be aware of how APEX processes tabular forms (more on that below).
The Oracle forums are another good source of information. According to an Oracle employee responding to a question in the Oracle forums:
Commits are issued when your code does so explicitly, when a page show or accept request reaches the end, or when anything within your code causes session state to be saved. Session state -altering actions include PL/SQL assignments to (or "select into") bind variables which are page- or application-item references, calls to session-state updating APIs, and page or application computations.
Our pages often include some simple items(text boxes, select lists, date pickers, etc.) plus a tabular form for repeating elements. If we're going to process the page as one transaction properly, we need to know if there are any implicit commits in the APEX Page Processing post-submit processes. If we create a tabular region, we find that Apex adds two post-submit processes: ApplyMRU and ApplyMRD.
Ideally, we should be able add our post-submit process and have all of the post-submit process run as one transaction. Either everything is committed, or none of it committed. We do not want half a transaction.
In the absence of good documentation to guide us, let's do some testing. We'll test in APEX 4.2, the results may differ in other versions. We can test for implicit commits by building a one-page applicaton with a tabular region on the DEMO_CUSTOMER table. For our first test, let's try some inserts and updates. Our three post-submit processes will be:
- ApplyMRU
- Rollback
- ApplyMRD
Click the Submit button, wait for the page to redisplay, and the envelope, please: Any updates or added rows are committed. The rollback has no effect; any updates or added rows are committed. This is not the behavior we expect. We expect a rollback to undo any inserts or updates from ApplyMRU.
For our second test, let's try some deletes. We'll move our rollback step after the ApplyMRD. Our three post-submit processes will look like this:
- ApplyMRU
- ApplyMRD
- Rollback
Check a few delete boxes, click the Delete button, confirm that we want to delete the rows, wait for the page to refresh, and the envelope, please: APEX displays a message saying the rows are deleted, but in fact, the deleted rows are still in the table. The rollback worked as we expected.
There are two important points: First, ApplyMRU takes an implicit commit. Second, ApplyMRU and ApplyMRD do not behave the same way! ApplyMRU and ApplyMRD are not executed at the same time, their process is driven by either the Submit button or Delete button, so we don't need to worry about running them together if the tabular form is the only updatable elements on the page.
But we do need to be careful if we run other processes after the submit button is clicked. If ApplyMRU runs first and commits changes, and if a second process runs and fails, then we have an half-completed transaction. Clearly, that's a bad thing. So, to the list of suggestions above, let's add two more:
- Order post-submit process so that ApplyMRU and ApplyMRD run after any other process. If the early processes raise an error, the ApplyMRU and ApplyMRD will not run, and there will not be a half-completed transaction.
- In some cases, it will make more sense to split a page into two (or more pages), and save all the database processing until the last page. APEX can save session variables on the first pages, and the session variables are available on the last page when all the database updates occur.
Tuesday, November 12, 2013
Quirks Mode
"The good thing about standards is that there are so many to choose from." I had a good laugh when I read that years ago. Andrew Tanenbaum, in his book Computer Networks, is quite correct. It was true of computer networks twenty years ago (remember SNA, DECNet, Arpanet, and BITNET, just to name a few), and it's true of web standards and web browsers today.
After laying out a page with <div> tags and testing the page with Internet Explorer, Chrome, Safari, and Firefox, one tester reported the page was not displaying properly. A bit of investigation revealed that the tester was using Internet Explorer 9. As of this writing, IE9 is only one release behind the current IE10, so we were puzzled why IE9 did not render the page as expected. Further investigation revealed that IE9 behaves differently when viewing intra-net pages and inter-net pages. For pages served within our corporate firewall, IE 9 runs in "quirks mode", and behaves like IE 5! "The good thing about standards..." For pages served from the external network, IE9 behaves like a modern browser. Google "IE quirks mode", and you will find lots of discussion regarding IE in quirks mode and standards mode.
We are using APEX to deliver the problem page, so we'll examine one solution to handling this problem in APEX. Our page just displays information with some links, and we are not collecting any information nor interacting with the end-user. This makes things much simpler. Our solution will consist of three parts:
Our modern browser region was laid out in three columns with <div> tags. For older browsers, we can lay out the page with <table> tags:
Finally, a rant and a plug. When I read Tanenbaum's Computer Networks, I was really impressed by how well written the book is. Computer Networks was a pleasure to read; I can not say that about many technical books. Why shouldn't a technical book read as well as a good novel? Sure, we're all geeks, and we love to learn; but I could read Tanenbaum's book just for the fun of reading it.
After laying out a page with <div> tags and testing the page with Internet Explorer, Chrome, Safari, and Firefox, one tester reported the page was not displaying properly. A bit of investigation revealed that the tester was using Internet Explorer 9. As of this writing, IE9 is only one release behind the current IE10, so we were puzzled why IE9 did not render the page as expected. Further investigation revealed that IE9 behaves differently when viewing intra-net pages and inter-net pages. For pages served within our corporate firewall, IE 9 runs in "quirks mode", and behaves like IE 5! "The good thing about standards..." For pages served from the external network, IE9 behaves like a modern browser. Google "IE quirks mode", and you will find lots of discussion regarding IE in quirks mode and standards mode.
We are using APEX to deliver the problem page, so we'll examine one solution to handling this problem in APEX. Our page just displays information with some links, and we are not collecting any information nor interacting with the end-user. This makes things much simpler. Our solution will consist of three parts:
- we will detect the browser and save the browser in an Apex item
- we will use two APEX regions, one for newer browsers and a second for older browsers
- Each region will have a Condition, and we will display the region (or not) depending on the browser
declare
v_browser_string varchar2(512) := null;
begin
v_browser_string := upper(owa_util.get_cgi_env('HTTP_USER_AGENT') ) ;
if instr(v_browser_string,'MOZILLA/4.0') > 0 -- older browser
or instr(v_browser_string,'MSIE 9') > 0 then -- IE9
:p1_browser := 'OLD';
else
:p1_browser := 'NEW';
end if;
end;
Unfortunately, when we have an IE9 browser, we can't tell what document mode the browser is in. We could examine the client's IP address, too, but now we're introducing network variables into our code. And the end-user can switch document modes, too. So, we'll just treat IE9 as an old browser.Our modern browser region was laid out in three columns with <div> tags. For older browsers, we can lay out the page with <table> tags:
<table width=100%>
<tbody>
<tr>
<td width=20%>
<!-- Column 1 content -->
</td>
<td width=40%>
<!-- Column 2 content -->
</td>
<td width=40%>
<!-- Column 2 content -->
</td>
</tr>
</tbody>
</table>
Next, we need to add a Condition to the modern browser region and the old browser region. The condition should be "PL/SQL Function Body Returning a Boolean", and the code should look like the first line for the modern browser region or the second line for the old browser region.
return :p1_browser = 'NEW'; return :p1_browser <> 'NEW';Now when a browser requests our page, we detect the browser and display the region that is appropriate for that browser. Modern browsers get the <div> layout, older browers get the <table> layout, and our end-users see the same layout.
Finally, a rant and a plug. When I read Tanenbaum's Computer Networks, I was really impressed by how well written the book is. Computer Networks was a pleasure to read; I can not say that about many technical books. Why shouldn't a technical book read as well as a good novel? Sure, we're all geeks, and we love to learn; but I could read Tanenbaum's book just for the fun of reading it.
Sunday, October 6, 2013
Rolling your own locks: Everything old is new again
No matter what language we program, no matter what operating system we use, or no matter what database we use, we frequently face the same problems. Yesterday's CICS/COBOL programmers and today's web developers both face a common problem: how to handle database locking. Fortunately, there is a solution common to both environments. We will use an old CICS strategy to solve an interactive web site problem.
The pseudo-conversational nature of CICS has bedeviled many a COBOL programmer. Processing transactions in a batch program is fairly simple:
But in the online world of CICS, the program terminates every time a screen is displayed. Any locks taken are released when the program terminates. So, how do you write an online CICS program that maintains data integrity?
Web programmers have the same problem when programming interactive web pages. In the CICS world, we called it pseudo-conversational. In the HTML world, we call it stateless. It's really the same problem: in both environments, after displaying the page or the screen, we do not have a way to hold a lock. And in fact, we do not want to hold a lock. Remember the last time you went online and made a purchase? It took several minutes to complete that purchase. Imagine holding a lock on a table row or a table page for several minutes? Such a system would quickly grind to a halt waiting for resources to unlock.
So, we adopt a strategy called optimistic locking. We'll assume that the data we displayed in an online screen, whether it's a web page or a CICS screen, does not go stale. Rather than hold a lock after displaying a screen and while waiting for a user to continue, we simply test to see if the data went stale when we're ready to update our data. The logic looks something like this:
Suppose Fred and Wilma go to the Bedrock Bank ATM to withdraw 70 rocks from savings. They each go to the ATM, display their account information, and attempt to make the withdrawal. According to each display, there should be enough rocks in the savings account to withdrawal 70 rocks. First, Wilma withdrawals 70 rocks, and all is good. Then Fred attempts to withdrawal 70 rocks. Should the Flintstone account go 40 rocks in the red? Of course not! The problem is Fred made his withdrawal based on stale information. His display says there are 100 rocks in savings, but that's old information now. There are only 30 rocks in savings. The ATM needs to implement controls to insure data integrity.
In the Oracle Apex Oracle Apex world, automatic row processing takes care of this behind the scenes. We can write web-based programs as if we didn't need to worry about locking. But in some cases our interactions with the database will be complicated enough that we need to implement the locking. Fortunately, it's not too difficult; we just need a way to know if the data went stale before we update it.
For our first solution, we'll take a page from CICS methodology. We'll add a last update timestamp to the ACCOUNT table. Notice that we're adding a complete timestamp, not just a date field. A date field is not fine-grained enough for our purpose.
We make two assumptions: (1) every insert or update always updates the LAST_UPDATE_TS column. We can do this automatically with a trigger, or we can require every application to do this. (2) Every transaction reads the LAST_UPDATE_TS on a select, and every update/delete transaction includes the LAST_UPDATE_TS as a predicate in the where clause.
Now, imagine Wilma and Fred at the ATM machine, each about to withdrawal 70 rocks:
And the update would look like:
Notice that we compute the MD5 checksum from the current data in both SQL statements. When Wilma completes her transaction before Fred, the MD5 checksum changes, and Fred's transactions fails, just as it did before.
Now we have tow tools to handle online web transactions in the stateless world web programming. We can use either row timestamps or MD5 checksums to identify stale data. Just remember to rollback the whole transaction if any part of the transaction fails.
More Reading
If you're rolling your own HTML pages outside of Apex and you need to compute checksums, the Oracle dbms_obfuscation_toolkit package includes an md5 function. The md5 function only accepts one argument, so just concatenate the values together. PL/SQL happily concatenates numbers to strings to numbers, so we don't need to worry about dissimilar types:
The pseudo-conversational nature of CICS has bedeviled many a COBOL programmer. Processing transactions in a batch program is fairly simple:
- Declare a cursor, including a for update of column_name1, column_name2 clause
- Open the cursor
- Fetch rows
- Update where current of cursor
- Close cursor
- Commit the updates
But in the online world of CICS, the program terminates every time a screen is displayed. Any locks taken are released when the program terminates. So, how do you write an online CICS program that maintains data integrity?
Web programmers have the same problem when programming interactive web pages. In the CICS world, we called it pseudo-conversational. In the HTML world, we call it stateless. It's really the same problem: in both environments, after displaying the page or the screen, we do not have a way to hold a lock. And in fact, we do not want to hold a lock. Remember the last time you went online and made a purchase? It took several minutes to complete that purchase. Imagine holding a lock on a table row or a table page for several minutes? Such a system would quickly grind to a halt waiting for resources to unlock.
So, we adopt a strategy called optimistic locking. We'll assume that the data we displayed in an online screen, whether it's a web page or a CICS screen, does not go stale. Rather than hold a lock after displaying a screen and while waiting for a user to continue, we simply test to see if the data went stale when we're ready to update our data. The logic looks something like this:
- Select data from database
- Display data on the screen; program terminates and waits for user input
- User updates the data on the screen
- Verify the original data has not changed, and apply the user's updates to the database
ID NAME CHECKING SAVINGS -- -------------- -------- ------- 1 Flintstone 100 100 2 Rubble 100 100
Suppose Fred and Wilma go to the Bedrock Bank ATM to withdraw 70 rocks from savings. They each go to the ATM, display their account information, and attempt to make the withdrawal. According to each display, there should be enough rocks in the savings account to withdrawal 70 rocks. First, Wilma withdrawals 70 rocks, and all is good. Then Fred attempts to withdrawal 70 rocks. Should the Flintstone account go 40 rocks in the red? Of course not! The problem is Fred made his withdrawal based on stale information. His display says there are 100 rocks in savings, but that's old information now. There are only 30 rocks in savings. The ATM needs to implement controls to insure data integrity.
In the Oracle Apex Oracle Apex world, automatic row processing takes care of this behind the scenes. We can write web-based programs as if we didn't need to worry about locking. But in some cases our interactions with the database will be complicated enough that we need to implement the locking. Fortunately, it's not too difficult; we just need a way to know if the data went stale before we update it.
For our first solution, we'll take a page from CICS methodology. We'll add a last update timestamp to the ACCOUNT table. Notice that we're adding a complete timestamp, not just a date field. A date field is not fine-grained enough for our purpose.
ID NAME CHECKING SAVINGS LAST_UPDATE_TS -- -------------- -------- ------ ----------------------- 1 Flintstone 100 100 2013-09-15.12.02.57.000321 2 Rubble 100 100 2013-08-18.15.21.01.000123
We make two assumptions: (1) every insert or update always updates the LAST_UPDATE_TS column. We can do this automatically with a trigger, or we can require every application to do this. (2) Every transaction reads the LAST_UPDATE_TS on a select, and every update/delete transaction includes the LAST_UPDATE_TS as a predicate in the where clause.
Now, imagine Wilma and Fred at the ATM machine, each about to withdrawal 70 rocks:
- Fred slides his card, the ATM displays 100 rocks and saves the LAST_UPDATE_TS.
- Wilma slides her card, the ATM displays 100 rocks and saves the LAST_UPDATE_TS.
- Wilma withdraws 70 rocks, the ATM executes the following:
update account set savings = savings - 70, last_update_ts = current timestamp where id = 1 and last_update_ts = 2013-09-15.12.02.57.000321 - Fred attempts to withdraw 70 rocks, the ATM executes the following:
update account set savings = savings - 70, last_update_ts = current timestamp where id = 1 and last_update_ts = 2013-09-15.12.02.57.000321 - Fred's attempt fails. Wilma's transaction changed both the SAVINGS column and the LAST_UPDATE_TS column. The ATM displays a message telling Fred his account balance has changed and re-displays the Flintstone account balance, now 30 rocks.
select name,
checking,
saving,
wwv_flow_item.md5(name, checking, saving)
into :v_name,
:v_checking
:v_saving
:v_hidden_md5
from account
where id = 1
And the update would look like:
update account set saving = saving - 70 where id = 1 and wwv_flow_item.md5(name, checking, saving) = :v_hidden_md5
Notice that we compute the MD5 checksum from the current data in both SQL statements. When Wilma completes her transaction before Fred, the MD5 checksum changes, and Fred's transactions fails, just as it did before.
Now we have tow tools to handle online web transactions in the stateless world web programming. We can use either row timestamps or MD5 checksums to identify stale data. Just remember to rollback the whole transaction if any part of the transaction fails.
More Reading
If you're rolling your own HTML pages outside of Apex and you need to compute checksums, the Oracle dbms_obfuscation_toolkit package includes an md5 function. The md5 function only accepts one argument, so just concatenate the values together. PL/SQL happily concatenates numbers to strings to numbers, so we don't need to worry about dissimilar types:
select name,
checking,
saving,
dbms_obfuscation_toolkit.md5(input_string => name || checking || saving)
into :v_name,
:v_checking
:v_saving
:v_hidden_md5
from account
where id = 1
Friday, August 16, 2013
Oracle Apex Tabular Forms
The Tabular Forms feature of Oracle's Apex web development tool allows users to update multiple rows in a table using only declarative programming. All of the Javascript and PL/SQL is done behind the scenes, making tabular forms a relatively easy to use and powerful tool.
After logging into your Apex environment (Oracle offers a free demo site), and navigating to your page, it's simple to create a tabular form: Create Region -> Form -> Tabular Form . Then, the tabular form wizard will guide you through the process of selecting the table and columns, choosing a primary key, choosing columns to update, and adding buttons and branching.
Run the page, and you find that the tabular form lets you scroll thru every row in the table. Pretty cool, but maybe you only want a subset of the rows? So your page has an ID field, and you modify the tabular form with the WHERE clause
Run the page again, and nothing shows up. No rows, no data, nothing. If the ID field of your page is blank, that's the correct response. Nothing in the table satisfies the where clause, so nothing is displayed. If you enter an ID value that's not in the table, again, nothing is displayed. If you enter an ID value that is in the table, only the matching rows are displayed. Perfect... almost.
There is an Add Row button to add new rows, and using the Add Row button is the way the tabular form is designed to work. But in some cases, we would like an empty row of cells to display if there isn't any data. The end-user may find it odd that nothing is displayed on the screen, and the blank row gives them a place to start.
Depending on what version of Apex you're using, you may be able to modify the tabular query by using a UNION to select a row of null values from dual. But this doesn't work with every release of Apex, so we'll take a different approach.
If we examine the Add Row button, we find that it fires a bit of Javascript:
So, we will execute the JavaScript addRow function when the page loads by using a dynamic action. Here's how to do it: create a new dynamic action and name it Add_1_Row. Click Next. The Event should be Page Load, don't choose a Condition, and click Next. The Action should be Execute JavaScript Code, the code should be javascript:apex.widget.tabular.addRow(); , and click Next. Then click Create Dynamic Action.
Now, your end-user will always have a blank row to enter data. And if they need another blank row, the Add Row button is ready for them to use. Best of all, you don't need to worry about how to process this row of blanks because the Apex multi-row processes auto-magically handle all the inserting and updating for you. Now it's perfect!
For more information about how APEX handles commits in tabular forms, see my post discussing commits in APEX.
After logging into your Apex environment (Oracle offers a free demo site), and navigating to your page, it's simple to create a tabular form: Create Region -> Form -> Tabular Form . Then, the tabular form wizard will guide you through the process of selecting the table and columns, choosing a primary key, choosing columns to update, and adding buttons and branching.
Run the page, and you find that the tabular form lets you scroll thru every row in the table. Pretty cool, but maybe you only want a subset of the rows? So your page has an ID field, and you modify the tabular form with the WHERE clause
select rowid, /* Apex generates the select */
table_key,
table_col1,
table_col2,
table_colx
where table_key = :id /* You add the where clause */
Run the page again, and nothing shows up. No rows, no data, nothing. If the ID field of your page is blank, that's the correct response. Nothing in the table satisfies the where clause, so nothing is displayed. If you enter an ID value that's not in the table, again, nothing is displayed. If you enter an ID value that is in the table, only the matching rows are displayed. Perfect... almost.
There is an Add Row button to add new rows, and using the Add Row button is the way the tabular form is designed to work. But in some cases, we would like an empty row of cells to display if there isn't any data. The end-user may find it odd that nothing is displayed on the screen, and the blank row gives them a place to start.
Depending on what version of Apex you're using, you may be able to modify the tabular query by using a UNION to select a row of null values from dual. But this doesn't work with every release of Apex, so we'll take a different approach.
If we examine the Add Row button, we find that it fires a bit of Javascript:
javascript:apex.widget.tabular.addRow();
So, we will execute the JavaScript addRow function when the page loads by using a dynamic action. Here's how to do it: create a new dynamic action and name it Add_1_Row. Click Next. The Event should be Page Load, don't choose a Condition, and click Next. The Action should be Execute JavaScript Code, the code should be javascript:apex.widget.tabular.addRow(); , and click Next. Then click Create Dynamic Action.
Now, your end-user will always have a blank row to enter data. And if they need another blank row, the Add Row button is ready for them to use. Best of all, you don't need to worry about how to process this row of blanks because the Apex multi-row processes auto-magically handle all the inserting and updating for you. Now it's perfect!
For more information about how APEX handles commits in tabular forms, see my post discussing commits in APEX.
Monday, July 29, 2013
Where have all the buttons gone?
Oracle's Apex product Apex.oracle.com allows a web page developer to place buttons in pre-defined locations within an Apex page region. As a developer, one is faced with a pull-down menu displaying about a dozen choices. I haven't used Apex enough to easily pick the right location the first time. I usually pick something that looks right, display the page, and take another try or two to get it right.
I need a cheat-sheet showing the button descriptions with the buttons sitting in the right places. So, I created an almost-empty Apex region and added a button at every possible pre-defined location. Each button has a label derived from the pull-down menu, so a quick glance shows the button descriptions and where it physically goes in the region.
If you visit the buttons page, you can see the predefined locations in a region and buttons placed among the Apex items. There are three regions on this page: one region with all the buttons right-aligned, one region with all the buttons left-aligned, and the last region with buttons and data items intermingled.
I need a cheat-sheet showing the button descriptions with the buttons sitting in the right places. So, I created an almost-empty Apex region and added a button at every possible pre-defined location. Each button has a label derived from the pull-down menu, so a quick glance shows the button descriptions and where it physically goes in the region.
If you visit the buttons page, you can see the predefined locations in a region and buttons placed among the Apex items. There are three regions on this page: one region with all the buttons right-aligned, one region with all the buttons left-aligned, and the last region with buttons and data items intermingled.
Subscribe to:
Posts (Atom)