3 Replies Latest reply: Feb 25, 2013 9:13 PM by user8812684 RSS

    How to get Apex App to run oracle procedure as APP_USER

    user8812684
      Hi

      I am very new to oracle APEX. I have just created a very basic apex app that picks up a text file from the file server, loads the data into a table and does some processing and validation on the data. I am using APEX verion 4.1 using an oracle 11.1.0.7 database on windows.

      First i use the automatically created login form (the standard one you get when you create a new app) to login to my app. Once login is successfull it displays the data processing page. To load the data from a text file i created a text field where i enter my file name and i then have a load button. When the button is pressed i call a dynamic action where i execute a packaged procedure from my database. The data loads ok with no issues. The data load procedure calls sqlloader to load the data.

      eg. begin
      my_package.Uploading_data(:SELECT_FILE);
      end;


      On the same page i then have a second button to do the validation and processing of the data. Once again when the button is pressed it calls a dynamic action where it executes a packaged procedure from my database.

      eg. begin
      my_package.process_data;
      end;

      The problem i have is when the button is pressed and the procedure is executed it inserts the valid and invalid data into seperate tables as the APEX_PUBLIC_USER and not the APP_USER or user i loggged on with. I need it to insert the data as the logged in user so i can see who has loaded data. I can't work out how to execute a packaged procedure stored in the database from my apex app as the app_user or logged in user.

      I should probably note that the procedure above executes insert statements on tables that have insert triggers that automatically populate a created_by, created_date , updated_by and updated_date columns when each row of data is inserted. As mentioned above these triggers are inserting the APEX_PUBLIC_USER and not the logged on user.

      I have setup and use a "Database Account" authentication scheme type (in shared components) to validate my login page.

      As i mentioned i am new to APEX and still learning so any help to solve this problem would be much appreciated.

      Thanks
      Anthony

      Edited by: user8812684 on 24/02/2013 20:51
        • 1. Re: How to get Apex App to run oracle procedure as APP_USER
          swesley_perth
          Short answer, you probably want to insert the value returned from v('APP_USER'), not USER as you normally might in your PL/SQL.

          Long answer starts with needing to understand the infrastructure is different to what you may be used to. APEX users are catalysts for authentication, and everything is parsed as the application's parsing schema using the APEX_PUBLIC_USER account.
          http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35125/sec_authentication.htm#BABHIEIA

          Welcome to the world of APEX.

          Scott.
          • 2. Re: How to get Apex App to run oracle procedure as APP_USER
            Vite DBA
            Hi Anthony,

            where your created by triggers are referencing the database USER, they should instead be referencing nvl(v('APP_USER'),USER). If the session doing the DML is initiated through Apex then it will pick up the Apex user from v('APP_USER'). Alternatively if the session is initiated outside Apex (EG SQLPlus) then v('APP_USER') will be null and the database USER will be returned.

            Regards
            Andre
            • 3. Re: How to get Apex App to run oracle procedure as APP_USER
              user8812684
              Hi Andre

              Thanks very much for you advice. What you suggested worked perfectly. I simply added NVL(v('APP_USER'),USER) to my insert trigger as shown below and the insert/update trigger on my table populated the created_by and updated_by columns with the logged in APEX user as shown below.


              TRIGGER LARS_INS_UPD1

              BEFORE INSERT OR UPDATE
              ON WSP_LAB_RESULTS
              REFERENCING OLD AS OLD NEW AS NEW
              FOR EACH ROW
              DECLARE

              l_today date := sysdate;
              l_user varchar2(30) := NVL(v('APP_USER'),USER);
              BEGIN
              if inserting then
              if :new.created_by is null
              then -- If user is not supplied, use currently logged on user
              :new.created_by := l_user;
              end if;
              :new.created_date := l_today;
              end if;
              if :new.last_updated_by is null
              then -- If user is not supplied, use currently logged on user
              :new.last_updated_by := l_user;
              end if;
              :new.last_updated_date := l_today;
              END;


              Also thanks to Scott for the theory about Authentication. Now i understand what is happening.


              cheers
              Anthony

              Edited by: user8812684 on 25/02/2013 19:13