How to change parsing schema in Oracle Apex?

Question

OrclQA Wiki

View Profile

1

OrclQA WikiProfessional

Asked: October 4, 20202020-10-04T12:36:40+05:30 2020-10-04T12:36:40+05:30

How to change parsing schema in Oracle Apex?

1

I am using the custom authentication in Oracle Apex and after the user login, I need to change the parsing schema we assigned to the user.

How can I do that?

10 Answers

You must login to add an answer.

Vinish Kapoor · Answer 1 · 2020-10-04T12:47:24+05:30

You can change the parsing schema using the apex_util.set_parsing_schema_for_request procedure from within the application's initialization code. Follow these steps:

Click on the Shared Components > Security Attributes > Database Session.

Then add the following PL/SQL code in the Initialization PL/SQL code section:

Declare
   v_parsing_schema varchar2(30);
Begin
   Select user_schema into v_parsing_schema
     from your_user_table
     where username = :APP_USER;

apex_util.set_parsing_schema_for_request (
   p_schema => v_parsing_schema);
End;

Change the table and column name according to your table.

Below is the screenshot for reference:

Hubert · Answer 2 · 2021-02-04T16:11:47+05:30

Hi Vinish,

Would like to further extend the functionality by providing a facility to automate the creation of a schema and assign it to the workspace.

Please assume that for this scenario, there is a table with customer name and schema name.

The requirement is to have a separate form with one item and one button

Customer name (text field)

Create Schema (button)

On clicking the button, the system will create a schema (name would be a random generated text of 10 chars) and insert the customer name and the schema name in the customer table.

PL/SQL code is required for execution on click on button.

Please let me know if any further clarifications are required.

Is this possible?

Regards

Hubert

Vinish Kapoor · Answer 3 · 2021-02-04T16:36:57+05:30

We can create the schema at runtime if the user has the related privilege. But I am not sure about assigning to the current workspace. Maybe you can try to logout after creating the schema so that the user can log in again and then it will be assigned as per the above procedure. Below is an example PL/SQL code:

Declare
  v_schema varchar2(10);
Begin
  v_schema := dbms_random.string('v', 10);
  insert into customer values (:p2_customer, v_schema);
  execute immediate 'Grant connect, resource to '||v_schema 
       ||' identified by '||v_schema;
  apex_authentication.logout(:SESSION, :APP_ID);
End;