Thursday, April 28, 2011

Oracle Application Express in Research Environments

Today, Oracle issued a press release in conjunction with Indiana University, highlighting their use of the Oracle Database and Oracle Application Express in their research environment.



I think it's a perfect example of:
  1. An organization providing access to the rich functionality of an Oracle Database as a service, on their "private cloud".
  2. How Oracle Application Express can be used to provide easy access to this rich database functionality.
  3. How Web and database application development via APEX is approachable by those who don't have "Web Master" on their business card.

It's a natural extension to see how Oracle Application Express could be used in research environments, but on Oracle Exadata. Stay tuned.

10 comments:

Lake said...

We are an NIH funded public research project and we evaluated application express and then gave up on it and went to forms 11.1.1.4 for the following reasons: 1. apex's 100 item limit. That is a killer. We have much survey data. A 25 page questionnaire can end up with hundreds of columns in the table. Also data from medical experts and/or instruments tend to have a very large number of columns. 2. the lack of support for natural keys. In some cases natural keys are superior. In other cases generated keys would be best.We need choice on a per table basis. Apex would do very well to start supporting natural keys in addition to their current support for generated keys. In particular the tabular forms do not accommodate natural keys. 3. Apex does not support composite keys of more than 2 parts. We have some tables with 4 part composite (natural) keys. We would love to be able to use application express instead of the very complicated to install forms software but the current limitations preclude this.

Joel R. Kallman said...

@Lake,

Thank you for your feedback. It is much appreciated.

With respect to #2 and #3, help is on the way. In Application Express 4.1, we will support ROWID for both Automatic DML forms and tabular forms, with no other requirement for a primary or unique key.

For the 100 item limit, I understand customer's complaint about this. However, I don't envision this changing any time soon. Even if we were to increase it to 200, there's going to be someone out there asking to raise it to 400. It will never end.

I realize you shouldn't be asked to change your data model to accommodate a tool, the tool should accommodate your data model. However, in your specific case, I would have modeled this differently, with questions and answers being modeled as a separate table, instead of columns in a single table. In this case, it's inherently extensible, and the questions and answers can be rendered via a tabular form, which have no limit.

Thanks again for your feedback - it is very valuable.

Joel

Lake said...

re: "It will never end. "
I think the column maximum idea is likely to end with the database maximum which is 1000.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/limits003.htm

Columns Per table 1000

I think anytime that a vendor puts out a database interface that has far more restricting limits than the (one and only) database that it is front-ending, that is a recipe for surprise and unhappiness amongst the customers. I think one would do well to even make an interface less restrictive than the database to accommodate future enhancements of the database and accommodate use with other databases. We spend far more time working on interfaces for entering/updating data than doing things in the database itself. That's where all the time and money is going. One way Oracle could gain market share is having a javascript crud-savvy intelligent interface to competitive databases. The credibility gained there could translate to customers moving to oracle database.

Note that probably your biggest competitor here is sqlserver
which is more generous with the columns. http://msdn.microsoft.com/en-us/library/ms143432.aspx
Columns per nonwide table 1,024
Columns per wide table 30,000
Now do they throttle these limits in their interfaces such as dot net? I don't know but it'd be interesting to find out.

I really don't understand the thinking here: 'our tables have a maximum number of columns of 1000 but you can only insert,update 100 of them'. There are very few ways to manage data in oracle these days. It used to be the case that any interface product would support a connection to oracle. Now it is the exception.

Joel R. Kallman said...

@Lake,

Thanks for your feedback, as always.

Customers, like you, may bristle at the fact that you cannot place 1,000 columns on a single Web page. However, I have a one-word opinion about applications that present 1,000 fields to an end-user on a Web page: "unusable". In the realm of elegant design, jamming 1,000 fields on a page results in something ultimately unusable.

>> I really don't understand the thinking here:

The thinking is quite simple. There is a performance penalty and PGA memory consumption increase for each additional element on a page that can be POSTed to the APEX engine. So, we could make the changes in Application Express to support 1,000 fields, but the poor typical customer who has 50 columns in his table now pays the penalty. We are trying to accommodate the vast majority of customer use cases, and make this as fast as possible.

To me it sounds like your use case could not be accommodated by Application Express, and you made the correct decision not to use it. I don't fault you for that.

Joel

P.S. I've modeled survey data and I've done it differently (as I said before, with questions and answers as separate child tables to the parent survey table). When you add the 502nd question to your survey, you now have to add a column to your table? That does not seem ideal.

Unknown said...

I know this is old but we just updated to 4.2 and now a page that use to render fine will not render at all. We have well over 200 items on a page. The items are broken up into tabs and represent a very large application that our customers must fill out. We have written our own JSON solution for saving the values that works quite nicely and the user can switch between tabs without having to make a server call which is key. This all use to work fine in 4.1 but now in 4.2 the page wont render at all. Where can I go to increase this limit so I dont have to redesign our pages? Thanks.

BTW, this comment cracks me up since the limit was raised to 200:

"Even if we were to increase it to 200, there's going to be someone out there asking to raise it to 400. It will never end."



Unknown said...

I know this is old but we just updated to 4.2 and now a page that use to render fine will not render at all. We have well over 200 items on a page. The items are broken up into tabs and represent a very large application that our customers must fill out. We have written our own JSON solution for saving the values that works quite nicely and the user can switch between tabs without having to make a server call which is key. This all use to work fine in 4.1 but now in 4.2 the page wont render at all. Where can I go to increase this limit so I dont have to redesign our pages? Thanks.

Joel R. Kallman said...

Hi Michael,

I'm not out to create work for you, but can you possibly create a test case on apex.oracle.com?

Joel

Unknown said...

I could but what are you looking for?

When I load a page with over 200 items I get an error page that says:

""Item count exceeds maximum of 200.

Contact your application administrator."

I kind of assumed that there was a hack that I could do that would disable this 200 item check and just render the page. I'll start working on a test case if that would help.

Joel R. Kallman said...

Michael,

It's unclear to me how this ever worked before. There's no possible way you could have successfully POSTed the page and saved all of the values to session state with more than 200 uniquely named items on the page.

There is no simple "hack" you can do to increase this.

Joel

pat said...

Hi,
I was very happy to learn of the increase in the page item limit to 200, however, I just upgraded to 4.2.1 and I am still having problems with a data entry form that has a master-detail form where the detail form brought me close to the 100 limit, so I had to scale back. However, when I tried to add columns back to the detail form, I am finding the same issues I had previously, where the detail portion would not save properly -- giving a 404 error. I am sure I was no where near doubling the page items. Is there some other requirement for increasing the page items -- like using newer templates??

Pat