This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Jan 24, 2013 11:21 PM by fac586 RSS

How to specify application schema in a table select

980912 Newbie
Currently Being Moderated
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.

Original SQL
select ename, empno from emp

I tried the statement below and I'm getting an invalid SQL.

select ename,empno from &p1_schema.emp

I have an interactive report that is based on the emp and it has source name of
#OWNER#"."emp" and this seems to working just fine.

Thanks in advance
  • 1. Re: How to use a substitution string in a select statement
    Howard (... in Training) Pro
    Currently Being Moderated
    I think it's difficult to use an additional schema but I don't have a handle on all the details. But please see {message:id=10682798}.

    Howard
  • 2. Re: How to use a substitution string in a select statement
    Howard (... in Training) Pro
    Currently Being Moderated
    You might get more help by (closing this thread and) starting a new one that has the words "Specifiying Application Schema" in the title. The current title doesn't reflect the problem -- as I think of it.

    Best wishes,
    H
  • 3. Re: How to use a substitution string in a select statement
    fac586 Guru
    Currently Being Moderated
    kvsinfo wrote:
    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.

    Original SQL
    select ename, empno from emp

    I tried the statement below and I'm getting an invalid SQL.
    Always post code using <tt>\
    ...\
    </tt> tags as described in the FAQ:
    select ename,empno from &p1_schema.emp 
    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
    I have an interactive report that is based on the emp and it has source name of
    #OWNER#"."emp" and this seems to working just fine.
    I'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.
  • 4. Re: How to use a substitution string in a select statement
    980912 Newbie
    Currently Being Moderated
    Hi,
    I tried the code below but I'm still getting an error

    (code) select ename,empno from &p101_schema..emp (code)

    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.
  • 5. Re: How to use a substitution string in a select statement
    TexasApexDeveloper Guru
    Currently Being Moderated
    As was suggested earlier, I would SUGGEST that you look at VPD and designing your database to allow for multiple companies within it. If you design the tables properly and also develop your vpd policies, then you can have multiple users hitting the tables with virtually no way for them to see anything but what they need to see/update/delete..

    Otherwise you will be trying to code for x number of schema's to develop on..

    Thank you,

    Tony Miller
    Ruckersville, VA
  • 6. Re: How to use a substitution string in a select statement
    fac586 Guru
    Currently Being Moderated
    kvsinfo wrote:
    Hi,
    I tried the code below but I'm still getting an error
     select ename,empno from &p101_schema..emp 
    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.

    Similar thing will happen in the app at runtime if a session state value for P101_SCHEMA hasn't been set.

    Change the LOV definition to the Function returning SQL query form:
    return 'select ename, empno from ' || :p101_schema || '.emp';
    where P101_SCHEMA has been sanitized for SQL injection.
    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.
  • 7. Re: How to use a substitution string in a select statement
    980912 Newbie
    Currently Being Moderated
    Hi,
    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.

    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.

    Any DML would be longer too since 1 huge table instead of multiple tables located in separate schema.

    Is there really no way to change the application schema dynamically?
  • 8. Re: How to use a substitution string in a select statement
    fac586 Guru
    Currently Being Moderated
    kvsinfo wrote:
    Hi,
    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.
    Hosting applications for 100 companies looks enterprise level to me.
    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.
  • 9. Re: How to use a substitution string in a select statement
    TexasApexDeveloper Guru
    Currently Being Moderated
    Sounds like the poster has Cadillac dreams, but a Volkswagen budget.. If you want to do the job RIGHT, you will be required to have the proper equipment and software. Trying to do what you want without an enterprise licensed version of the database is going to cost you more in end than with it.

    Your choice.. As the mechanic says, you can pay me now, or pay me later..

    Thank you,

    Tony Miller
    Ruckersville, VA
  • 10. Re: How to use a substitution string in a select statement
    980912 Newbie
    Currently Being Moderated
    Hi fac586,

    yes you are right about the partition to ease the backup/load and speed.

    Also you are right about the enterprise level and I guess my biggest fear is if APEX a right tool for us to convert from Oracle Forms considering we are doing an enterprise complex ERP applications?

    To make matter worst each schema can have multiple sets of our applications inside it. Schema A can have 5 sets of our application and this is handled via VPD though just a few cases like this but nonetheless has to be considered.
  • 11. Re: How to use a substitution string in a select statement
    980912 Newbie
    Currently Being Moderated
    Hi,
    Please note that our application can be installed via cloud which is using the Enteprise DB version but for on site this is not always the case.

    The thing is we need to come up with 1 solution for both cases since maintaining 2 sets of application is going to be impossible.
  • 12. Re: How to use a substitution string in a select statement
    TexasApexDeveloper Guru
    Currently Being Moderated
    Then I guess you have your hands full even trying to determine what development tool to use... Going from FORMS to ?? will be an interesting process.... Again, it sounds like you need to nail down your database DESIGN before even trying tackle what type of development tool you want to use..

    At this point, developing an application using the data model you have now would be a mistake in my honest opinion..

    Thank you,

    Tony Miller
    Ruckersville, VA
  • 13. Re: How to use a substitution string in a select statement
    980912 Newbie
    Currently Being Moderated
    Hi Tony Miller,

    As of now with Oracle Forms we have the flexibility of deploying our app in Enterprise or non-Enterprise DB version. In 1 physical DB we can have as much versions of our application with different patches and some using VPD and some not.

    As you can see from our current setup with 1 set of application source we have so much flexibility and able to support any environment.

    Now our goal is deploy it efficiently over the web and that is why I'm thinking of using APEX just to do our screens but all the business rules stays at the DB.

    I think APEX can handle it just a matter of knowing how. This why I need all the help I can get :)
  • 14. Re: How to specify application schema in a table select
    980912 Newbie
    Currently Being Moderated
    Hi,
    I think I know what is happening.

    1. I created a workspace "WS1" and application "app1" with schema "dbuser1" (this is my parsing schema)

    2. I then created a new schema "dbuser2" which is my custom application schema where all my tables are and assign this to workspace "ws1".

    workspace "ws1" is now assigned to 2 schemas, "dbuser1" and dbuser2".

    3. for my lov select I put
    (code)
    RETURN 'select ename,empno FROM ' || 'dbuser2' || '.emp'
    (code)

    please note I hard coded the schema name for now to be sure all is OK.

    If I run my app I get an error that the table does not exists.

    Do I need to grant the tables from "dbuser2" to my APEX app1 or to my parsing schema "dbuser1"?
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points