Is there a way in Oracle to call a restful API through Oracle query? Case: upon insertion a certain record in database, a trigger will call an API outside the machine using JSON format.
Add a comment |
2 Answers
Wrap the INSERT in a stored procedure and add a call to one of the methods in the UTL_HTTP package to make the call to the external API.
Something like (untested):
CREATE PROCEDURE YOUR_SCHEMA.CREATE_ITEM( i_value1 YOUR_SCHEMA.YOUR_TABLE.VALUE1%TYPE, i_value2 YOUR_SCHEMA.YOUR_TABLE.VALUE2%TYPE, i_value3 YOUR_SCHEMA.YOUR_TABLE.VALUE3%TYPE ) IS req UTL_HTTP.REQ; resp UTL_HTTP.RESP; content VARCHAR2(4000); buffer VARCHAR2(4000); BEGIN INSERT INTO YOUR_SCHEMA.YOUR_TABLE ( value1, value2, value3 ) VALUES ( i_value1, i_value2, i_value3 ); req := UTL_HTTP.BEGIN_REQUEST ( url => 'https://your_server.here/rest/items', method => 'POST' ); content := '{' || '"value1":"' || value1 || '",' -- remember to escape special characters || '"value2":"' || value2 || '",' || '"value3":"' || value3 || '"' || '}'; UTL_HTTP.SET_HEADER( req, 'content-type', 'application/json' ); UTL_HTTP.SET_HEADER( req, 'Content-Length', length(content) ); UTL_HTTP.WRITE_TEXT( r => req, data => content ); resp := UTL_HTTP.GET_RESPONSE(req); BEGIN LOOP UTL_HTTP.READ_LINE( resp, buffer, TRUE ); DBMS_OUTPUT.PUT_LINE( buffer ); END LOOP; UTL_HTTP.END_RESPONSE(resp); EXCEPTION WHEN UTL_HTTP.END_OF_BODY THEN UTL_HTTP.END_RESPONSE(resp); END; END; / 
Comments
i think you can call a RESTful API from an Oracle query using Oracle's built-in packages UTL_HTTP 1 Necessary Privileges
GRANT EXECUTE ON UTL_HTTP TO your_users; GRANT EXECUTE ON DBMS_NETWORK_ACL_ADMIN TO your_users; Configure Network ACLallow the Oracle user to access a specific web service
BEGIN DBMS_NETWORK_ACL_ADMIN.create_acl( acl => 'api_access.xml', description => 'Access to RESTful APIs', principal => 'your_user', is_grant => TRUE, privilege => 'connect' ); DBMS_NETWORK_ACL_ADMIN.assign_acl( acl => 'api_access.xml', host => 'api.example.com', lower_port => 80, upper_port => 80 ); END; / 3 Procedure
CREATE OR REPLACE PROCEDURE call_rest_api ( p_url IN VARCHAR2 ) AS l_req UTL_HTTP.req; l_resp UTL_HTTP.resp; l_buffer VARCHAR2(32767); BEGIN -- HTTP request l_req := UTL_HTTP.begin_request(p_url, 'POST', 'HTTP/1.1'); UTL_HTTP.set_header(l_req, 'Content-Type', 'application/json'); -- JSON data UTL_HTTP.write_text(l_req, '{"key":"value"}'); -- HTTP call l_resp := UTL_HTTP.get_response(l_req); -- response BEGIN LOOP UTL_HTTP.read_text(l_resp, l_buffer); DBMS_OUTPUT.put_line(l_buffer); END LOOP; EXCEPTION WHEN UTL_HTTP.end_of_body THEN UTL_HTTP.end_response(l_resp); END; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line('Error: ' || SQLERRM); UTL_HTTP.end_response(l_resp); END; / last = Trigger
REATE OR REPLACE TRIGGER after_insert_trigger AFTER INSERT ON your_table FOR EACH ROW BEGIN call_rest_api('http://api.your.com/resource'); END; / 
