How to install / use orafce package in postgresql 9.4?

Question

I'm running the "official" docker container of postgresql in version 9.4. I went inside the running container and installed orafce

docker exec -i -t my_postgres bash
apt-get install postgresql-9.4-orafce

afterwards I've tried to reload and restart the postgresql service, as well as just restarting the whole container, but when I try out using function, which should be defined in orafce, it doesn't work.

I'm talking about a simple example from here:

SELECT add_months(date '2005-05-31',1);  -- > 2005-06-30
SELECT last_day(date '2005-05-24');      -- > 2005-05-31
SELECT next_day(date '2005-05-24', 'monday'); -- > 2005-05-30
SELECT next_day(date '2005-05-24', 2); -- > 2005-05-30
SELECT months_between(date '1995-02-02', date '1995-01-01'); -- > 1.0322580645161
SELECT trunc(date '2005-07-12', 'iw');   -- > 2005-07-11
SELECT round(date '2005-07-12', 'yyyy'); -- > 2006-01-01

and this is the output I get, when simply copy-pasting those commands into phpPgAdmin into postgres/public:

SQL error:

ERROR:  function add_months(date, integer) does not exist
LINE 1:   SELECT add_months(date '2005-05-31',1);  -- > 2005-06-30
                 ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
In statement:

  SELECT add_months(date '2005-05-31',1);  -- > 2005-06-30
  SELECT last_day(date '2005-05-24');      -- > 2005-05-31
  SELECT next_day(date '2005-05-24', 'monday'); -- > 2005-05-30
  SELECT next_day(date '2005-05-24', 2); -- > 2005-05-30
  SELECT months_between(date '1995-02-02', date '1995-01-01'); -- > 1.0322580645161
  SELECT trunc(date '2005-07-12', 'iw');   -- > 2005-07-11
  SELECT round(date '2005-07-12', 'yyyy'); -- > 2006-01-01

Now, I'm aware, I shouldn't directly install packages inside docker, nor using phpPgAdmin, this is just a simple test to see if I can port a smaller oracle DB to postgres.

Is there anything I'm missing in order to be able to use orafce? I couldn't find any errors when installing the package or restarting the library.

score 1 · Accepted Answer · answered Jun 15 '15 at 15:47

1

Ok, a smple

CREATE EXTENSION orafce

is enough

answered Jun 15 '15 at 15:47

peter

14,348
9
62
96

Why do it works? Is this extension compiled aside postgresql default package the postgresql just have to enable it or in somehow postgresql download required deps when you run this command? – deFreitas Sep 19 '18 at 14:58
1

Ok, it [must to be installed on the machine](https://www.postgresql.org/docs/9.1/static/sql-createextension.html) then you just need to tell postgresql to load it – deFreitas Sep 19 '18 at 15:01

How to install / use orafce package in postgresql 9.4?

1 Answers1