Forum Stats

  • 3,769,363 Users
  • 2,252,957 Discussions
  • 7,875,005 Comments

Discussions

Categories

How to use one application with multiple schema without copying application?

T101_cyberdyne
T101_cyberdyne Member Posts: 54
edited Jun 13, 2013 10:22PM in APEX Discussions

Hi,

Previously we are using oracle forms and by that we can manage by using a set of folders containing fmx and use different schema/database for different customers. so the source code comes from one individual file but used for different database users.

is it possible to do this without copying application in apex?

reason is because if applications are copied for each customer, and in a situation where a page has a bug, the developer must correct multiple pages across all the application. This would not be appropriate to manage.

could this be done in apex? or is there any other approach?

Tagged:
· Share on TwitterShare on Facebook

Answers

  • Vite DBA
    Vite DBA Member Posts: 600 Silver Badge

    Hi,

    An application is tied to its parsing schema, so it is not possible to have one code base which you can then point to different schemas. I have seen some threads relating to dynamically setting the parsing schema, but I don't think it has worked to well, and would not be a supported configuration by Oracle.

    The normal way to do this is to have one schema and for each entity where it is logical you will have an extra key which is the customer id. I mention where it is logical, because not every entity needs its own data defined by customer. Some data will be common across all customers, such as lookup data and some entities will comprise child entities by which the data separation will be implied by the parent. You can then use Oracle's Virtual Private Database feature to implement a seperate view of the data through the application, based most likely on the customer who is logged on.

    Hope this helps.

    Regards

    Andre

    · Share on TwitterShare on Facebook
  • Scott Wesley
    Scott Wesley Member Posts: 6,127 Gold Crown

    Interesting problem. There are probably a few avenues that need addressing.

    One thought that came to mind was how the wizards sometimes default report SQL to #OWNER#.table_name.

    You might be able to persue something like that to parameterise most schema names.

    A VPD solution might involve some ugly looking views with UNION ALLs.

    What authentication scheme is being used?

    · Share on TwitterShare on Facebook
  • Luis Cabral
    Luis Cabral Member Posts: 973
    edited Jun 13, 2013 2:59AM

    Hi,

    Are we talking about a handful of customers or dozens? If only a few, I think the easiest way is to have a "master" application (which should be the only one manually changed) and a number of copied applications. Every time the master application is modified, it is just a case of exporting/importing it into the other applications, which ideally should be set to "run only". Maybe it could even be possible to automate this process.

    By the way, how does the form solution work? Each customer has only one login (username/password) which corresponds directly to a database schema?

    Luis

    · Share on TwitterShare on Facebook
  • user9344114
    user9344114 Member Posts: 5

    Hello Andre.

    That was one of my 1st questions - as I begun with APEX some days ago.

    We have many tables under other schemas; all provided with public synonyms and grants. Those tables are visible and accessible by all ORACLE-Users (via Forms and Reports) - but not via APEX.

    Why? What is missing?

    How to make those tables visible in APEX?

    Of course we do not want to create all that stuff again and loose the existing.

    Thanks

    Wolfgang


    · Share on TwitterShare on Facebook
  • Scott Wesley
    Scott Wesley Member Posts: 6,127 Gold Crown

    user9344114 - you should post this as a separate question

    · Share on TwitterShare on Facebook
This discussion has been closed.