ODTUG APEX Plug-In Competition

A while ago Tobias Arnhold posted a tweet suggesting that we should have an APEX Plugins competition. After a few months, many emails, a lot of effort by an excellent group of people, and the support from ODTUG, I'm pleased to announce the first ever world wide APEX Plug-ins competition! Here's a short blurb from the official announcement on odtug.com:

ODTUG is holding its first ever world wide APEX Plug-ins competition! This is your opportunity to write an APEX plug-in and have it viewed by the entire international APEX community and maybe crowned 2012 ODTUG APEX Plug-in Developer of the Year! Even better, there are some really great prizes with the grand prize being a free pass to Kscope13 or a Jawbone Jambox!

This is your opportunity to showcase your Oracle APEX Plugin skills and win some really cool prizes. We also made a special Experts category to ensure everyone is on a level playing field.

I hope this encourages everyone to try to submit a plugin and help expand the list of already fantastic plugins.

Good luck!

Update: Here's the original tweet from Tobias!

q Function Inside a q Function

Two years ago I wrote about how to escape single quotes in string using the q function: http://www.talkapex.com/2009/03/q-function-escape-single-quotes.html If you've never seen this before or don't know what I'm talking about please read the article first before continuing.

I recently had an issue where I had to use a q function inside another q function. Here's what I tried:

DECLARE
  l_code varchar2(4000);

BEGIN
  l_code := q'!BEGIN dbms_output.put_line(q'!It's cold in Calgary!'); END; !';
  
  dbms_output.put_line(l_code);
  
  execute immediate l_code;
  
END;
/

ERROR:
ORA-01756: quoted string not properly terminated

You'll notice that the above code doesn't run. The reason it doesn't work is that I'm using the same character (!) as the quote delimiter. To fix this, each "Q" block must have it's own unique quote delimiter. Below is a copy of the same code but using two different quote delimiters (# and !).

DECLARE
  l_code varchar2(4000);

BEGIN
  l_code := q'#BEGIN dbms_output.put_line(q'!It's cold in Calgary!'); END; #';
  
  dbms_output.put_line(l_code);
  
  execute immediate l_code;
  
END;
/

BEGIN dbms_output.put_line(q'!It's cold in Calgary!'); END;
It's cold in Calgary

PL/SQL procedure successfully completed.

For more information on the q function (it's real name is "Q-quote mechanism") read the Oracle documentation: http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_sqltypes.htm#BABECADE and scroll down to the "Quoting Character Literals" section.

Things to Watch Out for When Upgrading an APEX Plugin

Now that APEX plugins have been around for a while I'm starting to notice that upgrades are being released for the same plugin. This is a natural occurrence in the lifecycle of software development and is expected.

Prior to upgrading an APEX plugin you should be aware of what will happen to the settings (also known as Custom Attributes) for your plugin before upgrading. Before I continue it's important to note the two different types of custom attributes a plugin can have.

Application: These attributes are applicable for the plugin as a whole across the entire application. Think of them as a global variable for the plugin

Component: These attributes are applicable for each instantiation of the plugin. For example, if the plugin is an item type plugin the settings found while editing the item are component level attributes.

When upgrading a plugin the component level attributes/settings will retain their value (this is good). The same is not true for application level attributes. They will be reset to the plugin's default values. For example if I configure the ClariFit Dialog plugin they're a few settings I can change: Background Color and Background Opacity. In the image below I've changed them from the default values to red and 50% respectively.

After I upgrade the plugin to a newer version the application level attributes are reset back to the plugin's default values shown below.

Be sure to take note of your plugin application level settings before updating a plugin. I find the easiest way to do this is to take a screen shot, similar to the images above, before I upgrade the plugin and compare the values with the new default values and make the appropriate changes.

How APEX Processes Various Conditions and Validations

I was recently teaching an Intro to APEX course and the students had some questions about what to put in the Expression 1 text area for different conditions and validations based on the Type selected. If you're new to APEX the various options can be confusing.

To help clear things up I've included a list of the various APEX conditions and validations along with code that demonstrates how APEX processes the value in Expression 1 based on the given Type. I didn't include the definition for each type as they are already well documented in the APEX documentation and popup help.

Note: the calls to DBMS_OUTPUT are there to show you if the validation/condition is true or false.

Exists

DECLARE
  l_rows pls_integer;
BEGIN
  SELECT count(1)
  INTO l_rows
  FROM (
    -- Start Expression 1
    SELECT 1 
    FROM emp 
    WHERE sal > :p1_sal
    -- End Expression 1
  );
  
  IF l_rows > 0 THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/

Not Exists

DECLARE
  l_rows pls_integer;
BEGIN
  SELECT count(1)
  INTO l_rows
  FROM (
    -- Start Expression 1
    SELECT 1 
    FROM emp 
    WHERE sal > :p1_sal
    -- End Expression 1
  );
  
  IF l_rows = 0 THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/

SQL Expression
Note: SQL Expression and PL/SQL Expression are very similar but some SQL expressions can't be used in PL/SQL. Example: Decode

DECLARE
  l_rows pls_integer;
BEGIN
  SELECT count(1)
  INTO l_rows
  FROM dual
  WHERE 
    -- Start Expression 1
    :p1_sal > 500
    -- End Expression 1
  ;
  
  IF l_rows = 1 THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/

PL/SQL Expression

DECLARE
BEGIN
  IF (
    -- Start Expression 1
    :p1_sal > 500
    -- End Expression 1
  ) THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
    
END;
/

PL/SQL Function Returning Boolean

DECLARE
  FUNCTION f_apex_condtion RETURN boolean
  AS
  BEGIN
    -- Start Expression 1
    DECLARE
      l_rows pls_integer;
    BEGIN
      SELECT count(*)
      INTO l_rows
      FROM emp
      WHERE JOB = 'PRESIDENT';
      
      IF l_rows > 1 THEN -- only want at most 1 president
        RETURN FALSE;
      ELSE
        RETURN TRUE;
      END IF;
    END;
    -- End Expression 1
  END;
  
BEGIN

  IF f_apex_condtion THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
    
END;
/

Conditions Only

Function Returning Error Text

DECLARE
  l_err_msg varchar2(4000);

  FUNCTION f_apex_condtion RETURN varchar2
  AS
  BEGIN
    -- Start Expression 1
    DECLARE
      l_rows pls_integer;
    BEGIN
      SELECT count(*)
      INTO l_rows
      FROM emp
      WHERE JOB = 'PRESIDENT';
      
      IF l_rows > 1 THEN 
        RETURN 'Only 1 president can exist for the company';
      ELSE
        RETURN NULL; -- no error
      END IF;
    END;
    -- End Expression 1
  END;
  
BEGIN
  l_err_msg := f_apex_condtion;
  
  IF l_err_msg IS NULL THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE - Error message: ' || l_err_msg);
  END IF;
    
END;
/

PL/SQL Error

DECLARE

  PROCEDURE sp_apex_condtion AS
  BEGIN
    -- Start Expression 1
    DECLARE
      l_rows pls_integer;
    BEGIN
      SELECT count(*)
      INTO l_rows
      FROM emp
      WHERE JOB = 'PRESIDENT';
      
      IF l_rows > 1 THEN 
        raise_application_error(-20001, 'Only 1 president can exist for the company');
      END IF;
    END;
    -- End Expression 1
  END;
  
BEGIN
  sp_apex_condtion;
  
  dbms_output.put_line('TRUE');
  
exception WHEN others THEN
  dbms_output.put_line('FALSE'); -- Error message is defined in the validation's error message
END;
/