NTILE vs WIDTH_BUCKET

Rick Cale recently asked a question on an article I wrote two years ago about Some Interesting Oracle Analytic Functions. His question (see comments on Apr 11, 2014) were about the results from the NTILE function and how the same value could be in two different buckets. It was an excellent question and one that got me digging a bit more into the functionality of NTILE.

They're two ways to handle "bucketing data" in Oracle. In the documentation Oracle describes the two types as either having equiwidth or or equiheight histograms.

Equiwidth (NTILE): Each bucket will have the same number of items in it with some buckets having at most 1 more item than other buckets. An easy way to think of this concept is to order all the items, then divide the data evenly into groups based on the number of buckets. For example, supposed you have 9 values (AAAABCDEF) and wanted to put them into 3 buckets. the buckets would be B1 = AAA, B2 = ABC, B3 = DEF. You'll notice that the value "A" is in both buckets B1 and B2.

When an uneven amount of objects need to go into the buckets, NTILE will fill the first bucket first, second bucket second, etc. For example, suppose you had 10 values (AAAABCDEFG) and wanted to fill them into 3 buckets. (Note this is similar to the previous example with an additional "G"). The buckets would be B1 = AAAA, B2 = BCD, B3 = EFG.

Equiheight (WIDTH_BUCKET): This will take the min and max range, divided by the number of buckets and place each value in it. For example, the salaries in the EMP table range from 801 ~ 5000. If you set the min/max range from 0~5000 3 buckets will be created. All values from 0~1,666 will go into B1, values from 1,6667~3333 into B2, and values 3334 to 5000 into B3. (Note: for simplicity I took out decimals in this split). 

An easy way to think of these two bucketing methods is that NTILE divides values based on the number of items. WIDTH_BUCKET divides values based on their values.

Here's an example that highlights the difference between the two functions. 

with data as (
  -- using this as data input
  select 3 as num_buckets
  from dual
)
select 
  d.dname, 
  e.ename, 
  e.sal,
  ntile (data.num_buckets) over (order by sal asc) ntile,
  width_bucket(sal, 0, max(sal+1) over (), data.num_buckets) width_bucket
from emp e, dept d, data
where e.deptno = d.deptno
order by sal;


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

It's important to note that WIDTH_BUCKET is not an analytic function but NTILE is. For more information read the documentation for each function. For WIDTH_BUCKET, the documentation covers what happens with values outside the min/max range (they go into bucket 0 and num_buckets+1).

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.

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;

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

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).

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.

How To Reference Package Variables Outside of PL/SQL

When developing with PL/SQL you may store public variables in the package specification. This has many uses, none of which I will get into for this post. The only catch in Oracle is that you can not easily reference these values in SQL statements outside of PL/SQL. The following example demonstrates this:

- Create Package Spec with Variable

CREATE OR REPLACE PACKAGE pkg_var
AS
  c_my_var   CONSTANT VARCHAR2 (5) := 'hello';
END pkg_var;

- Reference the variable in a SQL statement in SQL*Plus

SQL> SELECT pkg_var.c_my_var x
  2    FROM DUAL;
SELECT pkg_var.c_my_var x
       *
ERROR at line 1:
ORA-06553: PLS-221: 'C_MY_VAR' is not a procedure or is undefined

This results in an Oracle error.


- Try the same code, but in an block of PL/SQL

SQL> DECLARE
  2    v_x   VARCHAR2 (5);
  3  BEGIN
  4    SELECT pkg_var.c_my_var x
  5      INTO v_x
  6      FROM DUAL;
  7
  8    DBMS_OUTPUT.put_line (v_x);
  9  END;
 10  /
hello

PL/SQL procedure successfully completed.

As you can see this worked.


So how can we refernce package variables in a non-PL/SQL setting? I created the following function to do so. It will handle values that are of type VARCHAR2. I've also removed any spaces from the parameter (pkg_name.var_name) to ensure that no SQL injection will occur.

-- **
-- * Returns Package Variable value
-- * Note: for demo purposes I broke this function into various steps
-- * 
-- * @param p_pkg_var_name fully qualified variable reference. Ex: pkg_x.var_y
-- * @return Varchar2 value
-- * @author Martin Giffy D'Souza: http://apex-smb.blogspot.com
-- **
CREATE OR REPLACE FUNCTION f_get_pkg_val_vc2 (p_pkg_var_name in varchar2)
  RETURN VARCHAR2
AS
  v_string          VARCHAR2 (4000);
  -- Full Variable Name (i.e. pkg.var)
  v_var_full_name   VARCHAR2 (61);                             -- Max of 61 chars since 30 + . + 30
BEGIN
  v_var_full_name := p_pkg_var_name;
  -- Remove any spaces to avoid SQL injections
  v_var_full_name := REGEXP_REPLACE (v_var_full_name, '[[:space:]]', '');

  EXECUTE IMMEDIATE 'begin :v_string := ' || v_var_full_name || '; end;'
              USING OUT v_string;

  RETURN v_string;
END f_get_pkg_val_vc2;

Now when you run in SQL*Plus you get the following:

SQL> SELECT f_get_pkg_val_vc2 ('pkg_var.c_my_var') x
  2    FROM DUAL;

X
-----------------------------------------------------

hello

Oracle and APEX Jobs

A lot of people were affected by the recession and may have lost their jobs. I know they're several companies looking for talented workers. To help out I've decided to start a small Jobs section on my blog (see right hand side). If you're interested in submitting a job, please leave a comment on this post with the following information:

- Job Title
- City the job is in
- Link to position

To keep with the theme of this blog please only submit Oracle (SQL,PL/SQL) and APEX related jobs.

On a side note I may create a small application where people can post jobs specific to APEX.

APEX Rules & Guidelines

After several late nights of nerding I've finally submitted/completed my APEX application for the APEX Developer Competition 2009.

You can demo and download the application here: http://apex.oracle.com/pls/otn/f?p=46865
Note: Demo is no longer up and will not be supported.

This purpose of this application is to allow development teams to create an online Rules & Guidelines development document. The template for this application is based on Patrick Cimolini's (Cayman Islands Government) presentation at ODTUG.

The concept of the R&G template is:

Rule: Follow 100% of the time
Guideline: Follow 90% of the time

Mandatory Headings:

Rule/Guideline: Active voice sentence.
Why:Important for Team Buy-In

Optional Headings:

Result: Illustration of desired outcome
How: How the result is achieved.
Notes: State the non-obvious
See Also: Point to related Rules and Guidelines

You can use this template for both APEX development and your PL/SQL standards. I've included the installation files in the demo application so feel free to install at your organization. If you are using it please post any comments/suggestions on this post and I'll try to write some updates.

Oracle: How to update all sequences

If you ever do data refreshes from production to development or test environments you may run into an issue where your sequences are not up to date. It seems that Oracle exports the sequences first, then the data. If your sequence numbers change during the entire export process you may get errors when using them in your refreshed schema.

To fix this problem you can try to find where your sequences are used and get the MAX(value) to find the next value. Alternatively you can just add a large random number, say 1,000,000, to all your sequences. For most users this will fix the problem and is very easy to do. Here's how:

-- Update all sequences
DECLARE
  v_increase_by                     NUMBER;
  v_bkp_increment_by            NUMBER;
  v_str                         VARCHAR2 (1000);
  v_count                       NUMBER;
BEGIN
  v_increase_by                  := 1000000;

  FOR rec IN (SELECT *
              FROM   user_sequences) LOOP
    -- Backup current incrementation number
    v_bkp_increment_by         := rec.increment_by;
    -- Alter the sequence to increase by a defined amount
    v_str                      := 'alter sequence ' || rec.sequence_name || ' increment by ' || v_increase_by;

    EXECUTE IMMEDIATE v_str;

    -- Increase by that amount
    v_str                      := 'select ' || rec.sequence_name || '.nextval from dual';

    EXECUTE IMMEDIATE v_str
    INTO              v_count;

    -- Reset the increment factor
    v_str                      := 'alter sequence ' || rec.sequence_name || ' increment by ' || v_bkp_increment_by;

    EXECUTE IMMEDIATE v_str;
  END LOOP;
END;
/

Oracle: Advanced Error Messages

This is not an APEX specific post, however it can be useful for error handling.

A colleague showed me a great way to get more useful debug information. Normally I used SQLERRM and SQLCODE in an exception to display or store error messages. Using DBMS_UTILITY you can get more detailed Oracle error messages. Here's an example:

-- I put this in a package for demo purposes
CREATE OR REPLACE PACKAGE pkg_err_test
AS
  PROCEDURE sp_err_test (
    p_empno IN emp.empno%TYPE
  );
END pkg_err_test;

CREATE OR REPLACE PACKAGE BODY pkg_err_test
AS
  PROCEDURE sp_err_test (
    p_empno IN emp.empno%TYPE
  )
  AS
    v_ename emp.ename%TYPE;
  BEGIN
    SELECT ename
      INTO v_ename
      FROM emp
     WHERE empno = p_empno;

    DBMS_OUTPUT.put_line ('Employee name is: ' || v_ename);
  EXCEPTION
    WHEN OTHERS THEN
      -- Basic Error Message
      DBMS_OUTPUT.put_line ('Old Error Message: ' || SUBSTR (SQLERRM, 1, 255));
      DBMS_OUTPUT.put_line ('Old Err Code: ' || SQLCODE);
      -- Advanced Error Messages
      DBMS_OUTPUT.put_line ('-- New Error Messages --');
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);   -- Error Message
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);   -- Where it occurred
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack);   -- Call Stack
  END sp_err_test;
END pkg_err_test;

-- Run the error test with an invalid employee number so an exception will be raised
EXEC pkg_err_test.sp_err_test(p_empno => 123);

DBMS Output:

Old Error Message: ORA-01403: no data found
Old Err Code: 100
-- New Error Messages --
ORA-01403: no data found

ORA-06512: at "GIFFY.PKG_ERR_TEST", line 9

----- PL/SQL Call Stack -----
object line object
handle number name
362D7814 24 package body GIFFY.PKG_ERR_TEST
362D70E0 1 anonymous block

The error message is displayed as well as where the error occurred and the call stack. In large systems this can be very helpful. You should be aware that when called from a package, it does not list the procedure or function (as seen in this example) where the error occurred so you may need to hard code the function or procedure name in your error message.

How to Quickly Append VARCHAR2 to CLOB

This is not an APEX specific issue, however it could be useful for some of your PL/SQL code

I ran into an issue today where I had to append VARCHAR2s to a CLOB many times in a loop. I first tried appending a VARCHAR2 to a CLOB: CLOB := CLOB || VARCHAR2. I noticed that this was taking a long time to run. In order to speed up the process I tried the following techniques:
- Create a "temp" CLOB (TMP_CLOB := VARCHAR2) and then appended it the clob CLOB := CLOB || CLOB
- Use the CLOB := CLOB || TO_CLOB(VARCHAR2)
- Use DBMS_LOB.append (CLOB, VARCHAR2)

All three options resulted in significant speed increases, however using the "temp" CLOB method resulted in the quickest code. Here is the test that I ran along with the results:

DECLARE
  v_start TIMESTAMP;
  v_end TIMESTAMP;
  v_clob CLOB;
  v_tmp_clob CLOB;
  v_iterations PLS_INTEGER := 100000; -- Used 1,000, 10,000, and 100,000 for testing
BEGIN
  v_start := SYSTIMESTAMP;
  v_clob := NULL;

  FOR i IN 1 .. v_iterations LOOP
    v_clob := v_clob || TO_CHAR (SYSTIMESTAMP) || ', ';
  END LOOP;

  v_end := SYSTIMESTAMP;
  DBMS_OUTPUT.put_line ('CLOB := CLOB || VARCHAR2 method: ' || TO_CHAR (v_end - v_start));
  v_start := SYSTIMESTAMP;
  v_clob := NULL;

  FOR i IN 1 .. v_iterations LOOP
    v_clob := v_clob || TO_CLOB (TO_CHAR (SYSTIMESTAMP) || ', ');
  END LOOP;

  v_end := SYSTIMESTAMP;
  DBMS_OUTPUT.put_line ('CLOB := CLOB || TO_CLOB(VARCHAR2) method: ' || TO_CHAR (v_end - v_start));
  v_start := SYSTIMESTAMP;
  v_clob := NULL;

  FOR i IN 1 .. v_iterations LOOP
    v_tmp_clob := TO_CHAR (SYSTIMESTAMP) || ', ';
    v_clob := v_clob || v_tmp_clob;
  END LOOP;

  v_end := SYSTIMESTAMP;
  DBMS_OUTPUT.put_line ('CLOB := CLOB || TMP_CLOB method: ' || TO_CHAR (v_end - v_start));
  v_start := SYSTIMESTAMP;
  v_clob := NULL;
  v_clob := 'h'; -- need to initialize it;

  FOR i IN 1 .. v_iterations LOOP
    DBMS_LOB.append (v_clob, TO_CHAR (SYSTIMESTAMP) || ', ');
  END LOOP;

  v_end := SYSTIMESTAMP;
  DBMS_OUTPUT.put_line ('DBMS_LOB.append method: ' || TO_CHAR (v_end - v_start));
END;

The results were as follows:

1,000 Iterations
CLOB := CLOB || VARCHAR2 method: +000000000 00:00:00.578000000
CLOB := CLOB || TO_CLOB(VARCHAR2) method: +000000000 00:00:00.063000000
CLOB := CLOB || TMP_CLOB method: +000000000 00:00:00.047000000
DBMS_LOB.append method: +000000000 00:00:00.172000000

10,000 Iterations
CLOB := CLOB || VARCHAR2 method: +000000000 00:00:10.656000000
CLOB := CLOB || TO_CLOB(VARCHAR2) method: +000000000 00:00:00.688000000
CLOB := CLOB || TMP_CLOB method: +000000000 00:00:00.672000000
DBMS_LOB.append method: +000000000 00:00:00.687000000

100,000 Iterations
CLOB := CLOB || VARCHAR2 method: +000000000 00:42:17.453000000
CLOB := CLOB || TO_CLOB(VARCHAR2) method: +000000000 00:00:17.953000000
CLOB := CLOB || TMP_CLOB method: +000000000 00:00:08.140000000
DBMS_LOB.append method: +000000000 00:00:11.110000000