Hi all.
I've just developed an application using Oracle Apex 5 in a dev environment. Now we want to deploy this app to a prod environment.
For this what was done, was the export of the workspace and application to a sql file.
When running the file f101.sql file we had to add this code to set the workspace id that was different and set the schema to prod:
begin
apex_application_install.set_workspace_id(5490760287589413);
apex_application_install.set_application_id(101);
apex_application_install.set_application_alias('INTEGRATION');
apex_application_install.set_schema('LSPROD');
end;
/
After the deploy, when accessing the app in the browser, the error appears:
Error during rendering of page item P0_SPRACHE.
ORA-06550: line 3, column 94: PL/SQL: ORA-00942: table or view does not exist ORA-00942: table or view does not exist ORA-06550: line 1, column 13: PL/SQL: SQL Statement ignored ORA-00942: table or view does not exist
So, the item P0_SPRACHE is a LOV that runs the query:
select CSPRACHE as d,
CSPRACHE as r
from JBSPR
order by 1
The table JBSPR exists in both environments!
The thing is, to access this table in production we need to add the schema LSPROD. So if we have the query with the schema, there is no problem here:
select CSPRACHE as d,
CSPRACHE as r
from LSPROD.JBSPR
order by 1
The problem seems to be that we need to put the schema in every query of the application but we can't put it hardcoded...
To solve this we tried to user an substitution string #FLOW_OWNER#
select CSPRACHE as d,
CSPRACHE as r
from #FLOW_OWNER#.JBSPR
order by 1
According to https://docs.oracle.com/database/apex-5.1/HTMDB/HTMDB.pdf
2.9.4.36 SCHEMA OWNER If you are generating calls to applications from within your PL/SQL code, you must reference the owner of the Oracle Application Express schema. The following describes the correct syntax for a direct PL/SQL reference:
APEX_APPLICATION.G_FLOW_SCHEMA_OWNER
You may also use #FLOW_OWNER# to reference this value in SQL queries and PL/SQL (for example, in a region or a process).
Unfortunately this is also not working.
Do anyone have any idea or had a similar situation, that can help?
Thank you,
Soraia