Monday, 19 November 2007

Debugging with the APEX repository

Introduction...
I was recently working on an APEX application on a familiar 'Form on a Table' wizard-built page and encountering a ORA-00957 Duplicate Column Name error, meaning that a column name must be specified more than once in the INSERT.

So the problem must be that I had more than one page item bound to the same database column. This page had got quite bloated with lots of other business logic so I started by turning off regions, the old 'process of elimination', debugging through seeing if there was anything obvious, but nothing sprang up. Then I thought if only there was a way to view all the page items on my page which were bound to the same database column. Well there is, using the APEX repository.

For those of you who are not familiar with the repository, it is basically a set of views that expose all the APEX application metadata. For further information see Have a clean up, utilising the APEX repository which contains links to loads more information.


How...
In the repository there is a view called APEX_APPLICATION_PAGE_DB_ITEMS which is what we are interested in. It is described in the APEX_DICTIONARY view as...

'Identifies Page Items which are associated with Database Table Columns. This view represents a subset of the items in the APEX_APPLICATION_PAGE_ITEMS view.'

We can then run the following query to return all the items for a specific application / page bound to a db column more than once.
SELECT   db_column_name,
SUM(1) Duplicates
FROM apex_application_page_db_items
WHERE page_id = :page_id
AND application_id = :app_id
GROUP BY db_column_name
HAVING SUM(1) > 1
ORDER BY SUM(1) DESC
(Note: This view does not contain conditional rendering information, so if this was required you would need to join to APEX_APPLICATION_PAGE_ITEMS on ITEM_ID).


Conclusion...
So if ever you think, I wish I could see this information about this page / report or whatever, you probably can, just take a look into the repository. I wonder if there would be any scope for an APEX debugging framework that defines sets of processes linked to common 'ORA' errors. So for this example, it would simply be:

Error: ORA-00957 - Duplicate column name
  • Step 1: Run the following query, binding in your page and application id.
    SELECT   db_column_name,
    SUM(1) Duplicates
    FROM apex_application_page_db_items
    WHERE page_id = :page_id
    AND application_id = :app_id
    GROUP BY db_column_name
    HAVING SUM(1) > 1
    ORDER BY SUM(1) DESC

  • Step 2: Investigate all rows returned from the query and unbind items which should not be bound to the database column.

  • Step 3: Retest your page.


That would be nice.
Anthony.

3 comments:

canahari said...

Hello!

I am a newbie in Oracle APEX (univ. student), so most possibly my question is quite stupid :), but please help me (and newbies like me) if you can.

During the development of my application I encountered a "Duplicate Column Name" error. As I couldn't find the duplicated items, I tried your solution, but APEX gave me an "ORA-00942: table or view does not exist" error, and really, in Utilities/Data Dictionary there isn't anything named apex_application_page_db_items.

I think the solution must be good :), so I must be doing something wrong here. Do you have any idea of why can't I see the view I need? Any help would be greatly appreciated.

Eszter

Anthony Rayner said...

Hi Eszter,

No question is a stupid question, so don't worry about that!!! Stupidity strikes when people don't ask questions when they're not sure about something!

What version of APEX are you running? The APEX views which this query uses were exposed in APEX version 2.2, so if you're using a version prior to 2.2 then this query won't work.

Anthony

canahari said...

Hi Anthony,

Thank you for your answer. You are right, the version I use is Application Express 2.1.0.00.39. :( I think I can't update (I am doing a homework here, which I need to hand on and I don't want any compatibility problems :), so it seems I will have to handle my
problem manually.

Thank you for your patience again! :)

Eszter