Skip to Main Content
 
Titelbild Muniqsoft Training

Search Results

Oracle Tipp Text

Oracle Datenaustausch mit Postgres via REST

Bereich:Postgres:REST, Version: ab RDBMS 12.x:RDBMS 18.1:RDBMS 19.1, Letzte Überarbeitung: 25.03.2021

Keywords:Oracle, Postgres, REST

Wenn man mit zwei Datenbanken parallel arbeiten möchte, stellt sich immer die Frage, wie geht das am Besten?

Wir wollen hier mal die REST Schnittstelle als Verbindung zwischen Oracle und Postgres verwenden.
Wir verwenden hier PostgREST
https://postgrest.org/en/v7.0.0/

Installationsverzeichnis aussuchen:

cd /var/lib/pgsql


 Software herunterladen:

wget https://github.com/PostgREST/postgrest/releases/download/v7.0.1/postgrest-v7.0.1-linux-x64-static.tar.xz

 
Auspacken:

tar xJf postgrest-<version>-<platform>.tar.xz


REM Rest Service Dienst starten

./postgrest


Schema und Beispieltabelle anlegen:

CREATE SCHEMA muniq;
CREATE TABLE muniq.todos (   id serial primary key,   done boolean not null default false,   task text not null,   due timestamptz );
insert into muniq.todos (task) values   ('finish tutorial 0'), ('pat self on back');


Rolle anlegen

CREATE ROLE web_anon nologin;


Nur Leserechte auf Schema

grant usage on schema muniq to web_anon;
grant select on muniq.todos to web_anon;


Anmelde Benutzer anlegen:

CREATE ROLE authenticator NOINHERIT LOGIN PASSWORD 'muso2021muso2021';
GRANT web_anon TO authenticator;


vi tutorial.conf

db-uri = "postgres://authenticator:muso2021muso2021@localhost:5432/xe18c"
db-schema = "muniq"
db-anon-role = "web_anon"


Passwort eintragen:

echo jwt-secret ="\"`date | md5sum | head -c32`\"" >> tutorial.conf
Rest Dienst mit Konfig-Datei starten
./postgrest tutorial.conf


Mit Ihrem Passwort gehen Sie bitte in die Webseite und führen folgende Schritte aus

cat tutorial.conf | grep jwt-secret
https://jwt.io/#debugger-io
1. Header:
{   "alg": "HS256",   "typ": "JWT" }
2. Payload:
{"role": "todo_user"}
3. PWD aus jwt-secret
4. Secret nicht anwählen
5. Token rauskopieren


Verwenden Sie ein zweite Session:
Lesetest:

curl http://localhost:3000/todos

Schreibtest:

export TOKEN="eyJhbGciOaJIUzI1NiIsInR5cCI6IkpXxCJ8.eyJyb2xlIjoidd9kb191c2Vycn0.Vl2f3BH_4iLc-PzY1SE74svS9mDvgdSkEFH7_2ReJsA"
curl http://localhost:3000/todos -X POST \      -H "Authorization: Bearer $TOKEN"   \      -H "Content-Type: application/json" \      -d '{"id":4,"task": "mp"}'


Beispiele für Lesefilter:

curl http://localhost:3000/todos -X GET \      -H "Authorization: Bearer $TOKEN"   \      -H "Content-Type: application/json"

Eine Zeile anzeigen

id=1 (equal 1)
curl http://localhost:3000/todos?id=eq.1
id <3  (lower Then <)
curl http://localhost:3000/todos?id=lt.3
id > 3 (greater then >)
curl http://localhost:3000/todos?id=gt.3
id >3 or id <2
curl "http://localhost:3000/todos?or=(id.gt.3,id.lt.2)"


Weitere Beispiele: https://postgrest.org/en/v7.0.0/api.html

Insert neue Zeile

curl http://localhost:3000/todos -X POST \     
-H "Authorization: Bearer $TOKEN"   \     
-H "Content-Type: application/json" \     
-d '{"id":4,"task": "mp"}'


Update (auf alle Zeilen)

curl http://localhost:3000/todos -X PATCH \     
-H "Authorization: Bearer $TOKEN"    \     
-H "Content-Type: application/json"  \      -
d '{"done": true}'


Update (auf eine Zeile (alle Spalten müssen angegeben werden!))

curl http://localhost:3000/todos?id=eq.3 -X PUT \     
-H "Authorization: Bearer $TOKEN"    \     
-H "Content-Type: application/json"  \     
-d '{"id":3, "task":"Test","done": true}'


Delete (eine Zeile)

curl http://localhost:3000/todos?id=eq.4 -X DELETE \     
-H "Authorization: Bearer $TOKEN"    \     
-H "Content-Type: application/json"


Sehen wir uns mal die Seite von Oracle aus an. Wie kann man die Postgres Schnittstelle ansprechen?
Dafür bietet sich das Package apex_web_service an, das installiert ist, wenn Sie auch APEX/ORDS installiert haben.
Wir schreiben uns ein kleines Package, das die Daten im JSON Format an die Postgres REST Schnittstelle übergibt:
 

CREATE OR REPLACE PACKAGE postgres_rest IS
FUNCTION get (
table_name IN VARCHAR2,
filter IN VARCHAR2 DEFAULT NULL)
RETURN CLOB;

PROCEDURE write (
table_name  IN VARCHAR2,
modus       IN VARCHAR2,
filter      IN VARCHAR2 DEFAULT NULL,
body        IN CLOB  DEFAULT NULL);
END;
/


und der dazugehörige Body. Bitte beachten Sie, dass der Bearer Schlüssel in Zeile 11 angepasst werden muss. Diese haben Sie im Schritt auf der Seite https://jwt.io/#debugger-io
(siehe oben) bekommen.
Wenn der Schlüssel nicht passt, bekommen Sie eine Fehlermeldung, dass Sie keine Rechte auf der Tabelle haben.
Passen Sie auch bitte die IP Adresse vom Ziel Server an (bei uns 172.30.30.8)
 


CREATE OR REPLACE PACKAGE BODY postgres_rest IS
vRestResult  CLOB;
v_request VARCHAR2(32000);

PROCEDURE INIT
IS BEGIN
apex_web_service.g_request_headers.delete();
apex_web_service.g_request_headers(1).name  := 'Content-Type';
apex_web_service.g_request_headers(1).value := 'application/json';
apex_web_service.g_request_headers(2).name  := 'Authorization';  
apex_web_service.g_request_headers(2).value := 'Bearer eyJhbGciOiJIUzI1NiIsInR5cdI7IkpXVCJ9.eyJyb2xlIjoidG9kb101c2VyIn0.CUZ02WPyLV-QdVtokEZmdqDkWrOqSStlWbADj1Sz_uU';
END INIT;

-- ################################################################################################

FUNCTION get (table_name IN VARCHAR2, filter IN VARCHAR2 DEFAULT NULL ) RETURN CLOB
IS  

BEGIN  
init;
IF filter IS NOT NULL THEN
    v_request:=table_name||'?'||filter;
ELSE
     v_request:=table_name;
END IF;

vRestResult := apex_web_service.make_rest_request(    
p_url => 'http://172.30.30.8:3000/'||v_request,    
p_http_method => 'GET');
RETURN vRestResult;
END;

-- ################################################################################################

PROCEDURE write (
table_name  IN VARCHAR2,
modus       IN VARCHAR2,
filter      IN VARCHAR2 DEFAULT NULL,
body        IN CLOB DEFAULT NULL
)
IS
v_mode VARCHAR2(2001);
BEGIN
init;
IF    substr(upper(modus),1,1)='I' THEN
v_mode:='POST';
ELSIF substr(upper(modus),1,1)='U' THEN
v_mode:='PUT';
ELSIF substr(upper(modus),1,1)='D' THEN
v_mode:='DELETE';
ELSE
    RAISE_APPLICATION_ERROR(-20000,'Invalid Mode (I)nsert, (U)pdate, (D)elete');
END IF;
IF v_mode IN ('PUT','DELETE') THEN
    v_request:=table_name||'?'||filter;
ELSE
    v_request:=table_name;
END IF;

vRestResult := apex_web_service.make_rest_request(    
p_url => 'http://172.30.30.8:3000/'||v_request,
p_body =>body,
p_http_method => v_mode);
--dbms_output.put_line('Request:'||v_request);
--dbms_output.put_line(substr(vRestResult,1,4000));
END write;
END;
/

    
Als Bonus-Track schreiben wir uns einen Trigger, der das obige Oracle  Package nutzt und alle Änderungen auf Oracle Seite in der Tabelle in eine gleiche Tabelle auf Postgres synchon spiegelt.
Hinweis die Tabelle (mit Primary Key) muss in Postgres jedoch angelegt werden:

Hinweis: Auf Postgres Seite ausführen !
CREATE TABLE muniq.emp (
    empno numeric CONSTRAINT PK_EMP PRIMARY KEY,
    ename character varying(10),
    job character varying(9),
    mgr numeric,
    hiredate date,
    sal numeric(7,2),
    comm numeric(7,2),
    deptno numeric(2,0));

 

Hinweis: Auf Oracle Seite ausführen
CREATE OR REPLACE TRIGGER EMP_TRG
AFTER DELETE OR INSERT OR UPDATE ON EMP
FOR EACH ROW
DECLARE
v_tab_name   VARCHAR2(2001):='emp';
v_tab_filter VARCHAR2(2001):='empno=eq.'||nvl(:new.empno,:old.empno);
v_body CLOB;
BEGIN

  v_body:='{
     "empno": "'  ||:new.empno ||'"' ||
     CASE WHEN :new.mgr     IS NOT NULL THEN ',"ename":"'   ||:new.ename    ||'"' END ||
     CASE WHEN :new.job     IS NOT NULL THEN ',"job":"'     ||:new.job      ||'"' END ||
     CASE WHEN :new.mgr     IS NOT NULL THEN ',"mgr":"'     ||:new.mgr      ||'"' END ||
     CASE WHEN :new.hiredate IS NOT NULL THEN ',"mgr":"'    ||:new.hiredate ||'"' END ||
     CASE WHEN :new.sal     IS NOT NULL THEN ',"sal":"'     ||:new.sal      ||'"' END ||
     CASE WHEN :new.comm    IS NOT NULL THEN ',"comm":"'    ||:new.comm     ||'"' END ||
     CASE WHEN :new.deptno  IS NOT NULL THEN ',"deptno":"'  ||:new.deptno   ||'"' END ||
     '}';

  --dbms_output.put_line('Debug:'||v_body);   
  IF INSERTING THEN  
   postgres_rest.write(
   table_name   =>v_tab_name,
   modus        =>'I',
   body         =>v_body);
  ELSIF UPDATING THEN
    postgres_rest.write(
    table_name  =>v_tab_name,
    modus       =>'U',
    body        =>v_body,
    filter=>v_tab_filter);
  ELSIF DELETING THEN
    postgres_rest.write(
    table_name  =>v_tab_name,
    modus       =>'D',
    filter      =>v_tab_filter);
  END IF;
END;
/


Beispiele zum Packageaufruf:

select postgres_rest.get(table_name=>'todos') from dual;
EXEC postgres_rest.write(table_name=>'todos',modus=>'I',body=>'{"id":6, "task": "1234"}');
EXEC postgres_rest.write(table_name=>'todos',modus=>'U',filter=>'id=eq.6',body=>'{"id":6, "task": "12345"}');
EXEC postgres_rest.write(table_name=>'todos',modus=>'D',filter=>'id=eq.6');


Beispiele zum Trigger-Testen:

insert into emp (empno,ename) values (8008,'Marco');
update emp set
ename='Marco2' where empno=8008;
delete from emp where empno=8008;   



Nun kann man Oracle und Postgres wunderbar miteinander verbinden. Weitere Tipps & Tricks erfahren Sie u.a. in unserm Oracle ORDS Kurs.

 



Weitere Interessente Artikel zum Thema:


Empfohlene Schulungen zum Thema:
2026.02.18