Hi,
I'm trying to upgrade my postgresql version from 9.6.3 to 10.1. I'm running the pg_upgrade check option and I get the next error : could not load library "$libdir/orafce": ERROR: could not load library "path" undefined symbol: Float8GetDatum.

I downloaded the current source code and installed it via make install and I didnt get any errors.
Any idea how to handle it ? Does it suitable for version 10.1?

It seems that ROUND(double precision, integer) is not supported in PostgreSQL.

But it is supported in Oracle:

ROUND(n, integer)

ROUND returns n rounded to integer places to the right of the decimal point. If you omit integer, then n is rounded to 0 places. The argument integer can be negative to round off digits left of the decimal point.

n can be any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. The argument integer must be an integer. If you omit integer, then the function returns the same datatype as the numeric datatype of the argument. If you include integer, then the function returns NUMBER.

This also applyes to TRUNC(double precision, integer)

Here are the founctions that could be added to solve this issue:

CREATE or REPLACE FUNCTION oracle.round(double precision, int)
RETURNS numeric
AS $$SELECT pg_catalog.round($1::numeric, $2)$$
LANGUAGE sql;

CREATE or REPLACE FUNCTION oracle.trunc(double precision, int)
RETURNS numeric
AS $$SELECT pg_catalog.trunc($1::numeric, $2)$$
LANGUAGE sql;

Hi,
my os is redhat 7.3 and I am trying to install orafce to postgresql with psql version 9.2 and server version 9.6. Firstly, i try to install via the script orafce downloaded on github but after reading some topics about extension in postgresql, i understood that the installation only requires to run

CREATE EXTENSION orafce;

in psql because the file in .sql is already present in extension directory of postgresql. But my problem is, when i run this commands it said

ERROR: could not access file "$libdir/orafce: No such file or directory".

When i run the command

SELECT * FROM pg_available_extensions;

to find which version of orafce is compatible with my postgresql version it said version "3.1". So i run the command

CREATE EXTENSION orafce--3.1;

in my database with a superuser and this time it doesn't give any errors. But when i check the extension list in my database as the same user with

\dx

I don't see orafce and I can't use the orafce functions in my pgplsql code.

There are no "GRANT ... ON SCHEMA oracle" commands in the installation script orafce--3.7.sql

with visual studio 2010, I have an error while compiling the project.
the error messages are :

c:\program files\postgresql\9.2\include\server\c.h(96): fatal error C1083: Cannot open include file: 'libintl.h': No such file or directory

..\plvlex.c(23): fatal error C1083: Cannot open include file: 'sqlparse.h'

do you have other dependencies then PostgreSQL we need to install befole compiling the project ?

gcc -O3 -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fno-aggressive-loop-optimizations -I/usr/include/libxml2 -fpic -I. -I../../src/include -D_GNU_SOURCE -I/home/hawq/apache-hawq-src-2.3.0.0-incubating/depends/libhdfs3/build/install/usr/local/hawq/include -I/home/hawq/apache-hawq-src-2.3.0.0-incubating/depends/libyarn/build/install/usr/local/hawq/include -c -o sqlparse.o sqlparse.c
sqlparse.y: In function 'orafce_sql_yyparse':
sqlparse.y:88:17: error: 'result' undeclared (first use in this function)
elements { ((void*)result) = $1; }
^

Hello,
using orafce 3.7
here is what I get

set search_path=oracle,public,pg_catalog;
select nvl(1,1);
org.postgresql.util.PSQLException: ERROR: function nvl(integer, integer) is not unique
Indice : Could not choose a best candidate function. You might need to add explicit type casts.
Position : 8

I don't understand why there are 3 nvl definitions (one under puplic, 2 under oracle schema) ?
Thanks in advance for your help

Regards
PAscal

Hello guys,

I try to create extension in another schema than public, using postgreSQL syntax:

db1=# CREATE EXTENSION IF NOT EXISTS orafce WITH SCHEMA common;
ERROR: schema "public" does not exist

Creating public schema solves the issue it seems orafce create a view to emulate Oracle 'dual' table.

Thank you

Hi i am migrating a function from oracle to postgresql.

    `CREATE OR REPLACE FUNCTION pda.purge ( IN_REQ_EXPORT text, IN_HEAD_EXPORT text, IN_TABLE       text, IN_NAME text, IN_CONDITION text, OUT_ERREUR OUT integer) AS $body$
   DECLARE


reqSelect varchar(10000) := '';
reqDelete varchar(10000) := '';
csv_file UTL_FILE.FILE_TYPE;
ligne varchar(10000);
records refcursor;


    BEGIN
OUT_ERREUR:=0;

reqSelect := IN_REQ_EXPORT || ' WHERE ' || IN_CONDITION;
reqDelete := ' DELETE FROM ' || IN_TABLE || ' WHERE ' || IN_CONDITION;

OPEN records FOR EXECUTE reqSelect;
    FETCH records INTO ligne;

IF Not records%NOTFOUND THEN

	csv_file := UTL_FILE.FOPEN(location     => 'EXTRACT_DIR',
	                           filename     => IN_NAME || '_' || to_char(clock_timestamp(), 'yyyyMMddhh24miss') || '.csv',
	                           open_mode    => 'w',
	                           max_linesize => 32767);

	UTL_FILE.PUT_LINE(file  => csv_file,
                  buffer => IN_HEAD_EXPORT);`

And it crash at the line UTL_FILE.PUT_LINE(file => csv_file, buffer => IN_HEAD_EXPORT);

reporting this error : ERROR: syntax error at or near "UTL_FILE"

I have the orafce well installed and all the other functions of UTL_FILE work well.
# \dx List of installed extensions Name | Version | Schema | Description ---------+---------+------------+----------------------------------------------------------------------------------------------- orafce | 3.1 | public | Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (2 rows)
I can't get what i am doing wrong.

plvlex.c: In function ‘plvlex_tokens’:
plvlex.c:209: warning: call to function ‘orafce_sql_yyparse’ without a real prototype
plvlex.c:39: note: ‘orafce_sql_yyparse’ was declared here

plvlex.c:39: extern int orafce_sql_yyparse();
plvlex.c:209: if (orafce_sql_yyparse(&lexems) != 0)

when using nvl or decode with different numeric data types like numeric and integer
like
select nvl(0,1.0) from dual;
it generates error
function nvl(integer, numeric) does not exist
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

the workaround may be using select nvl(0.0,1.0) from dual;

Hi there
I'm using this extension to write a file

I know every directory must be in the utl_file.utl_file_dir table

if I add /tmp then it works

if I add /tmp/wam then I always get an UTL_FILE_INVALID_PATH error

Is there a bug?

Thanks

Martin

In aggregate.c the following method is erroneous:

int orafce_float8_cmp(const void a, const void *b)
{
return (int) (((float8 ) a) - *((float8) b));
}

cast float8 to int can lead to overflow problems.

On 32 bits platforms orafce_float4_cmp is erroneous too.

This code correct the problem:

int orafce_float8_cmp(const void a, const void *b)
{
float8 diff = *((float8 *) a) - *((float8) b);
if(diff==0)
return 0;
else if(diff<0)
return -1;
}
return 1;

Hi , i've an issue (how do the package works)..
if i've understood, when you have install Orafce for using UTL_FILE,
It creates a table "utl_file.utl_file_dir " where directory path is stored.
To access to it i have just in my "PUT_LINE" function to specify the path directory not to make a select into that table?
that works if the directory is in the same computer (server) where postgresql is install..
if i want to acces to a specific directory in another server where there is not postgres install , how do i do?

help me

Hi guys,

I'm trying to install orafce in a Mac that runs Postgres 10.6.
Running make and make install throwed no errors but when I run CREATE EXTENSION orafce; I get this error:

WARNING:  operator attribute "function" not recognized
ERROR:  operator procedure must be specified

If I try to create the extension using pgAdmin orafce appears in the combo of available extensions but after the confirmation the same error is thrown.

Any ideas?

Thanks!

Running sqlsmith on a DB with orafce installed yielded a couple of
core dumps due to insufficient argument checking in some functions.
The following statements crash trying to dereference a null pointer:

select dbms_random.string(null, 42);
select dbms_pipe.create_pipe(null);
select plunit.assert_not_equals(1,2,3);

That's the original definition of yyerror():

sqlparse.y:void orafce_sql_yyerror(List **result, const char *message);

But plvex.c calls that without the first argument, which will crash:

plvlex.c:extern void orafce_sql_yyerror(const char *message);
plvlex.c:           orafce_sql_yyerror("bogus input");

(Originally reported at https://bugs.debian.org/749804)

hi there

we are trying to process some files with orafce by reading line by line

the file encoding is

file aadf.txt
aadf.txt: UTF-8 Unicode text, with very long lines

chardetect aadf.txt
aadf.txt: utf-8 with confidence 0.99

so we assume it's really a utf-8 file

the SERVER_ENCODING is UTF8
the CLIENT_ENCODING is UTF8
as well

nevertheless we get an ERROR while reading with orafce ult_file the file

ERROR: invalid byte sequence for encoding "UTF8": 0xc3
CONTEXT: PL/pgSQL function read2(text) line 24 at assignment

this is the line where it happens
line_content= utl_file.get_nextline(f);

line_content is of type text

we are processing the same file with python to insert it in the same database and that is working fine

do you have any suggestions?

Thanks

Best Regards

Martin

In the latest version 3.0.7 for PostgreSQL 9.4, orafce's tests now fail on 32 bit intel (i386) architecture because the server crashes:

test orafunc                  ... FAILED (test process exited with exit code 2)
[...]
2014-08-22 01:14:01 UTC [5095-2] LOG:  server process (PID 5164) was terminated by signal 11: Segmentation fault
2014-08-22 01:14:01 UTC [5095-3] DETAIL:  Failed process was running: select to_date('14-Jan08 11:44:49+05:30');

The full log can be seen at https://jenkins.qa.ubuntu.com/job/utopic-adt-orafce/ARCH=i386,label=adt/13/console . Notably the very same test succeeds on x86_64. Previous versions (up to 3.0.6) worked fine, but they didn't include this test yet.

This can be reproduced quite easily:

$ createdb test; psql test
test=# CREATE EXTENSION orafce;
CREATE EXTENSION
test=# set orafce.nls_date_format='DDMMYYYY HH24:MI:SS';
SET
test=# select to_date('21052014 12:13:44+05:30');
The connection to the server was lost. Attempting reset: Failed.
!> 

This is with PostgreSQL 9.4 beta 2.

CREATE FUNCTION oracle.dbtimezone() RETURNS text AS 'MODULE_PATHNAME','orafce_dbtimezone' LANGUAGE C STABLE STRICT; COMMENT ON FUNCTION oracle.sessiontimezone() IS 'Ruturns server time zone (orafce.timezone)';

flag CREATEUSER was removed in 9.6 - regression tests are broken

PostgreSQL 9.4.1 now writes an error message on invalid values of \set ECHO. orafce's tests have two invalid statements:

sql/dbms_pipe_session_B.sql:\set ECHO all;
sql/dbms_pipe_session_A.sql:\set ECHO all;

The semicolon is invalid and interpreted as part of the value, so tests fail with:

*** /tmp/adt-run.kPtv9H/build.I1z/orafce-3.0.7/expected/dbms_pipe_session_A.out 2014-07-27 20:10:01.000000000 +0000
--- /tmp/adt-run.kPtv9H/build.I1z/orafce-3.0.7/results/dbms_pipe_session_A.out  2015-02-08 10:12:42.305125312 +0000
***************
*** 9,14 ****
--- 9,15 ----
              0
  (1 row)

+ unrecognized value "all;" for "ECHO"; assuming "none"
   createimplicitpipe 
  --------------------

(same for the _B test). Merely dropping the semicolon isn't enough as the expected values aren't actually containing the echoed commands. So I suggest to either just drop the two \set ECHO all; lines, or drop the semicolon and add the echoed commands to the expected output.

Hello guys,

We have been using Oracfe for both PG 9 and 10, most recent version we used was 10.2. This week, we have upgraded to 10.4 (binary download from EDB site) and hit the error below while compiling oracfe on top of it.

orafce]$ make
/usr/bin/bison -d -o sqlparse.c sqlparse.y
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -DMAP_HUGETLB=0x40000 -fPIC -I. -I./ -I/home/xxx/platform/vendor/postgres/postgresql-10.4-1-linux-x64-CentOS7/include/postgresql/server -I/home/xxx/platform/vendor/postgres/postgresql-10.4-1-linux-x64-CentOS7/include/postgresql/internal -I/opt/local/Current/include -D_GNU_SOURCE -I/opt/local/Current/include/libxml2 -I/opt/local/Current/include -c -o convert.o convert.c
convert.c: In function ‘orafce_to_multi_byte’:
convert.c:401:2: error: unknown type name ‘__int64’
__int64 dstlen;
^
convert.c: In function ‘orafce_to_single_byte’:
convert.c:477:2: error: unknown type name ‘__int64’
__int64 dstlen;
^
make: *** [convert.o] Error 1

Is Oracfe already supporting 10.4? Thank you.

The current head of the repository fails when used with CREATE EXTENSION command. There are couple of issues that needs to be fixed before we can get it working.

1. Version number mismatch - orafce.control file mentions the current version as 3.0.5, but we haven't yet renamed orafce--3.0.sql and orafce--unpackaged--3.0.sql to the corresponding 3.0.5 names.
2. Even if we do (1), CREATE EXTENSION still fails because many CREATE FUNCTION statements in orafce--3.0.sql file have LANGUAGE specified as 'C' (quoted C). Since the catalog has the language name in lower case, these statements fail to execute.

While we can fix (2) by changing these CREATE FUNCTION statements so that the language name is not quoted, I wonder if a better strategy is to create these files from the other source sql files ? Otherwise we will have two separate sql files to maintain all the time. Comments ?

On a related note, I see that we go out of way to create appropriate orafunc.sql based on the PostgreSQL server version against we are building the module. But the uninstall script is common across all server versions. Is that OK ? Is there a possibility that we will leak certain functions after uninstall, especially if not all orafce functions are available across all PG versions ?

Hi,
Can we use utl_file to read the complete file and put it in a text variable so that we can insert the variable into a table ?

Thanks

raise this error when i create extension

postgres=# create extension orafce;
ERROR:  syntax error at or near "decode"

greenplum version is gpdb-6X_STABLE and the os is CentOS Linux release 7.6.1810 (Core)

please help!!!!

Is there a way we could package up all the objects in orafce so that we could install them on a RDS PG instance?

Looking at what is installed via create extension orafce, it appears I just need to consolidate all the objects like functions, types, casts, etc. and then just deploy them manually to an RDS PG instance database.

Can this be done and if so what is the best way to accomplish this?

We are facing error while building the dll in VS2013.

Please find attached log file for your reference
orafce.2010.zip

This query: select SUBSTR('abcdefg',-5,4) from dual;
In Oracle gets back: "cdef"
In PostgreSQL gets back an empty string.

When padding multi-byte characters using rpad(), a half-width space was added at the beggining.Encoding is UTF8.

$cat test.sql 
select '|' || oracle.RPAD(TO_MULTI_BYTE('±'),10,'あ') || '|' from dual;
$psql -f test.sql postgres                                                                             
 
  ?column?   
--------------
 | ±ああああ|
(1 行)

In such a case, the oracle rpad() adds a half-width space at the end.
I use Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit.

SQL> select '|' || RPAD(TO_MULTI_BYTE('±'),10,'あ') || '|' from dual;

'|'||RPAD(TO_MULT
-----------------
|±ああああ |

I think this is an error of the orafce rpad(), is it correct?

Hi,
i am trying to create ext on postgresql 10 using this command 'CREATE EXTENSION orafce;'
and i am using windows 10, and i got this error
ERROR: could not find function "orafce_dump" in file "C:\PostgreSQL\pg10\share\postgresql\extension\orafce-10-x64.dll"

I can't compile on macOS with postgresql 10.0. I also had this issue with 9.6, but was able to compile with version 3.3.0.

balrog:orafce-VERSION_3_6_0 n$ brew config
HOMEBREW_VERSION: 1.3.6-8-g15096e7
ORIGIN: https://github.com/Homebrew/brew
HEAD: 15096e7289b25ef19baa6c0455f2051583665f70
Last commit: 11 hours ago
Core tap ORIGIN: https://github.com/Homebrew/homebrew-core
Core tap HEAD: 150c67bb6de8c16e0a8a4bb083009ed3ff8ad6fe
Core tap last commit: 2 hours ago
HOMEBREW_PREFIX: /usr/local
HOMEBREW_REPOSITORY: /usr/local/Homebrew
HOMEBREW_CELLAR: /usr/local/Cellar
HOMEBREW_BOTTLE_DOMAIN: https://homebrew.bintray.com
CPU: quad-core 64-bit haswell
Homebrew Ruby: 2.3.3 => /usr/local/Homebrew/Library/Homebrew/vendor/portable-ruby/2.3.3/bin/ruby
Clang: 9.0 build 900
Git: 2.14.2 => /usr/local/bin/git
Perl: /usr/local/bin/perl => /usr/local/Cellar/perl/5.26.1/bin/perl
Python: /usr/local/opt/python/libexec/bin/python => /usr/local/Cellar/python/2.7.14/Frameworks/Python.framework/Versions/2.7/bin/python2.7
Ruby: /Users/n/.rvm/rubies/ruby-2.4.2/bin/ruby
Java: 1.8.0_45
macOS: 10.12.6-x86_64
Xcode: 9.0
CLT: 9.0.0.0.1.1504363082
X11: 2.7.9 => /opt/X11

balrog:orafce-VERSION_3_6_0 n$ make
clang -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2  -I. -I./ -I/usr/local/Cellar/postgresql/10.0/include/server -I/usr/local/Cellar/postgresql/10.0/include/internal  -I/usr/local/opt/openssl/include -I/usr/local/opt/readline/include -I/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.13.sdk/usr/include/libxml2   -c -o convert.o convert.c
clang -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2  -I. -I./ -I/usr/local/Cellar/postgresql/10.0/include/server -I/usr/local/Cellar/postgresql/10.0/include/internal  -I/usr/local/opt/openssl/include -I/usr/local/opt/readline/include -I/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.13.sdk/usr/include/libxml2   -c -o file.o file.c
clang -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2  -I. -I./ -I/usr/local/Cellar/postgresql/10.0/include/server -I/usr/local/Cellar/postgresql/10.0/include/internal  -I/usr/local/opt/openssl/include -I/usr/local/opt/readline/include -I/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.13.sdk/usr/include/libxml2   -c -o datefce.o datefce.c
clang -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2  -I. -I./ -I/usr/local/Cellar/postgresql/10.0/include/server -I/usr/local/Cellar/postgresql/10.0/include/internal  -I/usr/local/opt/openssl/include -I/usr/local/opt/readline/include -I/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.13.sdk/usr/include/libxml2   -c -o magic.o magic.c
clang -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2  -I. -I./ -I/usr/local/Cellar/postgresql/10.0/include/server -I/usr/local/Cellar/postgresql/10.0/include/internal  -I/usr/local/opt/openssl/include -I/usr/local/opt/readline/include -I/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.13.sdk/usr/include/libxml2   -c -o others.o others.c
clang -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2  -I. -I./ -I/usr/local/Cellar/postgresql/10.0/include/server -I/usr/local/Cellar/postgresql/10.0/include/internal  -I/usr/local/opt/openssl/include -I/usr/local/opt/readline/include -I/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.13.sdk/usr/include/libxml2   -c -o plvstr.o plvstr.c
clang -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2  -I. -I./ -I/usr/local/Cellar/postgresql/10.0/include/server -I/usr/local/Cellar/postgresql/10.0/include/internal  -I/usr/local/opt/openssl/include -I/usr/local/opt/readline/include -I/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.13.sdk/usr/include/libxml2   -c -o plvdate.o plvdate.c
clang -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2  -I. -I./ -I/usr/local/Cellar/postgresql/10.0/include/server -I/usr/local/Cellar/postgresql/10.0/include/internal  -I/usr/local/opt/openssl/include -I/usr/local/opt/readline/include -I/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.13.sdk/usr/include/libxml2   -c -o shmmc.o shmmc.c
clang -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2  -I. -I./ -I/usr/local/Cellar/postgresql/10.0/include/server -I/usr/local/Cellar/postgresql/10.0/include/internal  -I/usr/local/opt/openssl/include -I/usr/local/opt/readline/include -I/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.13.sdk/usr/include/libxml2   -c -o plvsubst.o plvsubst.c
clang -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2  -I. -I./ -I/usr/local/Cellar/postgresql/10.0/include/server -I/usr/local/Cellar/postgresql/10.0/include/internal  -I/usr/local/opt/openssl/include -I/usr/local/opt/readline/include -I/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.13.sdk/usr/include/libxml2   -c -o utility.o utility.c
/usr/bin/bison -d  -o sqlparse.c sqlparse.y
sqlparse.y:54.1-5: invalid directive: `%code'
sqlparse.y:54.7-14: syntax error, unexpected identifier
make: *** [sqlparse.c] Error 1

Hi,
I see that master has been updated to support PG 10, thanks for that.
Would you consider releasing a new version with these patches so I can update the packages for Debian and apt.postgresql.org?
Thanks!

Oracle: select to_number('') from dual returns NULL
PostgreSql: select to_number('') from dual throws exception ERROR: invalid input syntax for type numeric: ""

Should add check for empty string in Datum orafce_to_number(PG_FUNCTION_ARGS). Something like:
if( !buf || !*buf ) { PG_RETURN_NULL(); }

float with no precision specified is taken to mean double precision

https://www.postgresql.org/docs/9.0/static/datatype-numeric.html

CREATE OR REPLACE FUNCTION oracle.round(double precision, int)
RETURNS numeric
AS $$SELECT pg_catalog.round($1::numeric, $2)$$
LANGUAGE sql IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION oracle.round(float, int)
RETURNS numeric
AS $$SELECT pg_catalog.round($1::numeric, $2)$$
LANGUAGE sql IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION oracle.trunc(double precision, int)
RETURNS numeric
AS $$SELECT pg_catalog.trunc($1::numeric, $2)$$
LANGUAGE sql IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION oracle.trunc(float, int)
RETURNS numeric
AS $$SELECT pg_catalog.trunc($1::numeric, $2)$$
LANGUAGE sql IMMUTABLE STRICT;

would it be possible to add SYSDATE() funtion

based on
CREATE or REPLACE FUNCTION SYSDATE()
RETURNS oracle.date AS $$
SELECT statement_timestamp()::oracle.date ;
$$ LANGUAGE sql ...

that returns the current date and time when statement starts (not transaction start) set for the operating system on which the database resides. The datatype of the returned value is (Oracle) DATE, and the format returned depends on the value of the NLS_DATE_FORMAT initialization parameter. The function requires no arguments.

Hi ,
i have got the orafce 3.3.1 compile for windows here "https://groups.google.com/forum/#!topic/orafce-general/bZNUXXR1RsQ"
but when i try creating the extension i have an error due to "plvlex_tokens" missing into orafce.dll.

I' m using the "orafce.dll" for postgres 9.5 install in windows 64 bit.
thanks for your answer

Can you please share the link to latest binaries for Windows?
I'm unable to build it on Windows 10

Hi,

I'm getting these while trying to build orafce against 9.5:

• make -j3
  pipe.c: In function 'dbms_pipe_create_pipe':
  pipe.c:1093:72: error: too few arguments to function 'GetUserNameFromId'
  user = (char_)DirectFunctionCall1(namein, CStringGetDatum(GetUserNameFromId(p->uid)));
  ^
  In file included from pipe.c:11:0:
  /usr/pgsql-9.5/include/server/miscadmin.h:299:14: note: declared here
  extern char *GetUserNameFromId(Oid roleid, bool noerr);
  ^
  make[1]: *_* [pipe.o] Error 1
  make[1]: *** Waiting for unfinished jobs....

Hi,
When creating INDEX with VARCHAR 2 type column or executing sort,
the following problem occurred.

ERROR:  could not determine which collation to use for string comparison
HINT:  Use the COLLATE clause to set the collation explicitly.

CentOS 7.3
PostgreSQL 9.6.2
orafce master branch

Procedure for reproducing:

$ initdb -D ~/local/pg962/data --no-locale --encoding=utf8
$ pg_ctl -D ~/local/pg962/data start
$ psql postgres -c "create extension orafce"
CREATE EXTENSION
$ psql postgres -c "create table test(x varchar2(10))"
CREATE TABLE
$ psql postgres -c "insert into test values ('1'),('aaa')"
INSERT 0 2
$ psql postgres -c "create index ON test(x)"
ERROR:  could not determine which collation to use for string comparison
HINT:  Use the COLLATE clause to set the collation explicitly.
STATEMENT: create index on test(x)
ERROR:  could not determine which collation to use for string comparison
HINT:  Use the COLLATE clause to set the collation explicitly.
$ psql postgres -c "select * from test order by x"
ERROR:  could not determine which collation to use for string comparison
HINT:  Use the COLLATE clause to set the collation explicitly.
STATEMENT: select * from test order by x
ERROR:  could not determine which collation to use for string comparison
HINT:  Use the COLLATE clause to set the collation explicitly.

I think that it is caused by not setting collatable to true with CREATE TYPE option.
A same error also occurs in NVARCHAR2.

The error is solved by the following fixes.

$ git diff
diff --git a/orafce--3.5.sql b/orafce--3.5.sql
index ebebb14..341deaf 100644
--- a/orafce--3.5.sql
+++ b/orafce--3.5.sql
@@ -2171,7 +2171,8 @@ receive = varchar2recv,
 send = varchar2send,
 category = 'S',
 typmod_in = varchar2typmodin,
-typmod_out = varchar2typmodout
+typmod_out = varchar2typmodout,
+collatable = true
 );
 
 /* CREATE CAST */
@@ -2373,7 +2374,8 @@ receive = nvarchar2recv,
 send = nvarchar2send,
 category = 'S',
 typmod_in = nvarchar2typmodin,
-typmod_out = nvarchar2typmodout
+typmod_out = nvarchar2typmodout,
+collatable = true
 );
 
 /* CREATE CAST */

regards,
ikki

Do you think possible to add package dbms_sql?

https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sql.htm#BABEDAHF

Hello Team,

I have installed ORAFCE (3.4, 3.6) with my PostgreSQL versions (9.6 Linux and 10.0 on windows).
Most of the functions are working fine except below case

FYI - We have migrated from Oracle to PostgreSQL

Case #1

When I try to run a select query like below with decode function

select decode(lotid,1,'Lot Id Found','Lot Id not found') from public.test_decode; FYI - lotid column is of numeric type
It is showing an error like below

ERROR: function decode(numeric, integer, unknown, unknown) does not exist
LINE 1: select decode(lotid,1,'Lot Id Found','Lot Id not found') fro...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
********** Error **********

ERROR: function decode(numeric, integer, unknown, unknown) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 8

Case #2

At the same time, When I try to run a select query

select decode(lotid::integer,1,'Lot Id Found','Lot Id not found') from public.test_decode;
It is giving proper result

I already have DECODE function as decode(anyelement,anyelement,text,text) still my case 1 failed

FYI - My application contains many such instances of decode function with different number/types o of input

Case #3:
To make Case #1 work,

I created a function in orafce like below

CREATE OR REPLACE FUNCTION public.decode(
numeric,
integer,
text,
text)
RETURNS text AS
'$libdir/orafce', 'ora_decode'
LANGUAGE c IMMUTABLE
COST 1;
ALTER FUNCTION public.decode(numeric, integer, text, text)
OWNER TO postgres;

Now when I run a select query as in CASE #1
select decode(lotid,1,'Lot Id Found','Lot Id not found') from public.test_decode;

Now,Its terminating my session (database connection) and saying connection to the server has lost,
I didn't understand why is this happening.

CASE #4
Finally, I have created a function manually with CASE statement in it

CREATE OR REPLACE FUNCTION public.decode(
input1 numeric,
input2 integer,
input3 text,
input4 text)
RETURNS text AS
$BODY$
DECLARE

returnValue text;
BEGIN

IF input1 = input2 THEN
returnValue = input3;
ELSE
returnValue = input4;
END IF;

RETURN returnValue;
END;
$BODY$
LANGUAGE plpgsql STABLE
COST 100;
ALTER FUNCTION public.decode(numeric, integer, text, text)
OWNER TO postgres;

It is working fine as expected

My question is, why am I not able to achieve this with orafce plugin even though a function already exists?

Please let me know if I miss anything in the process.

Thanks,
Praveen

So that this doesn't happen:

co-pg-test:/usr/local/src/pg931/orafce-VERSION_3_0_6 # make USE_PGXS=1
cp expected/orafunc2.out expected/orafunc.out
co-pg-test:/usr/local/src/pg931/orafce-VERSION_3_0_6 #

Here is an example:
select round(TIMESTAMP WITH TIME ZONE'12/08/1990 05:35:25.123456','DDD')
The result:
1990-12-08 00:00:00.123456+00
As seen fractional seconds have not been rounded

In automatic testing, the dbms_alert_session_X.sql test fail for me about once every 10 times, presumably because the machine is fairly busy and so cannot stay within the timeouts. I suggest to increase the timeouts in all of the tests. Alternatively, maybe there should be a way to disable that group of tests if it cannot be made more reliable.

Here is it:

CREATE OR REPLACE FUNCTION pg_catalog.substrb(varchar2, integer) RETURNS varchar2 AS 'bytea_substr_no_len' LANGUAGE internal STRICT IMMUTABLE; COMMENT ON FUNCTION pg_catalog.substrb(varchar2, integer, integer) IS 'extracts specified number of bytes from the input varchar2 string starting at the specified byte position (1-based) and returns as a varchar2 string';

(from orafce--3.7.sql)

version is 3.2.1 from community yum repo, platform Fedora 22 x86_64

db=> select plvdate.bizdays_between('2016-06-06','2016-06-11');
 bizdays_between 
-----------------
               3
(1 row)

db=> select plvdate.bizdays_between('2016-06-06','2016-06-10');
 bizdays_between 
-----------------
               3
(1 row)

First result should be 4, as there are 4 business days between Monday and Saturday.

Even if this is what Oracle does it seems quite nonsensical.

Hello,

I tried the orafce 3.4.0 source, but an error occurs in the regression test.

RHEL/CentOS 7 posrgresql9.6 RPM provided by the community,
regression test will coredump.
Because the configure option "-enable-dtrace" is enabled in PG9.6 (RHEL/CentOS7).

I used the postgresql RPM below.
https://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/repoview/postgresqldbserver96.group.html

But, there is no problem as PG9.6(RHEL/CentOS 6) RPM by the community does not have "-enable-dtrace" option.
PostgreSQL RPM configure option is different in RHEL/CentOS 6 and 7.

***** regression test *****

$ cat /etc/redhat-release
CentOS Linux release 7.3.1611 (Core)
$ psql --version
psql (PostgreSQL) 9.6.2
$
$ make installcheck
/usr/pgsql-9.6/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --bindir='/usr/pgsql-9.6/bin'    --load-language=plpgsql --schedule=parallel_schedule --encoding=utf8 --dbname=contrib_regression orafce orafce2 dbms_output dbms_utility files varchar2 nvarchar2 aggregates nlssort dbms_random
(using postmaster on Unix socket, default port)
============== dropping database "contrib_regression" ==============
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
============== installing plpgsql                     ==============
CREATE LANGUAGE
============== running regression test queries        ==============
test init                     ... ok
parallel group (2 tests):  dbms_pipe_session_A dbms_pipe_session_B
     dbms_pipe_session_A      ... FAILED (test process exited with exit code 2)
     dbms_pipe_session_B      ... FAILED (test process exited with exit code 2)
parallel group (3 tests):  dbms_alert_session_B dbms_alert_session_A dbms_alert_session_C
     dbms_alert_session_A     ... FAILED (test process exited with exit code 2)
     dbms_alert_session_B     ... FAILED (test process exited with exit code 2)
     dbms_alert_session_C     ... FAILED (test process exited with exit code 2)
test orafce                   ... FAILED (test process exited with exit code 2)
test orafce2                  ... ok
test dbms_output              ... ok
test dbms_utility             ... ok
test files                    ... ok
test varchar2                 ... ok
test nvarchar2                ... ok
test aggregates               ... ok
test nlssort                  ... ok
test dbms_random              ... ok

=======================
 6 of 16 tests failed.
=======================

***** backtrace from coredump. ******

$ gdb postgres core
GNU gdb (GDB) Red Hat Enterprise Linux 7.6.1-94.el7
Copyright (C) 2013 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later 
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.  Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
...
Reading symbols from /usr/pgsql-9.6/bin/postgres...Reading symbols from /usr/lib/debug/usr/pgsql-9.6/bin/postgres.debug...done.
done.
[New LWP 828]
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib64/libthread_db.so.1".
Core was generated by `postgres: postgres contrib_r'.
Program terminated with signal 11, Segmentation fault.
#0  0x00000000006c2265 in LWLockAcquire (lock=lock@entry=0x7f58f1e8ea08, mode=mode@entry=LW_EXCLUSIVE) at lwlock.c:1314
1314            TRACE_POSTGRESQL_LWLOCK_ACQUIRE(T_NAME(lock), T_ID(lock), mode);
Missing separate debuginfos, use: debuginfo-install audit-libs-2.6.5-3.el7_3.1.x86_64 bzip2-libs-1.0.6-13.el7.x86_64 cyrus-sasl-lib-2.1.26-20.el7_2.x86_64 elfutils-libelf-0.166-2.el7.x86_64 elfutils-libs-0.166-2.el7.x86_64 glibc-2.17-157.el7_3.1.x86_64 keyutils-libs-1.5.8-3.el7.x86_64 krb5-libs-1.14.1-27.el7_3.x86_64 libattr-2.4.46-12.el7.x86_64 libcap-2.22-8.el7.x86_64 libcap-ng-0.7.5-4.el7.x86_64 libcom_err-1.42.9-9.el7.x86_64 libgcc-4.8.5-11.el7.x86_64 libgcrypt-1.5.3-13.el7_3.1.x86_64 libgpg-error-1.12-3.el7.x86_64 libselinux-2.5-6.el7.x86_64 libxml2-2.9.1-6.el7_2.3.x86_64 nspr-4.13.1-1.0.el7_3.x86_64 nss-3.28.2-1.6.el7_3.x86_64 nss-softokn-freebl-3.16.2.3-14.4.el7.x86_64 nss-util-3.28.2-1.1.el7_3.x86_64 openldap-2.4.40-13.el7.x86_64 openssl-libs-1.0.1e-60.el7_3.1.x86_64 pam-1.1.8-18.el7.x86_64 pcre-8.32-15.el7_2.1.x86_64 systemd-libs-219-30.el7_3.7.x86_64 xz-libs-5.2.2-1.el7.x86_64 zlib-1.2.7-17.el7.x86_64
(gdb) bt
#0  0x00000000006c2265 in LWLockAcquire (lock=lock@entry=0x7f58f1e8ea08, mode=mode@entry=LW_EXCLUSIVE) at lwlock.c:1314
#1  0x00007f58e9091edb in ora_lock_shmem (size=size@entry=30720, max_pipes=max_pipes@entry=30, max_events=max_events@entry=30, max_locks=max_locks@entry=256,
    reset=reset@entry=0 '\000') at pipe.c:323
#2  0x00007f58e9092bd7 in add_to_pipe (limit_is_valid=0 '\000', limit=0, ptr=0x294ef40, pipe_name=0x290fb88) at pipe.c:506
#3  dbms_pipe_send_message (fcinfo=) at pipe.c:937
#4  0x00000000005c4132 in ExecMakeFunctionResultNoSets (fcache=0x293d950, econtext=0x293d760, isNull=0x293e3f8 "\020", isDone=)
    at execQual.c:2041
#5  0x00000000005c9c5e in ExecTargetList (tupdesc=, isDone=0x7ffdcce3cf94, itemIsDone=0x293e510, isnull=0x293e3f8 "\020", values=0x293e3e0,
    econtext=0x293d760, targetlist=0x293e4e0) at execQual.c:5423
#6  ExecProject (projInfo=, isDone=isDone@entry=0x7ffdcce3cf94) at execQual.c:5647
#7  0x00000000005dec42 in ExecResult (node=node@entry=0x293d650) at nodeResult.c:155
#8  0x00000000005c3208 in ExecProcNode (node=node@entry=0x293d650) at execProcnode.c:392
#9  0x00000000005bf577 in ExecutePlan (dest=0x293b6f0, direction=, numberTuples=0, sendTuples=1 '\001', operation=CMD_SELECT,
    use_parallel_mode=, planstate=0x293d650, estate=0x293d540) at execMain.c:1569
#10 standard_ExecutorRun (queryDesc=0x28b4d10, direction=, count=0) at execMain.c:338
#11 0x00000000006d20e7 in PortalRunSelect (portal=portal@entry=0x28ad030, forward=forward@entry=1 '\001', count=0, count@entry=9223372036854775807,
    dest=dest@entry=0x293b6f0) at pquery.c:948
#12 0x00000000006d34fb in PortalRun (portal=0x28ad030, count=9223372036854775807, isTopLevel=, dest=0x293b6f0, altdest=0x293b6f0,
    completionTag=0x7ffdcce3d420 "") at pquery.c:789
#13 0x00000000006d1146 in PostgresMain (argc=, argv=, dbname=, username=) at postgres.c:1094
#14 0x000000000046f605 in BackendRun (port=0x28b5150) at postmaster.c:4271
#15 BackendStartup (port=0x28b5150) at postmaster.c:3945
#16 ServerLoop () at postmaster.c:1701
#17 0x00000000006748f8 in PostmasterMain (argc=argc@entry=1, argv=argv@entry=0x288da60) at postmaster.c:1309
#18 0x000000000047026e in main (argc=1, argv=0x288da60) at main.c:228
(gdb)

Probably,
I think that orafce needs to call LWLockRegisterTranche() before LWLockAcquire() in each process(session).

It was written as follows in lwlock.h

lwlock.h

> /*
>  * There is another, more flexible method of obtaining lwlocks. First, call
>  * LWLockNewTrancheId just once to obtain a tranche ID; this allocates from
>  * a shared counter.  Next, each individual process using the tranche should
>  * call LWLockRegisterTranche() to associate that tranche ID with appropriate
>  * metadata.  Finally, LWLockInitialize should be called just once per lwlock,
>  * passing the tranche ID as an argument.
>  *
>  * It may seem strange that each process using the tranche must register it
>  * separately, but dynamic shared memory segments aren't guaranteed to be
>  * mapped at the same address in all coordinating backends, so storing the
>  * registration in the main shared memory segment wouldn't work for that case.
>  */

For example, if you do the following in pipe.c,
then regression test will be OK.

Example:

pipe.c

----------
ora_lock_shmem()
:
:
#if PG_VERSION_NUM >= 90600

#if PG_VERSION_NUM < 100000                                                           //add
                                                                                      //add
                        {                                                             //add
                                static LWLockTranche tranche;                         //add
                                                                                      //add
 　                               tranche.name = "orafce";                              //add
   　                             tranche.array_base = &sh_mem->shmem_lock;             //add
  　                              tranche.array_stride = sizeof(LWLock);                //add
  　                              LWLockRegisterTranche(sh_mem->tranche_id, &tranche);  //add
 　                                                                                     //add
　                        }                                                             //add
 　                                                                                     //add
　#endif                                                                                //add
 　                       shmem_lockid = &sh_mem->shmem_lock;
　
　#else
　
 　                       shmem_lockid = sh_mem->shmem_lockid;
　
　#endif
　
 　                       LWLockAcquire(shmem_lockid, LW_EXCLUSIVE);
　:
　:
----------

Regards,
kfuru

I'm looking out how to use UTL_FILE package..
Every time i do"perform UTL_FILE.PUT_LINE ( myUTLfileType, ' text i want to add');"
I've my pgadmin server disconnect...and my application crashes...
what could be the problem?
thanks for your help in advance..

Hi,

I'm migrating a stored procedure wich invokes oracle utl_file.fopen.
Then there an exception block, above the translation from ora2pg

do you have these error codes in orafce ?

UTL_FILE.INVALID_PATH
UTL_FILE.INVALID_MODE
UTL_FILE.INVALID_OPERATION

Thank you very much for you help

    EXCEPTION
            when UTL_FILE.INVALID_PATH then
                    RAISE NOTICE 'INVALID PATH';
                    RAISE EXCEPTION '%', 'Ouverture du fichier de traces : INVALID_PATH' USING ERRCODE = '45010';
            when  UTL_FILE.INVALID_MODE then
                    RAISE NOTICE 'INVALID MODE';
                    RAISE EXCEPTION '%', 'Ouverture du fichier de traces : INVALID_MODE' USING ERRCODE = '45010';
            when  UTL_FILE.INVALID_OPERATION then
                    RAISE NOTICE 'INVALID OPERATION';
                    RAISE EXCEPTION '%', 'Ouverture du fichier de traces : INVALID_OPERATION' USING ERRCODE = '45010';
            when  others THEN
                    RAISE NOTICE 'OTHERS :Erreur SQL%.', DBMS_UTILITY.FORMAT_ERROR_STACK;
                    RAISE EXCEPTION '%', 'Erreur SQL' || DBMS_UTILITY.FORMAT_ERROR_STACK || '.' USING ERRCODE = '45010';

do you know code returned by orafce.fopen in this case ?

Thanks
Best regarf