Home/Questions/Q 1686
In Process
OrclQA Wiki
OrclQA Wiki

OrclQA Wiki

View Profile
  • 1
Professional
Asked: 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?

oracle apexpasing schema

    10 Answers

    1. Vinish

      Vinish

      View Profile
      Enlightened

      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:

      Changing parsing schema in Oracle Apex.

      • 0
    2. Hubert

      Hubert

      View Profile
      Contributor

      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

       

      • 1
    3. Vinish

      Vinish

      View Profile
      Enlightened

      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;

       

      • 0
    5. amrit

      amrit

      View Profile
      Professional

      Hi Vinish,

      I am trying to parse schema while login.  it give me error

      "workspace has no privileges to parse as schema gnps22"  kindly guide me how to resolve it.

      Thanks

       

       

       

      • 0

    You must login to add an answer.