orafce

orafce Overview

orafce is MogDB compatibility package for Oracle, which can support some Oracle tables, functions, and data types. orafce provides functions and operators that emulate a subset of functions and packages from the Oracle RDBMS.

This plugin contains some useful functions that can help with porting Oracle application to MogDB/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 1100-03-01 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 GitHub orafce.

Install orafce

For details, please refer to gs_install_plugin or gs_install_plugin_local.

Create and Use orafce

create extension orafce;

Check all schemas:

\dn

dbms_output Use Method

Check functions where some functions include parameters and some functions do not include parameters. The parameter type is boolean.

\df dbms_output.*

Example of functions that do not include parameters:

select dbms_output.disable();

Example of functions that include parameters:

select dbms_output.put_line('sss');

Boolean: (here the value is 0 or 1)

select dbms_output.serveroutput(0);

dbms_random Use Method

Check functions where some functions include parameters and some functions do not include parameters.

\df

Example of functions that include parameters:

select dbms_random.initialize(1);
select dbms_random.value(256.1243434351237831823812312,12333.3111);

Value does not have to have the parameter value inputted.

Example of functions that do not include parameters:

select dbms_random.value();

dbms_utility Use Method

Check functions.

\df

select dbms_utility.format_call_stack();
select dbms_utility.format_call_stack('o');

Only [ops] is allowed for functions that include parameters.

select dbms_utility.get_time();

oracle Function Usage

Check functions.

\df

select oracle.btrim('enmo');
select oracle.get_full_version_num();
select oracle.get_major_version();
select oracle.get_major_version_num();
select oracle.get_platform();
select oracle.get_status();
select oracle.length(1);
select oracle.lpad('enmo', 1);
select oracle.ltrim('enmo', 'enmo');
select oracle.numtodsinterval(12.22,'1232');
select oracle.nvl(1,2);
select oracle.regexp_count('enmo', 'tech');
select oracle.regexp_instr('enmo', 'tech');
select oracle.regexp_replace('enmo', 'tech', 'sss', 1);
select oracle.regexp_substr('enmo', 'tech', 1);

replace_empty_strings:

CREATE TABLE trg_test(a varchar, b int, c varchar, d date, e int);
CREATE TRIGGER trg_test_xx BEFORE INSERT OR UPDATE ON trg_test FOR EACH ROW EXECUTE PROCEDURE oracle.replace_empty_strings(true);
\pset null ***
INSERT INTO trg_test VALUES('',10, 'AHOJ', NULL, NULL);
INSERT INTO trg_test VALUES('AHOJ', NULL, '', '2020-01-01', 100);
SELECT * FROM trg_test;

replace_null_strings:

CREATE TABLE trg_test(a varchar, b int, c varchar, d date, e int);
CREATE TRIGGER trg_test_xx BEFORE INSERT OR UPDATE ON trg_test FOR EACH ROW EXECUTE PROCEDURE oracle.replace_null_strings();
\pset null ***
INSERT INTO trg_test VALUES(NULL, 10, 'AHOJ', NULL, NULL);
INSERT INTO trg_test VALUES('AHOJ', NULL, NULL, '2020-01-01', 100);
SELECT * FROM trg_test;

SELECT * FROM trg_test;
select oracle.round(1.212, 1);
select oracle.rpad('enmo', 1);
select oracle.rtrim('yunhe', 'enmo');
select oracle.sessiontimezone();
select oracle.substr(111.122,1);
select oracle.to_char('14-Jan08 11:44:49+05:30');
select oracle.translate_oracle_modifiers('icnsmx',true);

(The parameter must include one or more of 'icnsmx'. m will be converted to n, and n will be converted to s. If the parameter is set to true, g will be added to the end of the string. Otherwise, g is not added.)

select oracle.trunc(122.31, 1);
select oracle.unistr('yunhe-enmo');

utl_file Usage

Check functions.

\df

File Check Operation

CREATE OR REPLACE FUNCTION checkFlushFile(dir text) RETURNS void AS $$
DECLARE
 f utl_file.file_type;
 f1 utl_file.file_type;
 ret_val text;
 i integer;
BEGIN
 f := utl_file.fopen(dir, 'regressflush_orafce.txt', 'a');
 PERFORM utl_file.put_line(f, 'ABC');
 PERFORM utl_file.new_line(f);
 PERFORM utl_file.put_line(f, '123'::numeric);
 PERFORM utl_file.new_line(f);
 PERFORM utl_file.putf(f, '[1=%s, 2=%s, 3=%s, 4=%s, 5=%s]', '1', '2', '3', '4', '5');
 PERFORM utl_file.fflush(f);
 f1 := utl_file.fopen(dir, 'regressflush_orafce.txt', 'r');
 ret_val=utl_file.get_nextline(f1);
 i:=1;
 WHILE ret_val IS NOT NULL LOOP
  RAISE NOTICE '[%] >>%<<', i,ret_val;
  ret_val := utl_file.get_nextline(f1);
  i:=i+1;
 END LOOP;
 RAISE NOTICE '>>%<<', ret_val;
 f1 := utl_file.fclose(f1);
 f := utl_file.fclose(f);
END;
$$ LANGUAGE plpgsql

File Read Operation

CREATE OR REPLACE FUNCTION read_file(dir text) RETURNS void AS $$
DECLARE
 f utl_file.file_type;
BEGIN
 f := utl_file.fopen(dir, 'regress_orafce.txt', 'r');
 FOR i IN 1..11 LOOP
  RAISE NOTICE '[%] >>%<<', i, utl_file.get_line(f);
 END LOOP;
 RAISE NOTICE '>>%<<', utl_file.get_line(f, 4);
 RAISE NOTICE '>>%<<', utl_file.get_line(f, 4);
 RAISE NOTICE '>>%<<', utl_file.get_line(f);
 RAISE NOTICE '>>%<<', utl_file.get_line(f);
 EXCEPTION
  -- WHEN no_data_found THEN,  8.1 plpgsql doesn't know no_data_found
  WHEN others THEN
   RAISE NOTICE 'finish % ', sqlerrm;
   RAISE NOTICE 'is_open = %', utl_file.is_open(f);
   PERFORM utl_file.fclose_all();
   RAISE NOTICE 'is_open = %', utl_file.is_open(f);
 END;
$$ LANGUAGE plpgsql;

Issue