kvsinfo wrote:Always post code using <tt>\
I'm using Apex 4.2, 11gr2 DB and theme 24
I have a an EMP lov with a select statement below and I want to put the schema name but its based on an item on my page named p1_schema.
select ename, empno from emp
I tried the statement below and I'm getting an invalid SQL.
</tt> tags as described in the FAQ:
The static text exact substitution method of referencing the value of an APEX item requires a terminating dot (".") (even when the next character required by syntax is also a dot):
select ename,empno from &p1_schema.emp
select ename,empno from &P1_SCHEMA..emp
I have an interactive report that is based on the emp and it has source name ofI'm not keen on <tt>#OWNER#</tt>, and even less so on attempts to dynamically switch schemas. This is usually a sign of poor design (use one schema and VPD for multi-tenant environments) or false economy in development environments.
#OWNER#"."emp" and this seems to working just fine.
kvsinfo wrote:You get an error in the App Builder because there's no P101_SCHEMA value in the builder's session state, so <tt>&P101_SCHEMA.</tt> evaluates to NULL, making the LOV query invalid SQL.
I tried the code below but I'm still getting an error
select ename,empno from &p101_schema..emp
where P101_SCHEMA has been sanitized for SQL injection.
return 'select ename, empno from ' || :p101_schema || '.emp';
The main reason why I need multiple application schema with the same table structure is just imagine you have multiple companies accessing the same application. One way of doing this is to have 1 DB and APEX app but if you have 100 companies then that would be a lot of maintenance. I'm trying to do now is to have 1 DB and Apex APP to handle all those by dynamically changing the application schema. if you have userA to userZ (26 application schema) and you have an interactive report based on the emp if you can somehow user <item>.table name then that would solve all my issues I think.Multi-tenancy using VPD is even simpler: 1 DB, 1 app, and 1 schema.
kvsinfo wrote:Hosting applications for 100 companies looks enterprise level to me.
VPD is not an option since this requires an Enterprise version of the DB which so much expensive and also it creates some performace, security and backup issues.
By creating a separate schema the backup is so much faster, easier and smaller since you can easily create an exp/imp of each schema whereas VPD would require the whole DB or 1 huge data since all data is in 1 schema.But don't all 100 schemas have to be backed up? And there's another enterprise option to deal with it: partitioning.
To load a new set of data for 1 company would be hard since you have to deal with all the tables whereas with separate schema you can just delete the schema and re-imp it.Partitioning again.
Any DML would be longer too since 1 huge table instead of multiple tables located in separate schema.Partitioning would help here too. Additionally, time saved by not having to maintain multiple schemas and getting APEX to do unnatural things could be spent on designing and tuning for enhanced performance.