Thursday, October 25, 2007

Changing Scheme in Oracle Application Express (APEX) inside application

There is some application when you need to change your current scheme to another one. For example you would like to create your own SQL Editor in HTML. It would be much easier to change the current scheme, then work with "scheme." prefix. So, there is a solution.

You have to update three Oracle Application Express tables (wwv_flows, wwv_flow_companies, wwv_flow_company_schemas) in scheme flows_030000 (in my case I use APEX 3, if you use older version check your flows_ name).

There is an example:
You have to know your application and workspace identifier.

update flows_030000.wwv_flows
set owner=&NEW_SCHEME_NAME
where id=&APP_NAME;

update flows_030000.wwv_flow_companies
set FIRST_SCHEMA_PROVISIONED=&NEW_SCHEME_NAME
where PROVISIONING_COMPANY_ID=&WORKSPACE_ID;

update flows_030000.wwv_flow_company_schemas
set schema=&NEW_SCHEME_NAME
where id=&WORKSPACE_ID;


Best Regards!

2 comments:

Patrick Wolf said...

Hi,

why would you want to directly manipulate the APEX repository when you can do it through the UI of APEX?

Shared Components\Edit Application Definition\Parsing Schema

Patrick

Kostya said...

This is useful for end users of your application. For example creating a SQL Editor, component of ETL or something else.

As usual end users have no access to Application Builder.

Kostya

Google