Apex Enhancement Request Included

Got a real buzz today when I saw my feedback on Apex 3.1 beta made it into the product as an enhancement.


Here's the original feedback:

Hi Guys

Got the following error:


ORA-20001: get_report error ORA-20001: get_dbms_sql_cursor error
ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table
l_query=
select rowid as apxws_row_pk, TABLE_NAME, COLUMN_NAME , count(*) over () apxws_row_cnt

from ( select * from (select table_name, column_name from user_tab_columns) r ) r

where rownum <= to_number(:APXWS_MAX_ROW_CNT) order by rowid


Obviously it's currently looking for rowid, which seems an unnecessary restriction.

You may want rowid for full CRUD functionality, but often you only want read functionality.

And anyway couldn't you get the developer to nominate a single column as the primary key in the report attributes?

Why not replace your current query wrapper with something like this:

select apxws_row_pk, TABLE_NAME, COLUMN_NAME , count(*) over () apxws_row_cnt
from ( select rownum apxws_row_pk, r.* from (select table_name, column_name from user_tab_columns) r ) r
where rownum <= to_number(:APXWS_MAX_ROW_CNT) order by apxws_row_pk

Apart from that, all the additional functionality of interactive reports looks great.

I'll be upgrading and converting by reports as soon as possible.

Regards

Mark



Response

Mark,

Your suggestion has been logged as an enhancement request.

Regards,

David

Apex: Table and column name limits

I've been reviewing table and column name limits for Oracle Apex to come up with some internal standards.

Oracle supports 30 characters for object names, be it table, column, constraint, trigger, ... whatever.

Historically Oracle Designer used to add suffixes of 4 characters, and some of the built-in packages do also, e.g. for materialized view logs.

So generally 26 characters or less for object names is a good idea.

Oracle Apex prepends form items with a page reference e.g. P100_column_name.
Since it's easy to envisage an application having over a 100 pages, a limit of 25 characters should be imposed.

Importantly, you need to be aware that for PK columns, Apex imposes a limit of 22 characters for tabular forms.
See http://iadvise.blogspot.com/2006/12/apex-caveat-pk-column-name-length-it.html for details.

So in summing up the limits:

• table names 26 characters or less
• views, packages etc. can use 30 but try and stick to 26 characters or less
• column names generally 25 characters or less
• primary key column names 22 characters or less.

Naming conventions - well thats up to you!

Issue with APEX htmldbToolbar

I'd love the APEX team to wrap the htmldbToolbar in a DIV tag, this would be more convienient for repositioning it when in development mode. Our application sizes its vertical height to 100%, which results in a scroll bar when we are developing. Also we have to create a DIV tag, rather than referencing the document.body. It's a small change, and would be greatly appreciated.

See the image below for an example of what I mean.

PDF output from Apex

Carl Backstrom has a very interesting blog on APEX + Cocoon = PDF.

Upgrading Oracle XE to Apex 3.0

Upgrading Oracle XE to Apex 3.0 has a couple of tricks to it, so here's how.

1. Download the software, and unzip it.
2. Edit the ..\apex\coreins.sql script
3. Run the installation/upgrade as usual
4. Upload the images using the correct version of apex_epg_config.sql.
5. Upgrade the owa packages in the ..\apex\owa directory.

Edit the ..\apex\coreins.sql script

On line 573 you need to set the XE variable to 1 for XE installation: Rem Rem Set XE variable to '1' for XE installation, '0' for non-XE installation Rem define XE = '1'

Run the installation/upgrade as usual

For a local install: e.g. sqlplus "sys/syspass as sysdba" @apexins password SYSAUX SYSAUX TEMP /i/

Upload the images

XE uses the embedded PL/SQL gateway so you need to run the apex_epg_config.sql script to upload images into the XDB repository. Note: documentation references apex_epg_config11.sql - this is for Oracle 11g only!!! Login to sqlplus as sys, and run the script passing the path to the apex directory you unzipped e.g. sql> @apex_epg_config.sql c:\temp

Update the owa packages

See the ..\apex\owa\readme.txt for details.

That's all there is to it. The most important thing to remember is don't just read the documentation, read and understand the scripts as well. It's easy to forget to document something, not that I've ever done that ... ;)