Hello
We are using APEX 20.2 that is installed in a PDB in an Oracle 19c database. We also have ORDS 19.1. Same behaviour in Edge and Chrome.
We have a schema called c##prod - it has been around for quite a while and has loads of tables, views, packages etc in it. It is accessed by a lot of other non-APEX applications so it not an easy task to change it.
I want to use the schema for an APEX application and have run into a problem that I could do with some help with please.
I created a workspace and linked it to the schema - when I Iogged into the workspace everything appeared fine - when I clicked on the SQL Workshop tab I get the following error:
"Invalid Schema name. Contact your application administrator"
We have quite few other workspaces and they all are working fine and this error doesn't appear on any of the others. So I assume it is something to do with the fact that c##prod was created in a CDB? Can anyone confirm this please?
To try and rectify the problem I created another database user called TEST_DB in my PDB, and created a workspace linked to it. Once created I then added the c##prod schema to the workspace. This appeared to solve the issue with accessing the SQL Workshop. I created a single blank application and that also was fine. However when I tried to create a classic report using one of the tables from the c##prod schema I get the following error in a dialog box :
"Ajax call returned server error ORA-20987: APEX -invalid schema-contact your application administrator - for Execute PL/SQL Code "
If I click on the OK in the error message the columns for the table appear as expected and when I click on CREATE I get an empty Page Designer and an error JSON WRITER NOT_OPEN The issue is the same regardless of the table I select.
I have none of these problems with any other schema or workspace - all of the others have local database usernames ie their names do not start with c##.
I am assuming the problem has something to do with the type of database user I am using. Can anyone give me any pointers please how I can rectify this? At the moment we are looking at creating a schema with a load of views that access the tables in the c##prod schema but that is going to cause a lot of work and I would prefer to avoid this if at all possible.
Any help greatly appreciated.
Regards
Helen