I think it's a perfect example of:
- An organization providing access to the rich functionality of an Oracle Database as a service, on their "private cloud".
- How Oracle Application Express can be used to provide easy access to this rich database functionality.
- 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.
4 comments:
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.
@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
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.
@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.
Post a Comment