PL/SQL Function to call REST webservice
November 25, 2023We have been looking for a while to get a good function to call a webservice in a function. In our case it’s a call to our password manager to retrieve a password and let the function return the password.
In this way we can create users or database links using stored procedures without have to provide passwords during deployments. We have procedures to recreate all the database links in our test environments after we refreshed the environment.
For now we’ll share the function. If you like it, give us a comment and we can share the procedures we are using as well.
CREATE OR REPLACE function DBA_OWNER.get_password
(
p_database in varchar2
,p_username in varchar2
,p_api_key in varchar2
,p_api_id in varchar2
) return varchar2 is
v_req utl_http.req;
v_res utl_http.resp;
request_context utl_http.request_context_key;
v_buffer varchar2(4000);
v_url varchar2(4000);
v_return varchar2(4000);
begin
v_url := 'https://<YOUR_URL_HERE>';
-- In our case we have to provide some input parameters
-- v_url := 'https://testurl.com/api/getpwd/' || p_api_id || '?title=' || p_database || '&username=' || p_username;
-- You need to make sure to create a wallet where you can import the certificate (if it's https)
-- If you need to know how, please drop a comment.
request_context := utl_http.create_request_context(wallet_path => 'file:/opt/dba/wallets/DB1'
,wallet_password => null
,enable_cookies => true
,max_cookies => 300
,max_cookies_per_site => 20);
v_req := utl_http.begin_request(url => v_url
,method => 'GET'
,request_context => request_context);
utl_http.set_header(v_req
,'content-type'
,'application/json');
utl_http.set_header(v_req
,'APIKey'
,p_api_key);
-- Get response.
v_res := utl_http.get_response(v_req);
begin
loop
utl_http.read_line(v_res
,v_buffer);
-- get pwd from buffer
select json_value(v_buffer
,'$.Password') as "pwd"
into v_return
from dual;
end loop;
utl_http.end_response(v_res);
utl_http.end_request(v_req);
utl_http.destroy_request_context(request_context);
exception
when utl_http.end_of_body then
dbms_output.put_line('end_of_body');
utl_http.end_response(v_res);
utl_http.end_request(v_req);
utl_http.destroy_request_context(request_context);
when others then
dbms_output.put_line('others');
utl_http.end_response(v_res);
utl_http.end_request(v_req);
utl_http.destroy_request_context(request_context);
end;
return v_return;
exception
when others then
dbms_output.put_line('main exception');
utl_http.end_response(v_res);
dbms_output.put_line('Procedure ' || owa_util.get_procedure || ' gices the following error : ' || sqlerrm);
end;