This content has been marked as final. Show 17 replies
Well that's very interesting to consider.
I do know there is a limit of 100 database items on a page.
There's a lot of limits going on in this product. Where are these disclosed?
I'm getting very nervous about committing to using this. The 100
database items is a probable show stopper right there anyway. But I would
certainly be concerned about a limit on dynamic actions also. It really makes
you wonder what else has a teeny limit in apex?
"I do know there is a limit of 100 database items on a page."
Not entirely correct, but close.
If you have a 'standard' form, where there is a field for each field in your table, then there is a limit of 100 (and you probably have a very bad table design as well).
If you have a 'tabular' form, then you are also limited 100 columns (not items) because of the built-in limit on the apex_application.g_fxx construct.
But, I have several forms in my apps that use both the 'standard' and 'tabular' forms on the same page, far surpassing 100 different 'items' (fields) that can be displayed or edited, etc. As a matter of fact, the main search page in my app can easily show a 'tabular' form (of the search results) with 35 coulmns and a thousand or more rows. In total, that's well over the 100 'items' you referred to above.
I don't know if there is a number of dynamic action since I'm working with APEX 3.2, but I did discover some other limits. The number of columns in an interactive report is 60 columns. I know too that there is a column size limit when you are exporting your interactive report in PDF. Over 18 columns and you will probably exceed the exporter's buffer's limits.
There are limits on the conditions you made in interactive reports. You can't highlight a line or a cell for beeing between thesholds values since there is no >,>=,<,<= or between operators.
You can't choose the scale for the charts in the interactive reports. You can't assign different template to different users.
The number of limits is huge !
I stand corrected in that some limits are acknowledged:
Where are people getting these ideas that normal form or "Codd" said that there was a
limit on columns and if you had more columns than a database product found convenient to implement that it was a 'design defect'? I took database management as an undergrad and in grad school and there was NO rule about number of columns. Anyone who says there is is shoveling pure bs. Even if there were such a rule, and there isn't, it is very inadvisable for a vendor who allows 1000 columns in a table in their database product to impose a limit that is 1/10 of that in an interface to their database. (And btw you can create a table (A 'wide' table, in sqlserver with 30,000 columns according to this:
Artificially dividing up data instruments that were administered/collected at one time into pieces in separate tables, just to make a vendor's developers' life easier and more convenient, or just to keep the product from competing with the extremely extremely expensive "enterprise" products the vendor sells, is not acceptable.
Splitting highly related data into separate tables for no reason other than the
interface is not capable of handling it, creates a load of problems that would not otherwise
exist. What if the user enters less than all the parts? Then someone has to figure out if the parts not entered had no data recorded in the source or if they were simply accidentally skipped. Basically someone or a program would have to enter those pages and denote they had no content if that were the case. Much more logic, much more opportunity for error vs keeping data that was highly associated together.
A software vendor can apply whatever limits they want and no one can stop them, that's for sure. But what would be better here is making these more clear up front. This is a "rad" development tool for very small and not hugely complex forms and reports. Be clear and there will not be people royally steamed over spending their time and money evaluating a product that is not designed to do much of what people need it to do. This stuff should be in an FAQ and the top of the forum.
I've been working with ApEx since it's HTMLDB days, designed and built dozens of production applications - some fairly basic, some highly complex, and I have never come close to running into either the 100-field-per-page limitation, 100-column per classic report limitation, or 60-column per interactive report limitation. And I don't expect to either. Because it doesn't matter whether it's a basic application or a highly complex one - in my book (and I've been doing design/development for almost 25 years now) a well-designed, highly complex application is simply an application that has been broken down into a number of fairly basic ones. There may be a dozen pages, there may be ten dozen pages - my users have never wanted, nor would they accept, a single page or a single report with more than 100 fields/columns on it. Talk about usability - yikes!
This has nothing to do with database limitations. It doesn't matter if I'm doing an ApEx page against an Oracle database table with 1,000 columns or an ASP.NET page against a SQL Server table with 30,000 columns. Seriously, are any of you going to design a single page that collects 1,000 or 30,000 items of information, or a report that has 1,000 or 30,000 columns?? Read some user interface design books.
It cracks me up when these limitations are brought up like they're some incredible show-stopper. A Ferrari can do 250mph; a Honda can do 100mph - so sure, if I want to drive like an idiot, 100mph is going to be a show-stopping limitation to me.
While there are probably thousands of examples of working databases with tables that big, trying to design one page to hold that many items (individual items or report columns), is a usability nightmare. My users would string me up if I gave them and interface like that that would require constant scrolling back and forth trying to see all of the information.
They've told me in no uncertain terms that they only want on one page what they can see on one page, though I do break that rule a little bit here and there (some columns are pretty wide, so in a report they wind up pushing other columns over). But, at least they cut me a break on those pages due to the data, not the page or table structure.
We seem to have gotten off track with Sddc's original post. Not that the diversion wasn't refreshing - it got my dander up. I get aggravated when it seems people are making important decisions to commit/not commit to any product based on misinformation or misunderstanding - I fought that battle here where I am with people assuming ApEx was just Oracle's ill-fated WebDB product under another name, only fit for simple projects, etc. But the look on their faces when I blow right past them getting good, solid, efficient, scalable applications up while they're still wrestling with their "sexy" tools (which will remain nameless) - it's priceless.
Sddc, I don't know of any limitation on the number of dynamic actions on a page. But, like the 100-item-per-page limitation, I'm not sure I'd ever run into it. Just my opinion - but 29 independent, mutually exclusive actions on one page seems like a lot to me. Could you provide some more information on what some of these DA's do and what they're triggered by? Is there any possibility that the work of some of them can be combined?
I agree with you, a page with more then 100 fields is a bad design.
But sometime, it's a requirement for the users. By example, my clients wants a report that contain all the data in one page. And we have more then 80 fields in the main table (but they aren't all on the same page). I said it was a bad idea, there was too much data to be useful. So I had to develop an interactive report for them. But there is a limit of 60 columns. I ask the client to tell me which columns that where less needed. Even if I said it was wrong, they still wanted it. At least, they could remove many columns so the repor went under the 60 columns limit.
But, I think that some limits are really bad, why can't I have all the math operators ? <=,<,>,>= or a between. It shouldn't be hard to do. Because of that, the interactive report is far less useful then Ms Excel.
In all fairness IR's were only introduced in 3.1 I believe, so it was still pretty new in 3.2. I'm not sure about 3.2, but version 4 does allow those conditions on highlighting rows, as well as "like", "not like", "in", "not in", "contains", "not contains", and regular expression matching. I know this doesn't help you right now, but at least the ApEx team is responding and continuing to grow the product. Believe me - if you can get to version 4, I think you'll be pretty impressed about lots of things.
I wish we could use ApEx 4.0 . But I don't have any control on which version my client wants to use. We only change for ApEx 3.2 few months ago. Thjey will wait for new upgrades before going to ApEx 4.0.
I did try ApEx 4.0 on apex.oracle.com. It's better, but still, why those operators are not in the filter options. I hope they will add them in the next release, because, if I want to filter all values over 1000, I still can't in ApEx 4.0
BTW, you might be able to reduce the number of you dynamic actions by specifying multiple page items in the "Item(s)" attribute of the "When" section if all execute the same code. Just separate them with a comma (no space). Like: P1_MGR,P1_DEPTNO
My Blog: http://www.inside-oracle-apex.com
APEX 4.0 Plug-Ins: http://apex.oracle.com/plugins
Not sure what you mean by the filter operators still not being in 4.0. They are. Just drop-down Actions, click Filter, choose a column in your report, drop-down Operator, and they're all right there.
Thanks for your answer.
I enabled a more detailed error log for EPG.
Here is the error I get when I add a new dynamic action to my page:
ORA-20867: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APEX_040000.WWV_FLOW_DYNAMIC_ACTION", line 268
ORA-06512: at "APEX_040000.WWV_FLOW", line 12410
ORA-06512: at "APEX_040000.F", line 267
ORA-06512: at "APEX_040000.F", line 294
ORA-06512: at line 30