Search code examples
oracle12coracle-apex-5

Not Able change parsing schema at run time in oracle apex 5

I am using Oracle apex 5,oracle database 12c

I have successfully configured oracle apex 5 with oracle DB 12c.

I have created authentication scheme using database table,that Authentication scheme worked successfully.

But my requirement is - Each user has to be connect to its own schema (eg.user1 = HR; user2 = SCOTT) with in same application.

Shortly, application must run on multiple schemas at run time.

But I am not able to get that,I have tried below stuff -

current parsing schema is 'SCOTT' try to change it using -

  1. apex_application.g_flow_owner := 'HR'; --Failed
  2. ALTER SESSION SET CURRENT_SCHEMA = 'HR'; --Failed

I don't understand what to do,Please some body help me for solving it.

Solution

  • I think you are on the right track, the apex_application.g_flow_owner := 'HR'; command should do the trick but you have to place it in shared components > security > security attributes> database session > Initialization PL/SQL Code

    Edit: First of all having a schema for each user that logs into the application i do not think is the best approach. Just think that every modification has to be done to all the schemas. I suggest you take a look at Virtual Private Database (VPD) it can help you to control data access.

    But if you still want to try changing the schema i think you can do it like this. Create two processes for each page in your application; one at On Load Before Header and one at On Submit. This process should contain something like this:

    BEGIN
if :APP_USER='SCOTT' THEN
apex_application.g_flow_owner := 'SCOTT';
ELSE
apex_application.g_flow_owner := 'HR';
END IF
END;

    Like this when Scott loads a page the schema is changed to SCOTT and he sees data from his schema. When HR loads a page the schema is changed to HR and he sees his data. Same thing when they submit a page; the schema first changes and then you do the other operations.

    This second idea is not bullet proof and that's why i advise you to rethink what you want to do.

    Edit2: In component view simply click on the plus sign on "Processes" to add a process and in the wizard select "On Submit - Before Computations and Validations" for the Point option. enter image description here