APEX SQL Workshop and Wizards issues in EBS environment

E-Business Suite (EBS) is a beast... it's the biggest environment I've ever seen.
If you run this query in the APPS schema:


select object_type, count(*)
  from user_objects
group by object_type
order by 2 desc


This is the result:

Over 50,000 packages, over 40,000 synonyms, over 30,000 views in one schema... impressive!

As I don't have direct access to the Oracle EBS database, I do everything through APEX.
I setup two workspaces, one linked to the APPS schema, where I only use SQL Workshop to access the objects and another workspace which holds my applications and database objects and grants to some objects in the APPS schema.

The issue with the Object Browser in SQL Workshop (in APEX) is that it will only show 10,000 objects.
So there is a chance that if you create your own view, synonym or package that you won't see it.
(There are ways to increase this limit, but as it's unsupported I won't mention how. And you would need to have access to the web server to make the change, which you might not have either.)

Even if you try to filter the result, it won't show up. Obviously you won't see many environments having over 10,000 objects in one schema, but I thought to post it anyway as some other EBS customers might hit the issue too and maybe when the Public Oracle Cloud is online and you do everything through the APEX screens you might hit this issue too (if it was an EBS environment for example).

So how do you get around this? If you want to change your view or you want to see the definitions of that view you can do:

select text
  from user_views 
 where view_name = 'PER_PEOPLE_F'

You can copy the definition, make the change and run it in SQL Workshop > SQL Commands to update your view. Querying the user_objects will show you all the objects for example starting with APEX:


select *
  from user_objects
 where object_name like 'APEX%'


For packages (and package bodies) it's a pain, especially if you want to develop those packages online. If you can't select the package you don't have the nice editor in APEX, so it makes development a bit harder. You could argue you should do development in for example Oracle SQL Developer or others, but in my case I didn't have direct access to the database, only through a browser...


The other issue I encountered was in the APEX wizards when I created a Form on a Procedure, when the package/procedure resides in the APPS schema. It kept spinning and spinning and after a couple of minutes I just closed my browser because I didn't want to wait longer.

I guess it's because the APPS schema is so big and reading from the data dictionary takes a long time, potentially even longer than the web server timeout.

The workaround here is to add a blank page, create an HTML region and add your items manually.
Also don't forget to add your process to the page to fetch and/or store the record.


Hope that helps some people who can't find their objects in SQL Workshop or who find the wizard is slow or doesn't complete in an EBS environment.