• Using ApEx (HTML-DB) with AJAX - Steve Karam


• Running an On Demand Process from a Page Request

-- Definitions
select * from APEX_DICTIONARY;
select * from APEX_WORKSPACES;
select * from APEX_APPLICATIONS ORDER BY WORKSPACE, APPLICATION_ID;
select * from APEX_WORKSPACE_APEX_USERS;
select * from APEX_WORKSPACE_SCHEMAS;

-- Development
select * from APEX_DEBUG_MESSAGES;
select * from APEX_DEVELOPER_ACTIVITY_LOG;
select WORKSPACE_NAME, USER_NAME, IS_ADMIN, IS_APPLICATION_DEVELOPER, ACCOUNT_LOCKED  from APEX_WORKSPACE_DEVELOPERS ORDER BY 1,2;

-- Environment
select * from APEX_RELEASE;
select * from APEX_PATCHES;
select * from APEX_INSTANCE_PARAMETERS;
select * from APEX_WORKSPACE_SESSIONS;

-- Logging
select * from APEX_USAGE_METRICS;
select * from APEX_WORKSPACE_ACCESS_LOG;
select * from APEX_WORKSPACE_ACTIVITY_LOG;

References
Lightweight Directory Access Protocol (v3): UTF-8 String Representation of Distinguished Names

Custom Domain Name (URL) with your own ORDS on the Webserver

Find Apex Installations

SELECT * FROM ALL_USERS WHERE USERNAME LIKE 'APEX%';

Find Apex Workspaces

SELET WORKSPACE FROM APEX_nnnnnn.APEX_WORKSPACES;

Find Apex Users

SELECT USER_NAME FROM APEX_nnnnnn.WWV_FLOW_FND_USER;

In this case I had to dynamically choose the page to branch to and set 2 parameters dynamically...

You will need to update the variables for your particular application, but this includes all the placeholders for the different sections of the URL. Use the "Branch To Function Returning URL" type of branch and modify the following code to suit your application:

DECLARE
  my_url VARCHAR2(256) := 'f?';
BEGIN
  -- Application
  my_url := my_url || 'p='||v('APP_ID') ||':';
  -- Page
  my_url := my_url || :P_TARGET_PAGE || ':';
  -- Session
  my_url := my_url || v('APP_SESSION') ||':';
  -- Request
  my_url := my_url || v('REQUEST') || ':';
  -- Debug
  my_url := my_url || v('DEBUG') || ':';
  -- ClearCache
  my_url := my_url || ':';
  -- itemNames
  my_url := my_url || 'P'||:P_TARGET_PAGE||'_SELECT_START,';
  my_url := my_url || 'P'||:P_TARGET_PAGE||'_SELECT_END:';
  -- ItemValues
  my_url := my_url || :P_SELECT_START||','||:P_SELECT_END;

  return my_url;
END;

Resource Handler PL/SQL

• Create a simple handler with 2 parameters, :id and :value.
• Here is standard code with error handling that returns HTTP return codes to API caller.

DECLARE
    retval integer := 1;
BEGIN

    retval := UPDATEPROC(:id, :value);

    IF retval = 0 THEN
      -- No errors
      COMMIT;
      :status_code := 200;
    ELSE
      -- Error of some type that we detected in UPDATEPROC
      :string_out := 'An error occurred on the server side: ' || SQLERRM;
      :status_code := 500;
    END IF;


EXCEPTION
    WHEN OTHERS
    THEN
        -- Error detected via PL/SQL Exception
        :string_out := 'An error occurred during processing: ' || SQLERRM;
        :status_code := 500;
END;

Purpose
A quick example of how to render and decode checkboxes in an Apex page.
Example Objects
EMP table from Scott example schema.
Set up the interface
Use the APEX_ITEM.CHECKBOX function to render checkboxes in a report.

An example query:

SELECT ENAME, 
       APEX_ITEM.CHECKBOX(1,empno, Selected) as CommissionAllowed
  FROM ( select emp.*, 
                (SELECT 'CHECKED' FROM DUAL WHERE comm IS NOT NULL) as Selected 
           FROM EMP )
 ORDER BY 1;

This will produce output that will render checkboxes. The checkbox will be selected if the EMP. COMM field is not null. Sort of a useless example, but you can see what we're getting at.

ENAME	COMMISSIONALLOWED

ADAMS	<input type="checkbox" name="f01" value="7876"  />
ALLEN	<input type="checkbox" name="f01" value="7499" CHECKED />
BLAKE	<input type="checkbox" name="f01" value="7698"  />
CLARK	<input type="checkbox" name="f01" value="7782"  />
FORD	<input type="checkbox" name="f01" value="7902"  />
...

Set up the processing code

Now once your page is submitted you want to review the submitted items and act upon them.

Create a Page Process after submit. Make sure to use the Conditional Processing section to attach the process to a button press.

Note that after the page submit we will only be notified which checkboxes have 'CHECKED' selected. If you need to find the difference between before and after states you may need to do some fancy logic. Here I am just deleting all the records from COMM_ALLOWED and repopulating. Again, maybe a useless example, but you can see how we get the data out of the interface and work with it in PL/SQL.

DELETE FROM COMM_ALLOWED;

FOR I in 1..APEX_APPLICATION.G_F01.COUNT LOOP
    INSERT INTO COMM_ALLOWED (
        EMPNO
    ) VALUES (
        APEX_APPLICATION.G_F01(i)
    );

END LOOP;

Force Uppercase
In HTML Form Element Attributes enter the following:

onChange="javascript:this.value=this.value.toUpperCase();"