Search code examples
databaseoracleserveroracle-apex-5

Create a new Oracle Application Express (APEX)

Let me explain my situation. Now I need to create a new APEX application. But I am not using the APEX SQL Workshop (Figure 1) as the database. The data is being retrieved through package. Please refer to the hierarchy design in Figure 2. From the hierarchy, the data are stored in Table_A in D1EDATA. D1EDATA has a package called Package_A. D1APEX is the application. It calls the package to select, update or delete the records stored in Table_A

Figure 1: Development Environment

enter image description here

Figure 2: Application Architecture Design

enter image description here

My questions as below:

  1. How can I call the function/procedure in a package from APEX application?
  2. How can I get the logged in user id. Which table is storing the APEX user details?
Solution
    1. First question is quite simple: to run function/procedure in apex application, you have to do following:
      • create a page in your application (or use previously created)
      • click + button near the Regions label to create new region;
      • in create region wizard, choose Form - Form on a procedure
      • on next steps choose your procedure/function and fill other required fields.

    Apex will create a form with fields for every procedure/function parameters and with buttons Run (I don't remember exact name) and Cancel.

    1. Your second question is quite big. First of all, it depends on your authentication scheme. There is a lot of options: database accont, APEX account, custom, LDAP and many others.
      • database account - you use accounts of database to log in into your application. In this case apex uses logins and passwords in system oracle tables.
      • APEX account - you use developer aacounts to log in into APEX IDE and the same accounts - to log in into the application.
      • custom scheme - you create yourself all tables, procedures for check user login and password, etc.

    UPD
    You can define name of your user (regardless of current authentication scheme) in PL/SQL code with v function: v('APP_USER'). But remember, this function returns NULL, if you call it not from APEX application. There is a universal way to call it (in UPDATE statement):

    update mt_table set username = nvl(v('APP_USER'), user);