I've heard a lot recently that database links are not 'modern' (anyone else noticed this phrase coming up in relation to lots of things lately......). People don't seem to like them as they are tightly coupling the systems together and are often seen as 'hidden' kind of interfaces.
I personally don't share that view - they definitely have their uses and are often the simplest solutions to interface building (they do have their own quirks and issues of course).
However in the spirit of trying to modernize i came up with an alternative using ORDS/Rest/PLSQL to achieve what is done with database links using more loosely coupled 'modern' interfaces.
Now the first part of this is pretty well documented elsewhere - for example here :
1) Install ORDS in standalone mode (you can of course use any of the other hosting options i just chose the easiest to set up for a PoC)
https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-standalone-mode
2) publish a table to a rest endpoint - the first part of Tim's article below covers that - did that just for the DEPT table with which everyone is familiar
https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-using-sql-developer
At this point you have a simple rest interface available that allows you to retrieve the data (which is all i want) or you can do sort of other dml stuff with it.
For example if i browse to this url
http://hostname:8080/ords/scott/dept/
i get this json text back (not as readable as SQL/XML .... :-))
{"items":[{"deptno":10,"dname":"ACCOUNTING","loc":"NEW YORK","links":[{"rel":"self","href":"http://hostname:8080/ords/scott/dept/10"}]},{"deptno":20,"dname":"RESEARCH","loc":"DALLAS","links":[{"rel":"self","href":"http://hostname:8080/ords/scott/dept/20"}]},{"deptno":30,"dname":"SALES","loc":"CHICAGO","links":[{"rel":"self","href":"http://hostname:8080/ords/scott/dept/30"}]},{"deptno":40,"dname":"OPERATIONS","loc":"BOSTON","links":[{"rel":"self","href":"http://hostname:8080/ords/scott/dept/40"}]}],"hasMore":false,"limit":25,"offset":0,"count":4,"links":[{"rel":"self","href":"http://hostname:8080/ords/scott/dept/"},{"rel":"edit","href":"http://hostname:8080/ords/scott/dept/"},{"rel":"describedby","href":"http://hostname:8080/ords/scott/metadata-catalog/dept/"},{"rel":"first","href":"http://hostname:8080/ords/scott/dept/"}]}
You can also call this using the chrome advanced rest client which is nicer to work with and offers loads more options and a more readable format
https://advancedrestclient.com/
that looks something like this with the same data above
All very nice - this can then be called by all sorts of modern stuff and all the hipsters are happy....
However lets use this in another way and teach an old dog some new tricks and mimic database link functionality using rest directly in the database
1) first old school type definition
CREATE TYPE dept_row AS OBJECT (
deptno number(2),
dname VARCHAR2(14),
loc VARCHAR2(14)
);
2) followed by old school table of type definition
create type restdept is table of dept_row;
3) Now we turn it up the modernness to 11 and create this function
CREATE OR REPLACE FUNCTION resttest
RETURN restdept pipelined
AS
restresults CLOB;
l_count NUMBER;
BEGIN
apex_web_service.g_request_headers(1).name := 'Content-Type';
apex_web_service.g_request_headers(1).Value := 'application/x-www-form-urlencoded; charset=utf-8';
restresults := apex_web_service.make_rest_request(p_url => 'http://hostname:8080/ords/scott/dept/', p_http_method => 'GET');
apex_json.parse(restresults);
l_count := apex_json.get_count(p_path=>'items');
FOR i IN 1..l_count
LOOP
PIPE ROW(dept_row(
apex_json.get_varchar2(p_path=>'items[' || trim(TO_CHAR(i)) ||'].deptno'),
apex_json.get_varchar2(p_path=>'items[' || trim(TO_CHAR(i)) ||'].dname'),
apex_json.get_varchar2(p_path=>'items[' || trim(TO_CHAR(i)) ||'].loc') ));
END LOOP;
END;
/
Followed by a select to call it
SELECT * from TABLE(resttest);
and....
There we go - turns out sql/plsql is modern after all and not just some old guy turning up wearing teenagers clothes.....
Now this probably isn't very or quick and I've no doubt missed some trick that makes this even easier - however i still think this is quite a neat trick.
Whether it gets practically used for anything though I'm not sure but it's nice to see this sort of stuf is possible.
I'd be interested in any feedback if anyone else is architecting anything this way.....
Oh nd by the way (though you'd probably guess anyway) - you need apex installed to access a couple of the packages i use in the function - but that's modern - so everyone has that right....? :-)
Comments
Post a Comment