oracle.add_months for the last day of the month

[r.taka...@jp.fujitsu.com]

Hi,

In Oracle database, add_months returns the last day of the resulting month if the input date is the last day of the month.

SQL> select add_months('2018-11-30', 1) from dual;

ADD_MONTHS('2018-11

-------------------

2018-12-31 00:00:00

In orafce, pg_catalog.add_months works well, but oracle.add_months does not.

postgres=# select pg_catalog.add_months('2018-11-30', 1) from dual;

 add_months

------------

 2018-12-31

(1 row)

postgres=# select oracle.add_months('2018-11-30', 1) from dual;

     add_months

---------------------

 2018-12-30 00:00:00

(1 row)

The problem is that oracle.add_months is not implemented for the case of the last day of the month as following.

CREATE FUNCTION oracle.add_months(TIMESTAMP WITH TIME ZONE,INTEGER)

RETURNS TIMESTAMP

AS $$ SELECT ($1 + interval '1 month' * $2)::oracle.date; $$

LANGUAGE SQL IMMUTABLE STRICT;

Regards,

Ryohei Takahashi

[Pavel Stehule]

Hi

út 13. 11. 2018 v 2:14 odesílatel <r.taka...@jp.fujitsu.com> napsal:

should be fixed now. Unfortunately the fix needs drop/create extension

Thank you for report

Regards

Pavel

 

Regards,

Ryohei Takahashi

--
You received this message because you are subscribed to the Google Groups "Better Oracle functions support" group.
To post to this group, send email to orafce-...@googlegroups.com.
Visit this group at https://groups.google.com/group/orafce-general.

[r.taka...@jp.fujitsu.com]

Hi,

Thank you for replying.

I think the new commit contains a little mistake.

"pg_catalog.add_months($1::date, 1)" should be "pg_catalog.add_months($1::date, $2)".

Regards,

Ryohei Takahashi

[Pavel Stehule]

st 14. 11. 2018 v 1:21 odesílatel <r.taka...@jp.fujitsu.com> napsal:

Hi,

Thank you for replying.

I think the new commit contains a little mistake.

"pg_catalog.add_months($1::date, 1)" should be "pg_catalog.add_months($1::date, $2)".

I am sorry. Should be fixed now.

Thank you for double check

[r.taka...@jp.fujitsu.com]

Thank you for fixing.

I'll use it.

Regards,

Ryohei Takahashi

[r.taka...@jp.fujitsu.com]

Sorry, I found another problem.

Orafce_Documentation_02.md instructs users to set search_path = '"$user", public, oracle, pg_catalog'.

In this case, new add_months does not work since "date" in pg_catalog.add_months($1::date, $2) is treated as oracle.date.

Therefore, I think "pg_catalog.add_months($1::date, $2)" should be "pg_catalog.add_months($1::pg_catalog.date, $2)".

Regards,

Ryohei Takahashi

[Pavel Stehule]

čt 15. 11. 2018 v 2:31 odesílatel <r.taka...@jp.fujitsu.com> napsal:

sure, I am sorry. Should be fixed now. I checked it against Oracle.

Regards

[r.taka...@jp.fujitsu.com]

Thank you for fixing.

Regards,

Ryohei Takahashi

[Pavel Stehule]

čt 15. 11. 2018 v 9:14 odesílatel <r.taka...@jp.fujitsu.com> napsal:

Thank you for fixing.

I thank you for report and check

[r.taka...@jp.fujitsu.com]

Hi,

Thank you for your announcement of plan for Orafce 3.7!

I tried to update Orafce from 3.6 to 3.7, but failed as follows.

postgres=# alter extension orafce update;

ERROR:  function "add_months" already exists with same argument types

oracle.add_months in orafce--3.6--3.7.sql is defined by using "CREATE FUNCTION".

I think it should be defined by using "CREATE OR REPLACE FUNCTION".

Regards,

Ryohei Takahashi

[Pavel Stehule]

po 3. 12. 2018 v 2:32 odesílatel <r.taka...@jp.fujitsu.com> napsal:

Thank you for check

should be fixed now.