Orafce not converting Oracle equivalent

111 views
Skip to first unread message

Manish Lad

unread,
Aug 23, 2019, 4:55:45 AM8/23/19
to Better Oracle functions support
Hi All, 

Issue is when I am trying to convert the Oracle objects online connection to Oracle and generating the converted files for the Postgresql even after the oraface is configured it is not replacing the oracle functionalities to be used in the PG. I will have to do the change manually. Could someone point where we ware doing wrong.

Below are the details of the environment that we are using Ex : "Raise application exception" is getting converted in to raise exception like this many other.

Platform Windows 7
Oracle 11g XE
PostgreSQL 11 
Oraface 3.6.2
Ora2PG V19

Configuration file has USE_ORAFCE 1 ( tried with 0 too)

Please advise.

Thanks


Pavel Stehule

unread,
Aug 23, 2019, 4:59:44 AM8/23/19
to orafce-...@googlegroups.com
Hi

pá 23. 8. 2019 v 10:55 odesílatel Manish Lad <manish...@gmail.com> napsal:
Hi All, 

Issue is when I am trying to convert the Oracle objects online connection to Oracle and generating the converted files for the Postgresql even after the oraface is configured it is not replacing the oracle functionalities to be used in the PG. I will have to do the change manually. Could someone point where we ware doing wrong.

Below are the details of the environment that we are using Ex : "Raise application exception" is getting converted in to raise exception like this many other.

I am not sure, but it looks like ora2pg issue than orafce issue?

The place for Ora2pg questions is there https://github.com/darold/ora2pg/issues

Regards

Pavel


Platform Windows 7
Oracle 11g XE
PostgreSQL 11 
Oraface 3.6.2
Ora2PG V19

Configuration file has USE_ORAFCE 1 ( tried with 0 too)

Please advise.

Thanks


--
You received this message because you are subscribed to the Google Groups "Better Oracle functions support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orafce-genera...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/orafce-general/2241c675-f27c-4b59-a6db-65fc1ef311d3%40googlegroups.com.

Manish Lad

unread,
Aug 23, 2019, 6:09:35 AM8/23/19
to orafce-...@googlegroups.com
Thank you for your quick email Pavel.
I am sorry for the confusion 
Oracle functions like : 
dbms_output 
Select * from dual; 
Sysdate()
And other oracle functions that sould support  when orafce is used is not getting converted 
Any pointer 

Thank you.
Manish

Pavel Stehule

unread,
Aug 23, 2019, 6:12:39 AM8/23/19
to orafce-...@googlegroups.com


pá 23. 8. 2019 v 12:09 odesílatel Manish Lad <manish...@gmail.com> napsal:
Thank you for your quick email Pavel.
I am sorry for the confusion 
Oracle functions like : 
dbms_output 
Select * from dual; 
Sysdate()
And other oracle functions that sould support  when orafce is used is not getting converted 
Any pointer 

first, what means "is not getting converted" ?

What tools do you use?

Can you put some examples? Oracle code, converted code, and what do you expect?

Pavel

Manish Lad

unread,
Aug 23, 2019, 6:41:11 AM8/23/19
to Better Oracle functions support
Hi Pavel, 

I am using opensource tool ora2pg ver19 for the conversion. 

I tried running the tool Ora2PG by seeting folowing Orafce parameter in the config file Ora2pg.conf with both the values 1 and 0  
USE_ORAFCE 1

My Oracle Code which has oracel functions such as add_month(), add_year(), date_trunc() and to_char() is getting converted in to PG relevant but not all other. 

Below is the code snippet as requested:

Oracle: 
 
create or replace PROCEDURE TO_CHAR_TEST
AS
v_text varchar2(50);
v_month date;
v_trunc date;

BEGIN

select to_char(birthdate) into v_text from employees1 where firstname ='Nancy';

DBMS_OUTPUT.PUT_LINE(v_text);

select ADD_MONTHS('01-Aug-03', 12) into v_month from dual;

DBMS_OUTPUT.PUT_LINE(v_month);

select trunc(systimestamp) into v_trunc from dual;

DBMS_OUTPUT.PUT_LINE(v_trunc);

END;
 


Converted PG code when use_orafce set to 0: 

CREATE OR REPLACE FUNCTION oradb.to_char_test () RETURNS VOID AS $body$
DECLARE

v_text varchar(50);
v_month timestamp;
v_trunc timestamp;


BEGIN

select birthdate::varchar into STRICT v_text from employees1;

select '01-Aug-03' + '3 month'::interval into STRICT v_month;

select date_trunc('day', CURRENT_TIMESTAMP) into STRICT v_trunc;

END;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;

Converted PG code when use_orafce set to 1: 
CREATE OR REPLACE FUNCTION oradb.to_char_test () RETURNS VOID AS $body$
DECLARE

v_text varchar(50);
v_month timestamp;
v_trunc timestamp;


BEGIN

select to_char(birthdate) into STRICT v_text from employees1;

select ADD_MONTHS('01-Aug-03', 12) into STRICT v_month;

select trunc(CURRENT_TIMESTAMP) into STRICT v_trunc;

END;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;
 


Please do let me know if you need any further information. 

Thanks again.


Thank you.
Manish
Hi

To unsubscribe from this group and stop receiving emails from it, send an email to orafce-...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Better Oracle functions support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orafce-...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Better Oracle functions support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orafce-...@googlegroups.com.

Manish Lad

unread,
Aug 23, 2019, 7:48:11 AM8/23/19
to Better Oracle functions support
i have tested orafce 3.6.2 on windows 32 bit  machine and is working fine.

Now i would like to know where i can get precomplied version of Orafce for pg11 for windows 64 bit machine? 

Thank you.

To unsubscribe from this group and stop receiving emails from it, send an email to orafce-genera...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/orafce-general/b972014e-453c-418f-b0ee-e813b525bd13%40googlegroups.com.

Pavel Stehule

unread,
Aug 25, 2019, 3:24:07 PM8/25/19
to orafce-...@googlegroups.com
Hi

pá 23. 8. 2019 v 13:48 odesílatel Manish Lad <manish...@gmail.com> napsal:
i have tested orafce 3.6.2 on windows 32 bit  machine and is working fine.

Now i would like to know where i can get precomplied version of Orafce for pg11 for windows 64 bit machine? 

I published dll for  32 and 64 bits - so these libs should be somewhere.

Pavel

Pavel Stehule

unread,
Aug 25, 2019, 3:27:24 PM8/25/19
to orafce-...@googlegroups.com


pá 23. 8. 2019 v 12:41 odesílatel Manish Lad <manish...@gmail.com> napsal:


Please do let me know if you need any further information. \

the translated code looks correctly - I don't see any problem.

Regards

Pavel

To unsubscribe from this group and stop receiving emails from it, send an email to orafce-genera...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/orafce-general/b972014e-453c-418f-b0ee-e813b525bd13%40googlegroups.com.

Manish Lad

unread,
Aug 27, 2019, 1:26:38 AM8/27/19
to Better Oracle functions support
Thank you for your email Pavel. 
1. My Oracle Code which has oracel functions such as add_month(), add_year(), date_trunc() and to_char() is getting converted in to PG relevant but not all other. 
2. Could you please share the path for ora2pg v19 precompiled binary for pg 11 version. This is for windows 64 bit 

Pavel Stehule

unread,
Aug 27, 2019, 1:39:08 AM8/27/19
to orafce-...@googlegroups.com


út 27. 8. 2019 v 7:26 odesílatel Manish Lad <manish...@gmail.com> napsal:
Thank you for your email Pavel. 
1. My Oracle Code which has oracel functions such as add_month(), add_year(), date_trunc() and to_char() is getting converted in to PG relevant but not all other. 

ora2pg doesn't convert to orafce anything - only few functions from Orafce are detected by Ora2pg and translated directly. Other not.
 
2. Could you please share the path for ora2pg v19 precompiled binary for pg 11 version. This is for windows 64 bit 

Ora2pg is perl script. there are not binary files.

I newer used Ora2pg on Windows, so I have not any idea, how to do it. Google helps


I don't use MS Windows, so I have not any windows binary files.

Pavel

Manish Lad

unread,
Aug 27, 2019, 1:45:42 AM8/27/19
to Better Oracle functions support
Thank you for your email and helpful information on this conversion. 

Thanks again for your help

Pavel Stehule

unread,
Aug 27, 2019, 1:47:19 AM8/27/19
to orafce-...@googlegroups.com


út 27. 8. 2019 v 7:45 odesílatel Manish Lad <manish...@gmail.com> napsal:
Thank you for your email and helpful information on this conversion. 

Thanks again for your help

:)

Reply all
Reply to author
Forward
0 new messages