Decoding Decode Data Types

I ran into a strange issue in APEX a while ago which was due to a decode statement not returning the data type that I expected. Here’s a sample of what the query looked like:

create or replace view mdsouza_temp as
select
  decode(job, 'PRESIDENT', null, 0) col_decode,
  case when job = 'PRESIDENT' then null else 0 end col_case
from emp;

At first glance you would expect both columns to be the same. If you query the view they look like they return the same values but they don’t (this is what caught me). Upon further investigation the COL_DECODE column actually returns a VARCHAR2 and not a number:

desc mdsouza_temp;

Name       Null Type        
---------- ---- -----------
COL_DECODE      VARCHAR2(1)

COL_CASE        NUMBER     

It turns out that if you have a NULL as the first result (not the last, default, value) the returned value/data type will be a VARCHAR2 and not the data type of the other values as shown in the following example:

create or replace view mdsouza_temp as

select
  decode(job, 'MANAGER', 1, 'PRESIDENT', null, 0) col_decode,
from emp;

desc mdsouza_temp;

Name       Null Type  

---------- ---- ------
COL_DECODE      NUMBER

If you do have to have NULL as the first result in the set you need to explicitly convert the result to the appropriate data type:

create or replace view mdsouza_temp as

select
  to_number(decode(job, 'PRESIDENT', null, 0)) col_decode
from emp;

desc mdsouza_temp;

Name       Null Type  

---------- ---- ------
COL_DECODE      NUMBER   

This is not a bug, and is covered in the Oracle documentation on DECODE "Oracle automatically converts the return value to the same data type as the first result."

Session State Protection in Detail

Session State Protection (SSP) can be confusing for new APEX developers and also difficult for senior APEX developers trying to explain it. This article will cover SSP in detail.

What is SSP? 

SSP is used to prevent URL tampering. URL tampering is when a malicious user modifies URL parameters to access data that they may not have access to. For example if you have a Master Detail setup for the EMP table (i.e a report with edit links to a form) each link will look something like this: http://fcapex42.clarifit.com:8894/apex/f?p=211:11:14916146169058::::P11_EMPNO:7698. If you restricted the report to employees in your current department you can easily change the EMPNO id and change it to another employee in another department. Even though the intention of the application was to “restrict” users to only edit people within their own department a malicious or curious user could start editing all the employees.

SSP prevents URL tampering by applying a checksum to the end of the URL. The new URL looks something like: http://fcapex42.clarifit.com:8894/apex/f?p=211:11:14916146169058::::P11_EMPNO:7698&cs=3358AAB32787C2A2B294CE934D50FD0C3. If a user now tries to modify the EMPNO id they will get an error in APEX as the URL won't have the correct checksum.

Applying SSP

To add SSP at the page level (technically called Page Access Protection at the page level) edit the page and scroll down to the Security section. Select Arguments Must Have Checksum for the Page Access Protection option.

Once that is added you can modify the page items you want SSP to be applied to. Edit a page item and scroll down to the Security section. Select an option for Session State Protection (note: click on the help link to find the differences between the various options; I usually use Checksum Required - Session Level).

To modify a page and all its page items at the same time go to Shared Components > Session State Protection > Page Item and click on a page link.

Two Layered Approach: Page vs Page Items

The easiest way to think of SSP in APEX is that it’s a two layered approach. First you must define which pages (not page items) require a checksum applied to it when passing in URL parameters. The second part is to define which page items require a checksum when set from the URL. You can’t do the later with out the former (i.e. if you just define a set of page items to have SSP it won’t work) but you can have pages with SSP without any page items with SSP.

The two layered approach is where most people get confused. If you apply SSP at the page level, shouldn’t all items be protected? At first glance the answer is “yes”. But the actual answer is no. The following example highlights why both “layers” are required.

Suppose we only apply Page Access Protection for the EMP form page (let’s say P11). If I clicked on a link to edit an employee it would add the checksum. Initially it looks as if everything is ok. Now suppose we have another page, P20, that doesn’t have Page Access Protection enabled. We could actually set P11_EMPNO from P20. The URL would look like this: http://fcapex42.clarifit.com:8894/apex/f?p=211:20:14916146169058::::P11_EMPNO:7839 Most people forget that you can set page items from any page, i.e. you’re not restricted to only setting items for the current page that you’re accessing.

You could also set any of the page items via an AJAX call (since none of them have SSP applied to them). Either way, just applying page access protection isn't enough.

When Not to Apply SSP

If you haven’t implemented SSP in your application you should really look at doing so. Before you apply it to all items it’s important to note that any items that can be set via an AJAX call can not have SSP enabled for them. The most usual case of this is cascading LOVs (select a department, then a list of employees gets refreshed with all the employees that belong to the selected department).

The reason why you can’t have SSP item items that are set via AJAX is that AJAX uses JavaScript to build the URL. Since JavaScript code is downloaded and runs on the end user’s machine it is not deemed to be secure. So if you had your checksum code in a JavaScript file a malicious user to easily reverse engineer it and apply checksums for any item they wanted to.

Conclusion

SSP is a great feature to quickly help secure your application. It’s important to remember that SSP only prevents URL tampering. Nothing more, nothing less. It’s a common mistake by developers, and managers alike, that just applying SSP means that they’ve locked down and secured an application. It’s just one of many steps to help protect your application.

APEXposed Down Under

In a few weeks David Peake (Oracle APEX Product Manager and native Australian) and I will be heading to New Zealand and Australia for ODTUG APEXposed.

The conference will offer talks that will be relevant for all levels of APEX developers ranging from beginners (and those interested in learning about the product) all the way to advanced APEX development. Of course if there isn’t something covered you can always bring your questions and ask me or David during the Q&A panel. You can see the entire agenda here.

This will be a two day event in both Auckland (Nov 5-6) and then we’re going to do it all over again in Melbourne on (Nov 8-9). If you haven't already registered you can still take advantage of the early bird discount until Oct 24th. 
 
I look forward to seeing everyone there.

Martin

PL/SQL Exceptions Propagation during Variable Declaration

It's always good to know how any language handles and propagates exceptions, Oracle PL/SQL being no different. They're plenty of examples online about raising and handling exceptions on the web, but one thing you may not have realized is how PL/SQL propagates exceptions that occur in the variable declaration section of a procedure.

In the first example I created a procedure that has a variable, l_var, which can handle one character. As expected, when I assign more then one character an exception is raised and is propagated to the EXCEPTION block of the procedure.

SQL> CREATE OR REPLACE PROCEDURE sp_test(p_var in varchar2)
  2  AS
  3    l_var VARCHAR2(1);
  4  BEGIN
  5    dbms_output.put_line('***START***');
  6    l_var := 'abc';
  7  exception
  8    WHEN others THEN
  9      dbms_output.put_line('***Exception***');
 10      raise;
 11  END sp_test;
 12  /

Procedure created.

SQL> exec sp_test(p_var => 'abc');
***START***
***Exception***
BEGIN sp_test(p_var => 'abc'); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "ODTUG.SP_TEST", line 10
ORA-06512: at line 1

In the next example, instead of assigning the value in the main block of code I assigned the value in the declaration section. You'll notice that the procedure doesn't even get to the "START" line nor is the exception handled in the procedure's exception block. Instead the exception is propagated to the calling process right away.

SQL> CREATE OR REPLACE PROCEDURE sp_test(p_var in varchar2)
  2  AS
  3    l_var VARCHAR2(1) := p_var;
  4  BEGIN
  5    dbms_output.put_line('***START***');
  6  exception
  7    WHEN others THEN
  8      dbms_output.put_line('***Exception***');
  9      raise;
 10  END sp_test;
 11  /

Procedure created.

SQL> exec sp_test(p_var => 'abc');
BEGIN sp_test(p_var => 'abc'); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "ODTUG.SP_TEST", line 3
ORA-06512: at line 1

Before you go and change any of your existing code based on this article, I'm not saying that you should avoid defining variables in the declaration section of a procedure. Instead, just be aware of how the exception is propagated. This can be useful to know if your local variable is assigned to an input parameter. In that case you may want to assign the local variable in the main block of code rather then in the variable declaration section.

For documentation of how PL/SQL propagates exceptions raised in declarations go here. If you haven't already done so, I'd recommend reading the entire PL/SQL Error Handling documentation.

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.

ODTUG APEXposed 2012

ODTUG is hosting it's APEX focused APEXposed conference in Montreal this fall from September 11-12. The speaker list is loaded with experts from around the world who will present on a wide variety of APEX and SQL Developer topics. The list includes 3 Oracle Product Managers (APEX / SQL Developer), 6 ACE and ACE Directors, and some other excellent presenters. To put some perspective on this list of presenters, there's only 14 ACE/ACE Directors in Canada and 375 world wide. Having 6 ACE/ACEDs coming to Montreal is a great opportunity to learn from them.

On a personal note, I'll be giving two talks on APEX + HTML5, and APEX Plugins. I gave the HTML 5 presentation this summer at Kscope 12 but I'll be adding some new parts to it. The plugins presentation will go over how to create a plugin from scratch to help you and your team build your own APEX plugins.

For more information and to register go to http://odtugapextraining.com. If you register before August 15th you can take advantage of the early bird rate for only $399! This is a steal for the amount of information that you'll learn and the opportunity to ask the experts any of your APEX questions. To make things even more compelling if you use the code CLARIFIT you will save an additional $50 off your registration cost.

For those that are coming from out of town (Toronto, Ottawa, Quebec City, Southern US, etc) ODTUG has secured a preferred rate at Le St.Martin. For more information go to http://odtugapextraining.com/Location.html and scroll to the bottom.

I really hope that the Oracle community in Canada takes advantage of this opportunity. I'm not sure how often we'll get such an amazing list of presenters and presentations in our own back yard. See you there!

APEX 4 + HTML5 = Awesome II - Slides

I have uploaded the slides from the my talk on HTML5 that I gave at ODTUG Kscope 12 last week. The slides include comments and links to various demos. I haven't had the opportunity to clean up the drag & drop file upload demo and I will post that separately when I have some free time.

You can download the slides or go to the Presentations page to see full abstract and download link. If you have any questions, comments, or feedback please feel free to send me an email (email address in slides).

Building a Better Team - Slides

A lot of people have emailed me about the slides from my talk on Building a Better Team that I gave at ODTUG Kscope 12 last week. I updated the slides to include a lot of comments about the main points from the talk.

You can download the slides or go to the Presentations page to see full abstract and download link. If you have any questions, comments, or want to let me know how implementing some of the ideas go please feel free to send me an email (email address in slides).

ODTUG Kscope 12: Things To Look Out For

With Kscope 12 around the corner (literally) I thought I'd post somethings that I'm really looking forward to at the conference.

Cary Millsap's Keynote at the General Session: I've heard a lot of great things about Cary's talk. Everyone that has seen it has only had rave reviews about it so I'm really excited to see it myself. Besides Cary's talk there will be a lot of other funny and exciting things from ODTUG at the general session.

Red Gate's booth: Red Gate will be doing something I've never seen before at a conference. They'll be having a Rapid Development Lab to develop a new Database Version Control system. I strongly encourage you to check it out and bring some ideas on how to manage changes coming in from multiple developers and how a tool could help with it.

Open Mic Night: Immediately following the general session on Monday there will be the annual APEX Open mic night where anyone can demo anything with APEX. Only rule is no powerpoints and a fixed time limit. I'm always amazed at all the neat things that people are doing in their organization.

Lots of APEX presentations: Obviously I'm biased on this topic, but I think there will be a lot of great presentations on the newly announced 4.2 EA 1 and mobile applications. I'm giving two talks this year that I'm really excited about: 

• APEX 4 + HTML 5 = Awesome II: Mon  11:30 @ Cibolo Canyon 5 - This talk is a sequel to last year's award winning talk on HTML 5. I have some very cool new features to showcase.
• Building a Better Team: Mon  11:30 @ Cibolo Canyon 5 - This talk will cover how to help improve your development team. Though it's not APEX specific it'll cover some very key points.
• In both of my presentations I'll be giving copies of my APEX books away!

I look forward to seeing everyone over the next week and seeing a lot of APEX presentations.

SELECT INTO Techinques

One thing that bothers a lot of PL/SQL developers is when they have to select some data from a table into local variables. The problem is that the data may or may not exist (i.e. return 0..1 rows). They're several ways to write this code, some taking longer than others which is the pain point for developers.

I've listed out various ways to do this below and hopefully it'll help you find some easier ways to solve this issue.

The first example is the classic case of handling the 0..1 rows issue in PL/SQL. They're a few issues (not problems) that I have with this. The first is that it adds some additional lines of code (highlighted) which can make the code harder to follow. The other issue is that instead of using "WHEN no_data_found" some developers tend to use "WHEN OTHERS" which is guaranteed to give you some false positives in the long run.

DECLARE
  l_sal emp.sal%TYPE;
  p_empno emp.empno%TYPE := 123; -- Invalid empno number
BEGIN

  BEGIN
    SELECT sal
    INTO l_sal
    FROM emp
    WHERE empno = p_empno;
  EXCEPTION
    WHEN no_data_found THEN
      l_sal := 0;
  END;
  
END;
/

Another way to get the value from the table is to use the aggregate function MAX. Since you're expecting 0..1 rows this will always return 1 row and eliminates the need to have the "BEGIN ..." block of code which saves 5 lines of code. You can handle what to do when no value is found by using the NVL function.

DECLARE
  l_sal emp.sal%TYPE;
  p_empno emp.empno%TYPE := 123; -- Invalid empno number
BEGIN

  SELECT NVL(MAX(sal), 0)
  INTO l_sal
  FROM emp
  WHERE empno = p_empno;
  
END;
/

The only issue with the above example is when you can have 0..n rows but should only get 0..1. Ideally you should have table constraints in order to guarantee 0..1 rows but it's not always the case in some system. The following example shows how you can use the same technique as above but it will trigger an exception if n rows are found (which is what you want to do):

DECLARE
  l_sal emp.sal%TYPE;
  p_empno emp.empno%TYPE := 123; -- Invalid empno number
BEGIN

  SELECT NVL(MAX(sal), 0)
  INTO l_sal
  FROM emp
  WHERE empno = p_empno
  HAVING count(1) <= 1;
  
END;
/

NTH_VALUE Windowing Clause

In my previous post, which highlighted some analytic functions, I mentioned that the windowing clause must be explicitly defined when using the NTH_VALUE function.

To recap, here's the example I used for NTH_VALUE which lists the 2nd highest salary for each department:

SELECT d.dname, e.ename, e.sal, 
   nth_value(e.sal, 2) OVER (
    PARTITION BY e.deptno ORDER BY e.sal DESC
    -- windowing_clause
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) sec_high_sal_dept
FROM emp e, dept d
WHERE e.deptno = d.deptno;

-- Result

DNAME          ENAME             SAL SEC_HIGH_SAL_DEPT
-------------- ---------- ---------- -----------------
ACCOUNTING     KING             5000              2450
ACCOUNTING     CLARK            2450              2450
ACCOUNTING     MILLER           1300              2450
RESEARCH       FORD             3000              3000
RESEARCH       SCOTT            3000              3000
RESEARCH       JONES            2975              3000
RESEARCH       ADAMS            1100              3000
RESEARCH       SMITH             800              3000
SALES          BLAKE            2850              1600
SALES          ALLEN            1600              1600
SALES          TURNER           1500              1600
SALES          WARD             1250              1600
SALES          MARTIN           1250              1600
SALES          JAMES             950              1600

What happens if we don't include the windowing clause? Here's the same query, but just focusing on the Accounting department, without the windowing clause:

SELECT d.dname, e.ename, e.sal, 
   nth_value(e.sal, 2) OVER (
    PARTITION BY e.deptno ORDER BY e.sal DESC) sec_high_sal_dept
FROM emp e, dept d
WHERE e.deptno = d.deptno
  AND d.dname = 'ACCOUNTING';

-- Result

DNAME          ENAME             SAL SEC_HIGH_SAL_DEPT
-------------- ---------- ---------- -----------------
ACCOUNTING     KING             5000
ACCOUNTING     CLARK            2450              2450
ACCOUNTING     MILLER           1300              2450

You'll notice that the first row (KING) has a NULL returned for the SEC_HIGH_SAL_DEPT column. That's because when it looks at the first row (KING) it still hasn't had a chance to evaluate at least 2 values. Obviously writing some test queries will identify this "issue" which may or may not be what you're looking for. If it isn't then just add the windowing clause (above).

Some Interesting Oracle Analytic Functions

I had to write some reports for a personal application of mine last night and needed to expand beyond my usual set of analytic functions that I normally use. I thought it would be a good idea to do a quick blog post on these analytic functions.

If you've never heard of or used Oracle analytic functions please read this article first. It's by far the best article I've read at explaining what analytic functions are and how to use them. The Oracle analytic function documentation can be found here.

RATIO_TO_REPORT

RATIO_TO_REPORT compares the current value against the sum of the other set of values. The following example shows the percentage of each employees salary when compared to the sum of their department's salary. You'll notice that you can calculate the same value using a different method (sal_dept_ratio2) which may be why I haven't seen RATIO_TO_REPORT used that often.

SELECT d.dname, e.ename, e.sal, 
  round(ratio_to_report(sal) OVER (PARTITION BY e.deptno),2) sal_dept_ratio, 
  round(sal / sum(e.sal) OVER (PARTITION BY e.deptno),2) sal_dept_ratio2
FROM emp e, dept d
WHERE e.deptno = d.deptno;

-- Result

DNAME          ENAME             SAL SAL_DEPT_RATIO SAL_DEPT_RATIO2
-------------- ---------- ---------- -------------- ---------------
ACCOUNTING     CLARK            2450            .28             .28
ACCOUNTING     MILLER           1300            .15             .15
ACCOUNTING     KING             5000            .57             .57
RESEARCH       FORD             3000            .28             .28
RESEARCH       SCOTT            3000            .28             .28
RESEARCH       JONES            2975            .27             .27
RESEARCH       SMITH             800            .07             .07
RESEARCH       ADAMS            1100             .1              .1
SALES          WARD             1250            .13             .13
SALES          MARTIN           1250            .13             .13
SALES          TURNER           1500            .16             .16
SALES          JAMES             950             .1              .1
SALES          ALLEN            1600            .17             .17
SALES          BLAKE            2850             .3              .3

NTH_VALUE

NTH_VALUE returns the nth row in the window clause. It's extremely important that you explicitly define the window or your values may not make sense. I'll write another post explaining this later. Update: NTH_VALUE Windowing Clause

The following example shows the 2nd highest salary in each department.

SELECT d.dname, e.ename, e.sal, 
   nth_value(e.sal, 2) OVER (
    PARTITION BY e.deptno ORDER BY e.sal DESC 
    -- Don't forget to define the window
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) sec_high_sal_dept
FROM emp e, dept d
WHERE e.deptno = d.deptno;

-- Result

DNAME          ENAME             SAL SEC_HIGH_SAL_DEPT
-------------- ---------- ---------- -----------------
ACCOUNTING     KING             5000              2450
ACCOUNTING     CLARK            2450              2450
ACCOUNTING     MILLER           1300              2450
RESEARCH       FORD             3000              3000
RESEARCH       SCOTT            3000              3000
RESEARCH       JONES            2975              3000
RESEARCH       ADAMS            1100              3000
RESEARCH       SMITH             800              3000
SALES          BLAKE            2850              1600
SALES          ALLEN            1600              1600
SALES          TURNER           1500              1600
SALES          WARD             1250              1600
SALES          MARTIN           1250              1600
SALES          JAMES             950              1600

LISTAGG

LISTAGG was a highly requested feature that was implemented in 11gR2. Overall LISTAGG allows you to group values in multiple rows and put them in a comma delimited column on one row.

The following example (which is not an analytic function) shows all the employees for each department:

SELECT d.dname,
  listagg(e.ename, ',') WITHIN GROUP (ORDER BY e.ename) dept_emp_list
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname;

-- Result

DNAME          DEPT_EMP_LIST
-------------- ----------------------------------------

ACCOUNTING     CLARK,KING,MILLER
RESEARCH       ADAMS,FORD,JONES,SCOTT,SMITH
SALES          ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

Using the LISTAGG as an analytic function you can see each employee's colleagues in their department, including themselves.

SELECT d.dname, e.ename,
  listagg(e.ename, ',') WITHIN GROUP (ORDER BY e.ename) 
    OVER (PARTITION BY e.deptno) dept_colleagues
FROM emp e, dept d
WHERE e.deptno = d.deptno;

-- Result

DNAME          ENAME      DEPT_COLLEAGUES
-------------- ---------- --------------------------------------

ACCOUNTING     CLARK      CLARK,KING,MILLER
ACCOUNTING     KING       CLARK,KING,MILLER
ACCOUNTING     MILLER     CLARK,KING,MILLER
RESEARCH       ADAMS      ADAMS,FORD,JONES,SCOTT,SMITH
RESEARCH       FORD       ADAMS,FORD,JONES,SCOTT,SMITH
RESEARCH       JONES      ADAMS,FORD,JONES,SCOTT,SMITH
RESEARCH       SCOTT      ADAMS,FORD,JONES,SCOTT,SMITH
RESEARCH       SMITH      ADAMS,FORD,JONES,SCOTT,SMITH
SALES          ALLEN      ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
SALES          BLAKE      ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
SALES          JAMES      ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
SALES          MARTIN     ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
SALES          TURNER     ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
SALES          WARD       ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

NTILE

NTILE allows you to divide your values into buckets and see which bucket the current row belongs to. For educational institutions this is a very good function to let students know that they're in the top x% of the class.

Update (12-Apr-2014): NTILE vs WIDTH_BOX describes the different ways to bucket data in Oracle. It also explains why SAL 1250 exists in both buckets 2 and 3.

The following example shows the 3 tiers (or buckets) of salaries across the entire company. It allows you to easily see who's in the top 33% of salaries.

SELECT d.dname, e.ename, e.sal,
  ntile (3) over (order by sal desc) three_tier_sal
FROM emp e, dept d
WHERE e.deptno = d.deptno;

-- Result

DNAME          ENAME             SAL THREE_TIER_SAL
-------------- ---------- ---------- --------------
ACCOUNTING     KING             5000              1
RESEARCH       SCOTT            3000              1
RESEARCH       FORD             3000              1
RESEARCH       JONES            2975              1
SALES          BLAKE            2850              1
ACCOUNTING     CLARK            2450              2
SALES          ALLEN            1600              2
SALES          TURNER           1500              2
ACCOUNTING     MILLER           1300              2
SALES          WARD             1250              2
SALES          MARTIN           1250              3
RESEARCH       ADAMS            1100              3
SALES          JAMES             950              3
RESEARCH       SMITH             800              3

ClariFit From To Date Picker 1.0.1 Update

I've updated the ClariFit From To Date Picker APEX Plugin. There was a small bug in the initial version that caused rendering issues in IE.

The initial post about the plugin can be found here and the demo here.

Thanks to Marc B and Joni V for finding the issue.

Command Line Backups for APEX Applications

Update 21-Apr-2015: This script will no longer be supported. Instead please use the new script (for Mac/Linux) available on Github. New article: http://www.talkapex.com/2015/04/apex-backup-script.html

One thing developers like to do is keep local backup copies of their development work. Sometimes they'll copy files and suffix them with .bak or they'll store them in a version control repository. With APEX you don't copy a file to back it up. Either you have to copy the application (within the workspace) or export the application and save it locally. For one-off backups this can be ok, but as you develop with more and more applications in larger environments it can be tedious work and will slow down your development time.

Thankfully APEX has a tool, that comes as part of the install zip file, which allows for command line exports. It's one of the lesser known tools that is extremely useful.

Below is a simplified copy of a batch file which will automatically backup all the APEX applications on a given database using the APEX backup tool. I usually recommend that organizations run such a script on their development environments to store hourly backups of their development environments. The example is a very simple example which should be modified to tag the backup with the date/time or zip and store the backups in version control. I've also appended an extra script which lists all the APEX applications, workspaces, and IDs and stores this information as part of the backup in a .html file.

To use the script, create the files listed below in the same directory and make the appropriate modifications in apex_backup.bat

apex_backup.bat
Backups all the applications in the database

REM ***** START MODIFICATIONS ******

REM ***** Parameters ******
REM Oracle information
REM To find SID name: select * from v$instance;
set oracleSID=XE
set oracleSystemPass=oracle
set oraclePort=1526
set oracleHost=localhost
REM Root directory where oracle\apex\*class files are stored
REM These is a sub directory from the extracted APEX install zip file
set apexExportJavaDir=C:\temp\apex_4.1.1_en\apex\utilities
REM File that contains names of applications and workspaces
set apexHtmlFileName=apex_info.html
REM Directory where backups will be stored
set backupLocation=C:\temp\apexbackup

REM ****** PATHS *********
REM Note: You may not need to explicitly define these as they may already be set in OS.
set ORACLE_HOME=C:\oracle\product\11.2.0\client_1
set CLASSPATH=%CLASSPATH%;.\;%ORACLE_HOME%\jdbc\lib\ojdbc5.jar;%apexExportJavaDir%
set PATH=%PATH%;.\;C:\Program Files (x86)\Java\jre6\bin

REM ***** END MODIFICATIONS ******

REM ****** Other *********
set startRunLocation=%CD%

REM ****** Directory Setup ******
REM Create temp bacpkup location
mkdir %backupLocation%

REM ****** APEX BACKUP *******
REM Go to backup location to run backups in
cd %backupLocation%

REM Export all applications
java oracle.apex.APEXExport -db %oracleHost%:%oraclePort%:%oracleSID% -user system -password %oracleSystemPass% -instance

REM Export all Workspaces
java oracle.apex.APEXExport -db %oracleHost%:%oraclePort%:%oracleSID% -user system -password %oracleSystemPass% -expWorkspace

REM Generate listing of Workspaces and Applications
sqlplus system/%oracleSystemPass%@%oracleHost%:%oraclePort%/%oracleSID% @%startRunLocation%\apex_backup_info.sql %apexHtmlFileName%

REM Back to start location
cd %startRunLocation%

apex_backup_info.sql
Stores all the application names, workspaces, and IDs

-- Parameters
-- 1 = File Name

-- http://forums.devshed.com/oracle-development-96/sql-plus-column-width-format-185085.html
-- http://download.oracle.com/docs/cd/B10501_01/server.920/a90842/ch13.htm#1012748
SET NUMFORMAT 9999999999999999999999999999999999999
SET MARKUP HTML ON SPOOL ON HEAD ""

SET ECHO OFF
SPOOL &1

-- Current Date
select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') current_date
from dual;

-- Applications
SELECT application_id, workspace, application_name, owner
FROM apex_applications
WHERE workspace != 'INTERNAL'
ORDER BY application_id;

-- Workspaces
SELECT workspace_id, workspace
FROM apex_workspaces
WHERE workspace != 'INTERNAL'
ORDER BY workspace_id;

SPOOL OFF
SET MARKUP HTML OFF
SET ECHO ON  

EXIT;

Presenting at Calgary Oracle User Group (COUG) April 19th

I'm really excited about my presentation at the Calgary Oracle User Group (COUG) on April 19th. I gave a presentation there three years ago and it'll be nice to give a talk "on home turf" again.

Here's the information about the presentation:

Title: Common Questions DBA have about APEX

Abstract: DBAs have many responsibilities managing the databases in their organizations. The introduction of Oracle Application Express (APEX) has added some more items to their already demanding list of tasks. This presentation will answer some of the common questions DBAs have about APEX.

APEX items to be discussed include: development best practices, release management, automatic backups, and web server configurations. This presentation is also relevant to developers and they are encouraged to attend.

Date/Time/Location:
April 19th 8:30am (registration starts at 8:00 am)

Suncor Energy Centre (West Tower)
150 6th Avenue SW
17th floor – Room A/B


I will also be giving away a few things at the end of my talk. Apress has been very generous and donated a copy of each of my books and ODTUG has donated a lot of really cool swag to help promote the best Oracle conference in the world, Kscope 12.

Hope to see you there!

Martin

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;
/