APEX_ADMINISTRATOR_ROLE

The APEX Dictionary is a set of views that describe all the different objects of an APEX application. They are extremely useful when trying to compare objects or using the metadata in your application. One example, which I recently wrote about, is to use a view from the dictionary to leverage the APEX build options in your PL/SQL code.

By default the views will only allow you to see information for applications, and their objects, that are linked to your current schema (i.e. the application's parsing schema must be the same as your schema). For older versions of APEX the only way to view all the applications in the entire database was to either log in as SYSTEM or SYS.

In newer versions of APEX (I think it was released in APEX 4.1) there's a new database role called APEX_ADMINISTRATOR_ROLE. This role allows for non SYSTEM/SYS users to view all the APEX applications in your database. It's a very useful thing to have if you want to run your own scripts to check for things like standards, security audits, performance, etc.

One example where this role can be very useful is to monitor for slow running pages in all your applications across the entire database (rather than just ones in a particular schema). The following query, executed by a user that has the APEX_ADMINISTRATOR_ROLE, will show all the slow pages in the past two days:

SELECT *
FROM apex_workspace_activity_log
WHERE trunc(view_date) >= trunc(SYSDATE) - 1 -- Just look at the past 2 days
  AND elapsed_time > 1; -- 1 = 1 second

This is just one of many examples where the APEX_ADMINISTRATOR_ROLE can be
useful for system wide level analysis.

The APEX_ADMINISTRATOR_ROLE also allows you to run procedures in the APEX_INSTANCE_ADMIN package.

How to Send/Upload a CLOB from the Browser to APEX via AJAX

Today Alistair Lang asked on Twitter "how do I pass in a CLOB to an on-demand process using AJAX in APEX?". I had this same questions a few months ago when I was working on uploading files using AJAX into APEX.

It turns out you can't use the standard addParam APEX JavaScript method (hopefully this will change in 4.2). Instead you need call a different function which will store the CLOB into a special APEX collection then process the CLOB from that collection. Here's a breakdown of what needs to happen:

- Send the CLOB from the browser to APEX. It will be stored in the CLOB001 column in the collection "CLOB_CONTENT".
- Once the CLOB is sent to APEX call your On Demand process (i.e. AJAX request) to run some PL/SQL code. This PL/SQL code will need to retrieve the CLOB value from the collection

Here's an example

On Demand Process: On your page create an On Demand process called "MY_PROCESS". In the code enter the following:

DECLARE
  l_clob CLOB;
BEGIN
  SELECT clob001 
  INTO l_clob
  FROM apex_collections 
  WHERE collection_name = 'CLOB_CONTENT';
  
  -- Now you can process the CLOB using l_clob
END;

JavaScript Code: This can be stored either in a Dynamic Action or custom JS code:

/**
 * Code to run once the upload is done
 * 
 * Clob is now accessible in the apex_collections view:
 * SELECT collection_name, seq_id, clob001 FROM apex_collections 
 * WHERE collection_name = 'CLOB_CONTENT';
 *  - Note: The collection name "CLOB_CONTENT" is not modifiable
 * 
 * Use this function to make an AJAX request to trigger 
 * an On Demand Process (i.e. run some PL/SQL code)
 */
function clubUploadDone(){
  var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=MY_PROCESS',$v('pFlowStepId'));
  //Optional: pass some additional values get.addParam('x01','some data');
  gReturn = get.get();
}

/**
 * Send clob to APEX (will be stored in the apex_collection "CLOB_CONTENT"
 */
var clobObj = new apex.ajax.clob(
  //Callback funciton. only process CLOB once it's finished uploading to APEX
  function(p){
    if (p.readyState == 4){
      clubUploadDone();
    }
  });
  
clobObj._set('Put clob content here'); //Sends the data to Oracle/APEX collection

It's important to note that there is only one area where the CLOB data is stored so each time you send a new CLOB it will erase the older value in the collection. If you're sending multiple CLOBS sequentially you need to handle it accordingly. A good example of this is if you're uploading multiple files via a drag & drop interface.

Setting SYSDATE in Oracle for Testing

A while ago two very smart guys (Cristian Ruepprich and Carsten Czarski) had a conversation on Twitter about how to modify the value of SYSDATE in Oracle for testing purposes. The ability to modify the value of SYSDATE can be very valuable if you have to do time-sensitive testing.

Thankfully they did have a solution, by setting the FIXED_DATE system parameter. Here's an example on how to use it:

DECLARE
SQL> SELECT SYSDATE FROM dual;

SYSDATE
--------------------
22-AUG-2012 10:20:19

SQL> -- Do this now as priviledged user (ex: SYSTEM)
SQL> -- Note: this affects the entire database (not just your session)
SQL> ALTER SYSTEM SET fixed_date='2010-01-01-14:10:00';

System altered.

SQL> SELECT SYSDATE FROM dual;

SYSDATE
--------------------
01-JAN-2010 14:10:00

SQL> -- Reset SYSDATE back to "Current Time"
SQL> ALTER SYSTEM SET fixed_date=NONE;

System altered.

SQL> SELECT SYSDATE FROM dual;

SYSDATE
--------------------
22-AUG-2012 10:21:29

They're a few things to know about FIXED_DATE before using it:

- Setting it requires access to ALTER SYSTEM. This can be mitigated by creating a procedure to handle this as a privledged user (see Tom Kyte's suggestion here).

- It affects the entire system, not just your session. If you have multiple users testing on a system then you may not be able to use it. Hopefully in the future we'll be able to modify it at a session level.

- It only affects SYSDATE and not date/time functions such as systimestamp (see Sean's comment below

The documentation for FIXED_DATE can be found here.

SQL%ROWCOUNT and Logger

Logger (by Tyler Muth) is a free open source logging tool for PL/SQL. Tyler initially launched it in 2009 and now it has become a staple tool for many Oracle development teams. If you've never heard of it go check it out.

When using logger I like to log the number of rows an update statement made after the update was performed. In order to do that I use the SQL%ROWCOUNT variable. The thing to be aware of is if you log SQL%ROWCOUNT using logger it will be "reset" by the implicit insert that logger does.  In the example below you'll notice that after calling logger the SQL%ROWCOUNT now has a value of 0 since it does an insert:

SQL> BEGIN
  2    UPDATE emp
  3    set sal = sal;
  4
  5    dbms_output.put_line('Rows updated: ' || SQL%rowcount);
  6    dbms_output.put_line('Rows updated: ' || SQL%rowcount);
  7    logger.log('Rows updated: ' || SQL%rowcount);
  8    dbms_output.put_line('Rows updated: ' || SQL%rowcount);
  9  END;
 10  /
Rows updated: 14
Rows updated: 14
Rows updated: 0

PL/SQL procedure successfully completed.

SQL> SELECT text
  2  FROM logger_logs
  3  WHERE ROWNUM = 1
  4  ORDER BY ID DESC;

TEXT
------------------
Rows updated: 14

This is important to know because sometimes you may do some additional work after an update statement depending on how many records were updated in the previous statement. You should change your code from:

UPDATE emp
SET sal = sal;
logger.log('Rows updated: ' || SQL%rowcount);

IF SQL%rowcount > 0 THEN
  ...
END IF;

To:

UPDATE emp
SET sal = sal;
l_row_count := SQL%rowcount;
logger.LOG('Rows updated: ' || l_row_count);

IF l_row_count > 0 THEN
  ...
END IF;

Integrating Build Options in your PL/SQL Code

Build Options is a great tool to enable or disable certain objects in APEX. If you've never used them before or don't know what they are I suggest you read Scott Wesley's article.

I typically use Build Options to display data for developers that should not be displayed in production. This can help improve development time since you don't have at include, then remove, code in an application before it is migrated to production.

Build Options are great for APEX but what about PL/SQL code that is directly associated with an APEX application? You could enable and disable certain page processes with build options but that doesn't allow "fine grained Build Option" control in your PL/SQL code.

Thankfully you can view your Build Options, and their status, with an APEX Dictionary view:  APEX_APPLICATION_BUILD_OPTIONS.

Here is a function that I wrote that will allow you to easily see if a given Build Option is enabled in your PL/SQL code:

/**
 * Returns Y or N if a build option is enabled
 *
 * @param p_build_option_name Name of build option (case sensitive)
 *  - You can change this to not be case sensitive if applicable
 * @param p_app_id Application ID, default current Application ID
 *  - Included this as a parameter in case testing from straight PL/SQL
 * @return Y or N
 * @author Martin Giffy DSouza http://www.talkapex.com
 * @created 15-Aug-2012
 */ 
create or replace FUNCTION f_is_build_option_enabled(
  p_build_option_name IN apex_application_build_options.build_option_name%TYPE,
  p_app_id IN apex_application_build_options.application_id%TYPE DEFAULT nv('APP_ID'))
  return varchar2
AS
  l_build_option_status apex_application_build_options.build_option_status%type;
BEGIN

  SELECT upper(build_option_status)
  into l_build_option_status
  FROM apex_application_build_options
  WHERE application_id = p_app_id
    AND build_option_name = p_build_option_name;
    
  IF l_build_option_status = 'INCLUDE' THEN
    RETURN 'Y';
  ELSE
    RETURN 'N';
  END IF;
exception
  WHEN others THEN
    -- Your call on how to handle errors
    raise;
END

Here's an example of how you can use it in your PL/SQL code. Note that it doesn't pass in the application id since it is assuming that you're executing the PL/SQL as part of the APEX application:

...
IF f_is_build_option_enabled(p_build_option_name => 'DEV_ONLY') = 'Y' THEN
  htp.p('DEV_ONLY build option enabled');
ELSE
  htp.p('DEV_ONLY build option disabled');
END IF;
...

NO_DATA_FOUND Exceptions in PL/SQL Functions Called in Queries

Functions in PL/SQL can be a tricky thing as they don't always raise an exception when one occurs. You may be asking yourself: How, I thought functions and procedures always raise exceptions when one occurs? This is true except in some very specific situations.

First lets look at a very simple function which is then executed in both PL/SQL and in a query. It should (and does) raise an exception in both cases since we have a 1/0 in it.

SQL> CREATE OR REPLACE FUNCTION f_generate_error RETURN VARCHAR2
  2  AS
  3    l_x pls_integer;
  4  BEGIN
  5    SELECT 1 INTO l_x FROM dual WHERE 1/0 = 0;
  6    RETURN 'No exception raised';
  7  END;
  8  /

Function created.

SQL> EXEC dbms_output.put_line(f_generate_error);
BEGIN dbms_output.put_line(f_generate_error); END;

*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "MARTIN.F_GENERATE_ERROR", line 5
ORA-06512: at line 1


SQL> SELECT f_generate_error FROM dual;
SELECT f_generate_error FROM dual
       *
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "MARTIN.F_GENERATE_ERROR", line 5

What if we change the query slightly so that the SELECT INTO statement doesn't return any rows (by applying a 1=0 predicate) and raises a NO_DATA_FOUND exception?

SQL> CREATE OR REPLACE FUNCTION f_generate_error RETURN VARCHAR2
  2  AS
  3    l_x pls_integer;
  4  BEGIN
  5    SELECT 1 INTO l_x FROM dual WHERE 1=0;
  6    RETURN 'No exception raised';
  7  END;
  8  /

Function created.

SQL> EXEC dbms_output.put_line(f_generate_error);
BEGIN dbms_output.put_line(f_generate_error); END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "MARTIN.F_GENERATE_ERROR", line 5
ORA-06512: at line 1


SQL> SELECT f_generate_error FROM dual;

F_GENERATE_ERROR
----------------------------------------------------------------

1 row selected.

When we run it in PL/SQL it raises an exception but when we run it in a query it doesn't. It just returns a null value which doesn't really tell the calling query that an exception was raised. This can obviously cause issues and unexpected behavior in your application.

According to the Oracle documentation the NO_DATA_FOUND exception will not propagate the exception if run in a query: "Because this exception is used internally by some SQL functions to signal that they are finished, you should not rely on this exception being propagated if you raise it within a function that is called as part of a query."

To get around this issue you can raise a custom exception when you encounter a NO_DATA_FOUND exception in functions so that it will propagate when called via a SQL query. In the example below a custom exception is raised and the exception is propagated when called from a query.

SQL> CREATE OR REPLACE FUNCTION f_generate_error RETURN VARCHAR2
  2  AS
  3    l_x pls_integer;
  4  BEGIN
  5    SELECT 1 INTO l_x FROM dual WHERE 1=0;
  6    RETURN 'No exception raised';
  7  EXCEPTION
  8    WHEN NO_DATA_FOUND THEN
  9      raise_application_error(-20001, 'Custom NO_DATA_FOUND');
 10  END;
 11  /

Function created.

SQL> SELECT f_generate_error FROM dual;
SELECT f_generate_error FROM dual
       *
ERROR at line 1:
ORA-20001: Custom NO_DATA_FOUND
ORA-06512: at "MARTIN.F_GENERATE_ERROR", line 9

How to Create an APEX Session in PL/SQL

Debugging a function or procedure which references APEX items using the V/NV functions or trying to debug APEX collections can be frustrating when the only way to set, and view, the values is to actually do it through the application. Thankfully, there's a way to create an APEX session from a PL/SQL session to test out your code.

They're various examples on the APEX forums on how to create an APEX session in PL/SQL. Here's one version of it.

CREATE OR REPLACE PROCEDURE sp_create_apex_session(
  p_app_id IN apex_applications.application_id%TYPE,
  p_app_user IN apex_workspace_activity_log.apex_user%TYPE,
  p_app_page_id IN apex_application_pages.page_id%TYPE DEFAULT 1) 
AS
  l_workspace_id apex_applications.workspace_id%TYPE;
  l_cgivar_name  owa.vc_arr;
  l_cgivar_val   owa.vc_arr;
BEGIN

  htp.init; 
  
  l_cgivar_name(1) := 'REQUEST_PROTOCOL';
  l_cgivar_val(1) := 'HTTP';
  
  owa.init_cgi_env( 
    num_params => 1, 
    param_name => l_cgivar_name, 
    param_val => l_cgivar_val ); 
    
  SELECT workspace_id
  INTO l_workspace_id
  FROM apex_applications
  WHERE application_id = p_app_id;

  wwv_flow_api.set_security_group_id(l_workspace_id); 

  apex_application.g_instance := 1; 
  apex_application.g_flow_id := p_app_id; 
  apex_application.g_flow_step_id := p_app_page_id; 

  apex_custom_auth.post_login( 
    p_uname => p_app_user, 
    p_session_id => null, -- could use APEX_CUSTOM_AUTH.GET_NEXT_SESSION_ID
    p_app_page => apex_application.g_flow_id||':'||p_app_page_id); 
END;

To create an APEX session (in PL/SQL) to mimic some tests I can do the following:

SQL> BEGIN
  2    sp_create_apex_session(
  3      p_app_id => 106,
  4      p_app_user => 'MARTIN',
  5      p_app_page_id => 10);
  6  END;
  7  /

PL/SQL procedure successfully completed.

View some APEX session state variables:

SQL> SELECT v('APP_USER') app_user, v('APP_SESSION') app_session,
  2    v('APP_PAGE_ID') page_id, v('P1_X') p1_x
  3  FROM dual;

APP_USER   APP_SESSION     PAGE_ID    P1_X
---------- --------------- ---------- ----------
MARTIN     374363229560201 10

SQL> -- Set P1_X
SQL> exec apex_util.set_session_state('P1_X', 'abc');

PL/SQL procedure successfully completed.

SQL> SELECT v('APP_USER') app_user, v('APP_SESSION') app_session,
  2    v('APP_PAGE_ID') page_id, v('P1_X') p1_x
  3  FROM dual;

APP_USER   APP_SESSION     PAGE_ID    P1_X
---------- --------------- ---------- ----------
MARTIN     374363229560201 10         abc

SQL> -- Clear APEX Session State
SQL> exec APEX_UTIL.CLEAR_APP_CACHE(p_app_id => 106);

PL/SQL procedure successfully completed.

You can also create and view collections using the APEX_COLLECTION APIs and the APEX_COLLECTION view.