Orafce

= Orafce - Oracle's compatibility functions and packages

Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS.

There is an associated Google group - https://groups.google.com/forum/?hl=en#!forum/orafce-general

The Orafce is supported in https://aws.amazon.com/about-aws/whats-new/2018/03/amazon-aurora-with-postgresql-compatibility-supports-minor-version-9-6-6/?nc1=h_ls[AWS Aurora with PostgreSQL Compatibility] and also in https://azure.microsoft.com/en-gb/updates/the-orafce-extension-on-azure-database-for-postgresql-is-now-available/[Azure Database for PostgreSQL].

== Oracle functions and Oracle packages

This module contains some useful functions that can help with porting Oracle application to PostgreSQL or that can be generally useful.

Built-in Oracle date functions have been tested against Oracle 10 for conformance. Date ranges from 1960 to 2070 work correctly. Dates before 1582-10-05 with the 'J' format and before 1100-03-01 with other formats cannot be verified due to a bug in Oracle.

All functions are fully compatibles with Oracle and respect all known format strings. Detailed descriptions can be found on the internet. Use keywords like : oracle round trunc date iyyy.

== List of format strings for trunc, round functions

---

Y,YY,YYY,YYYY,SYYY,SYEAR year I,IY,IYY,IYYY iso year Q, quarter WW week, day as first day of year IW week, beginning Monday W week, day as first day of month DAY,DY,D first day of week, sunday MONTH,MON,MM,RM month CC,SCC century DDD,DD,J day HH,HH12,HH24 hour MI minute

Functions round up. That is, a date of July 1st will be rounded to the next year. The 16th of July will be rounded to August.

== Date Functions

• add_months(date, integer) date - Returns date plus n months

---

add_months(date '2005-05-31',1) -> 2005-06-30

---

• last_date(date) date - Returns last day of the month based on a date value

---

last_day(date '2005-05-24') -> 2005-05-31

---

• next_day(date, text) date - Returns the first weekday that is greater than a date value

---

next_day(date '2005-05-24', 'monday') -> 2005-05-30

---

• next_day(date, integer) date - Same as above. The second argument should be 1..7 and interpreted as Sunday..Satday.

---

next_day(date '2005-05-24', 1) -> 2005-05-30

---

• months_between(date, date) numeric - Returns the number of months between date1 and date2. If a fractional month is calculated, the months_between function calculates the fraction based on a 31-day month.

---

months_between(date '1995-02-02', date '1995-01-01') -> 1.0322580645161

---

• trunc(date, text) date - truncate date according to the specified format

---

trunc(date '2005-07-12', 'iw') -> 2005-07-11

---

• round(date, text) date - will round dates according to the specified format

---

round(date '2005-07-12', 'yyyy') -> 2006-01-01

---

• to_date(text) timestamp - will typecast input text to timestamp. The GUC orafce.nls_date_format is used to specify input text format for this function. If the value is left blank or set as DEFAULT then input text format according to PostgreSQL's datestyle GUC setting.

• orafce.nls_date_format value to DEFAULT

---

to_date('2014-05-19 17:23:53+5:30') -> 2014-05-19 17:23:53

---

• orafce.nls_date_format='YYYY-MMDD HH24:MI:SS'

---

to_date('2014-0519 17:23:53+5:30')  -> 2014-05-19 17:23:53

---

• to_date(text, text) timestamp - will typecast input text with the specified format to timestamp. The GUC orafce.orafce_emit_error_on_date_bug is used to specify wether an error is reported when the date value hits the Oracle bug on dates. This bug appears with dates before 1582-10-05 when the 'J' format is used ('J2299159') and before 1100-03-01 with other formats. An error is reported by default, to disable this behavior set orafce.orafce_emit_error_on_date_bug to off.

---

SELECT oracle.to_date('112012', 'J');
ERROR:  Dates before 1582-10-05 ('J2299159') cannot be verified due to a bug in Oracle.

SELECT oracle.to_date('1003-03-15', 'yyyy-mm-dd');
ERROR:  Dates before 1100-03-01 cannot be verified due to a bug in Oracle.

SET orafce.oracle_compatibility_date_limit TO off;

SELECT oracle.to_date('112012', 'J');
	to_date
------------------------
 4407-07-30 00:00:00 BC
(1 row)

SELECT oracle.to_date('1003/03/15', 'yyyy/mm/dd');
       to_date
---------------------
 1003-03-15 00:00:00
(1 row)

---

== oracle.date data type

This module contains implementation of oracle compatible DATE data type "oracle.date" and functions which are using DATE data type like oracle.add_months,oracle.last_day(),oracle.next_day(),oracle.months_between() etc.

Example:

set search_path TO oracle,"$user", public, pg_catalog;
create table oracle_date(col1 date);
insert into oracle_date values('2014-06-24 12:12:11'::date);
select * from oracle_date;
        col1         
---------------------
 2014-06-24 12:12:11
(1 row)

---

== oracle.date functions

• oracle.add_months(timestamp with time zone, integer) - Returns date and time plus n months

---

oracle.add_months(oracle.date'2005-05-31 10:12:12',1) -> 2005-06-30 10:12:12

---

• oracle.last_day(timestamp with time zone) - Returns last day of the month based on a date value

---

oracle.last_day(oracle.date '2005-05-24 11:12:12') -> 2005-05-31 11:12:12

---

• oracle.next_day(timestamp with time zone, text) - Returns the first weekday that is greater than a date value

---

oracle.next_day(oracle.date '2005-05-24 10:12:12', 'monday') -> 2005-05-30 10:12:12

---

• oracle.next_day(timestamp with time zone, integer) - Same as above. The second argument should be 1..7 and interpreted as Sunday..Saturday.

---

oracle.next_day(oracle.date '2005-05-24 11:21:12', 1) -> 2005-05-29 11:21:12

---

• oracle.months_between(timestamp with time zone, timestamp with time zone) - Returns the number of months between timestamp1 and timestamp2. If a fractional month is calculated, the months_between function calculates the fraction based on a 31-day month.

---

oracle.months_between(oracle.date '1995-02-02 10:00:00', oracle.date '1995-01-01 10:21:11') -> 1.03225806451613

---

• oracle.to_date(text,text) - Returns timestamp without time zone.

---

oracle.to_date('02/16/09 04:12:12', 'MM/DD/YY HH24:MI:SS') -> 2009-02-16 04:12:12

---

• oracle.to_date(text) - Returns oracle.date

---

oracle.to_date('02/16/09 04:12:12') -> 2009-02-16 04:12:12

---

• oracle.sysdate() - Returns statement timestamp at server timezone (orafce.timezone)

---

oracle.sysdate() -> 2015-12-09 17:47:56

---

• oracle.dbtimezone - Returns server time zone - emulated via orafce.timezone

---

oracle.dbtimezone() -> GMT

---

• oracle.sessiontimezone() - Returns session timezone - current PostgreSQL timezone

---

oracle.sessiontimezone() -> Europe/Prague

---

• oracle.sys_extract_utc(timestamp with timezone) - Returns timestamp in utc timezone

---

oracle.sys_extract_utc(current_timestamp)

---

• oracle.sys_extract_utc(oracle.date) - Returns timestamp in utc timezone, when time zone is not specified, session (current PostgreSQL) timezone is used

---

oracle.sys_extract_utc(oracle.date '2005-05-24 11:21:12', 1) -> 2005-05-24 09:21:12

---

• oracle.to_char(timestamp) - Returns timestamp in nls_date_format.

---

orafce.nls_date_format='YY-MonDD HH24:MI:SS'
oracle.to_char(to_date('14-Jan08 11:44:49+05:30')) -> 14-Jan08 11:44:49
orafce.nls_date_format='YY-MonDD HH24:MI:SS'
oracle.to_char(oracle.to_date('21052014 12:13:44+05:30','DDMMYYYY HH24:MI:SS')) -> 14-May21 12:13:44

---

== oracle.date Operators

• oracle.+(oracle.date,smallint) - Returns oracle.date

---

oracle.to_date('2014-07-02 10:08:55','YYYY-MM-DD HH:MI:SS') + 9::smallint -> 2014-07-11 10:08:55

---

• oracle.+(oracle.date,integer) - Returns oracle.date

---

oracle.to_date('2014-07-02 10:08:55','YYYY-MM-DD HH:MI:SS') + 9::integer -> 2014-07-11 10:08:55

---

• oracle.+(oracle.date,bigint) - Returns oracle.date

---

oracle.to_date('2014-07-02 10:08:55','YYYY-MM-DD HH:MI:SS') + 9::bigint -> 2014-07-11 10:08:55

---

• oracle.+(oracle.date,numeric) - Returns oracle.date

---

oracle.to_date('2014-07-02 10:08:55','YYYY-MM-DD HH:MI:SS') + 9::numeric -> 2014-07-11 10:08:55

---

• oracle.-(oracle.date,smallint) - Returns oracle.date

---

oracle.to_date('2014-07-02 10:08:55','YYYY-MM-DD HH:MI:SS') - 9::smallint -> 2014-06-23 10:08:55

---

• oracle.-(oracle.date,integer) - Returns oracle.date

---

oracle.to_date('2014-07-02 10:08:55','YYYY-MM-DD HH:MI:SS') - 9::integer -> 2014-06-23 10:08:55

---

• oracle.-(oracle.date,bigint) - Returns oracle.date

---

oracle.to_date('2014-07-02 10:08:55','YYYY-MM-DD HH:MI:SS') - 9::bigint -> 2014-06-23 10:08:55

---

• oracle.-(oracle.date,numeric) - Returns oracle.date

---

oracle.to_date('2014-07-02 10:08:55','YYYY-MM-DD HH:MI:SS') - 9::numeric -> 2014-06-23 10:08:55

---

• oracle.-(oracle.date,oracle.date) - Returns double precision

---

oracle.to_date('2014-07-17 11:10:15', 'yyyy-mm-dd hh24:mi:ss') - oracle.to_date('2014-02-01 10:00:00', 'yyyy-mm-dd hh24:mi:ss') -> 166.048785

---

You need to set search_path TO oracle,"$user", public, pg_catalog because functions like oracle.add_months,oracle.last_day,oracle.next_day,oracle.months_between are installed side-by-side with pg_catalog.add_months,pg_catalog.last_day,pg_catalog.next_day,pg_catalog.months_between.

== Table dual

PostgreSQL does not need Oracle's table 'dual', but since it is intensively used by Oracle users, it has been added in orafce. This table is in schema oracle. Usually you want allow unqualified access - so you should to add this schema to search_path configuration (like search_path = 'oracle, pg_catalog, "$user", public' in postgresql.conf).

== Package dbms_output

PostgreSQL sends information to the client via RAISE NOTICE. Oracle uses dbms_output.put_line(). This works differentl