Enhanced Developer Experience in EPAS 16 with Oracle Compatibility

Introduction

EDB (EnterpriseDB) has always been at the forefront of enhancing PostgreSQL for enterprise needs. With the release of EPAS 16, they have taken a significant leap in ensuring Oracle compatibility and improving the developer experience. In this post, we’ll delve into four key features – DBTIMEZONE, SESSIONTIMEZONE, TO_TIMESTAMP_TZ, and DBMS_UTILITY.EXPAND_SQL_TEXT – that stand out in EPAS 16, offering a more seamless transition for Oracle developers and a more intuitive environment for all users.

1. DBTIMEZONE Function

The DBTIMEZONE function in EPAS 16 is a welcomed addition for developers migrating from Oracle. It returns the time zone of the database, a crucial element for applications dealing with multiple time zones.

Example

SELECT DBTIMEZONE FROM DUAL;
 dbtimezone 
------------
 +00:00
(1 row)

This query returns the database’s time zone, simplifying time zone management in global applications.

2. SESSIONTIMEZONE Function:

Similarly, SESSIONTIMEZONE is another function that enhances the EPAS environment by returning the session’s current time zone setting. This is particularly useful in user-specific time zone configurations.

Example:

SELECT SESSIONTIMEZONE FROM dual;
 sessiontimezone 
-----------------
 UTC
(1 row)

This shows the current session’s time zone, aiding developers in debugging and designing user-centric applications.

3. TO_TIMESTAMP_TZ Function

EPAS 16 simplifies the TO_TIMESTAMP_TZ function by allowing it to accept just one argument. This makes converting string data to timestamp with time zone much more straightforward.

Example:

SELECT TO_TIMESTAMP_TZ('20-MAR-20 04:30:00.123456 PM +03:00') FROM DUAL;
        to_timestamp_tz        
-------------------------------
 2020-03-20 13:30:00.123456+00
(1 row)

This converts the provided string into a timestamp with the time zone.

4. DBMS_UTILITY.EXPAND_SQL_TEXT subprogram:

EPAS 16 introduces the DBMS_UTILITY.EXPAND_SQL_TEXT procedure, a powerful tool for developers. It provides a way to analyze and expand SQL texts for better understanding and optimization.

Example

Below is an example in DBMS_UTILITY.EXPAND_SQL_TEXT expanded normal SQL to show the exact table behind the view.

SELECT pg_get_viewdef('public.salesemp');
              pg_get_viewdef               
-------------------------------------------
  SELECT empno,                           +
     ename,                               +
     hiredate,                            +
     sal,                                 +
     comm                                 +
    FROM emp                              +
   WHERE ((job)::text = 'SALESMAN'::text);


edb=# DECLARE
   out_sql TEXT;
BEGIN
  DBMS_UTILITY.EXPAND_SQL_TEXT(input_sql_text => 'SELECT * FROM salesemp', output_sql_text => out_sql);
  DBMS_OUTPUT.PUT_LINE(out_sql);
END;

 SELECT empno,
    ename,
    hiredate,
    sal,
    comm
   FROM ( SELECT emp.empno,
            emp.ename,
            emp.hiredate,
            emp.sal,
            emp.comm
           FROM emp
          WHERE ((emp.job)::text = 'SALESMAN'::text)) salesemp

EDB-SPL Procedure successfully completed

Conclusion:

EPAS 16 is making strides in bridging the gap between Oracle and PostgreSQL, particularly in terms of compatibility and ease of development. These four features not only ease the transition for Oracle developers but also enhance the overall developer experience in PostgreSQL. With these advancements, EPAS continues to strengthen its position as a robust, enterprise-level database solution, catering to a diverse range of development needs.
As EPAS evolves, it’s exciting to think about what further enhancements and capabilities future releases will bring to the world of database management and development.