Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Madhan.

Asked: March 20, 2017 - 4:10 am UTC

Last updated: February 25, 2021 - 8:37 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

I have one more requirement where I need to change one particular user's password belongs to an application which is hosted outside of our network.

External application team provided information about REST API that need to used to search user and change password.

I am completely new to this and i read your blogs that this something related to REST services which we can be invoked via PLSQL.


If i am right, what are all the parameters i need to ask them to provide and also kindly share the code snippet so that i can use the same and replace it with their parameter to see if password change functionality works fine from PLSQL.


Please provide your inputs.

and we said...

So you need to call a third party REST API from PL/SQL?

You'll need to setup your database access control lists (ACLs) to allow it to make external calls first.

Then you can call the endpiont using utl_http. You just need to know the URI of the API and it's parameters.

You can find some worked examples of how to do this at:

https://technology.amis.nl/2015/05/11/invoke-a-rest-service-from-plsql-make-an-http-post-request-using-utl_http-in-oracle-database-11g-xe/
https://oracle-base.com/articles/9i/consuming-web-services-9i

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Please help

Madhan Subbiah, March 08, 2018 - 10:23 pm UTC

Below is my environmental information

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production


Below activity performed by DBA

grant execute on utl_http to schema_name
grant execute on dbms_lock to schema_name

BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => 'scim_api_dev.xml',
description => 'A test of the API DEV - ACL functionality',
principal => 'schema_name',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);
end;

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (acl => 'scim_api_dev.xml'
, principal => 'schema_name'
, is_grant => TRUE
, privilege => 'resolve');
begin
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'scim_api_dev.xml',
host => ' https://hs-identity-api.example.com/scim/v1/dev/Users',
lower_port => 1,
upper_port => 50000);
end;
commit;

Below is the code snippet, but getting an error "ORA-29273: HTTP request failed ORA-12541: TNS:no listener ORA-06512:"

declare
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
val VARCHAR2(2000);
str varchar2(1000);
begin
req := UTL_HTTP.BEGIN_REQUEST(' https://hs-identity-api.example.com/scim/v1/dev/Users' || '?' || 'filter=userName+Eq+%22madhan%22' || '&'||'attributes=id');
resp := UTL_HTTP.GET_RESPONSE(req);
LOOP
UTL_HTTP.READ_LINE(resp, val, TRUE);
DBMS_OUTPUT.PUT_LINE(val);
END LOOP;
UTL_HTTP.END_RESPONSE(resp);
EXCEPTION
WHEN UTL_HTTP.END_OF_BODY
THEN
UTL_HTTP.END_RESPONSE(resp);
END;

Please help me out.
Chris Saxon
March 14, 2018 - 1:49 pm UTC

Have you talked to your network administrators about this?

Madhan Subbiah, March 13, 2018 - 3:28 pm UTC

1. It depends on your OS. Simple method is to see if you can access the site using browser while logged in to the server. Or using a command line tool like wget.
Testing on a laptop is useless. You need to be connected to the server.
-Yes. i am able to access complete url from browser.
2. I believe this refers to environment variables. You can't set these from PL/SQL.
If you're struggling, speak with your network admin.
- Had a meeting with network admin. They are able to see a call begin made to destination from PLSQL but there is a block in firewall and hence recommended to access the destination via Proxy settings from PLSQL.

When i used below command in PLSQL, receiving an error "ORA-29273: HTTP request failed
ORA-29024: Certificate validation failure"

Command
UTL_HTTP.SET_PROXY('webproxy.mycompany.com:8080','');

Would like to know if above command is correct - Please help.

Below is the code snippet.

declare
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
val VARCHAR2(2000);
str varchar2(1000);
begin
UTL_HTTP.SET_PROXY('webproxy.mycompany.com:8080','');
req := UTL_HTTP.BEGIN_REQUEST(' https://hs-identity-api.company.com/' );
resp := UTL_HTTP.GET_RESPONSE(req);
LOOP
UTL_HTTP.READ_LINE(resp, val, TRUE);
DBMS_OUTPUT.PUT_LINE(val);
END LOOP;
UTL_HTTP.END_RESPONSE(resp);
EXCEPTION
WHEN UTL_HTTP.END_OF_BODY
THEN
UTL_HTTP.END_RESPONSE(resp);
END;

Rajeshwaran, jeyabal, November 12, 2020 - 3:47 am UTC

Team:

docs says we can have more than PK values are comma separated, but when tried it fails like this - kindly advice.

https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/20.2/aelig/developing-REST-applications.html#GUID-D3A4C99B-3090-481B-846D-45FBA578B1D2

demo@XEPDB1> spool output.txt
demo@XEPDB1> $ curl -i -X GET http://localhost:8080/ords/xe/emp/7499
HTTP/1.1 200 OK
Date: Thu, 12 Nov 2020 03:42:02 GMT
Content-Type: application/json
ETag: "u1lQygTTgvOp7RL9UMBAMeCuO/4yLrgGf3UG9aev9NNN4RZA6lqaOakGXLLOI+aIkCzNwCa0QjE/wggaVFXI2g=="
Transfer-Encoding: chunked

{"empno":7499,"ename":"ALLEN","job":"SALESMAN","mgr":7698,"hiredate":"1981-02-19T18:30:00Z","sal":1600,"comm":300,"deptno":30,"links":[{"rel":"self","href":"http://localhost:8080/ords/xe/emp/7499"},{"rel":"edit","href":"http://localhost:8080/ords/xe/emp/7499"},{"rel":"describedby","href":"http://localhost:8080/ords/xe/metadata-catalog/emp/item"},{"rel":"collection","href":"http://localhost:8080/ords/xe/emp/"}]}
demo@XEPDB1>
demo@XEPDB1> $ curl -i -X GET http://localhost:8080/ords/xe/emp/7499,7369
HTTP/1.1 500 Server Error
Content-Type: text/html
Error-Reason: error="error"; error_description*=UTF-8''An%20unexpected%20error%20with%20the%20following%20message%20occurred%3a%20null
Content-Length: 16270

<!DOCTYPE html>
<html>
<style type="text/css" media="screen">
footer,header{display:block;}

Connor McDonald
November 12, 2020 - 4:19 am UTC

Its perhaps not a great wording of the documentation.

It is not about multiple primary keys, but for when a single key requires multiple values (ie, a composite key).

To get multiple rows, you could something like

http://localhost:8089/ords/scott/emp/?q={"EMPNO": {"$or": [{"$eq":"7934"},{"$eq":"7654"}]}}

REST URL from curl command line

Rajeshwaran, Jeyabal, February 22, 2021 - 1:47 pm UTC

Team:

when tried this url from browser, it works fine.
http://localhost:8080/ords/peeps/emp/?q={"deptno":{"$lte":10}}

However when invoked from curl command line, getting this error. Kindly help us to understand, what are we missing here.
C:\Users\admin>curl -i http://localhost:8080/ords/peeps/emp/?q={"deptno":{"$lte":10}}
curl: (3) nested brace in URL position 49:
http://localhost:8080/ords/peeps/emp/?q={deptno:{$lte:10}}
                                                ^

Chris Saxon
February 22, 2021 - 4:42 pm UTC

I only have basic knowledge of curl, so I searched for

curl: (3) nested brace in URL position


and the first result was:

https://stackoverflow.com/questions/15425446/how-to-put-a-json-object-with-an-array-using-curl

Which says:

This is because curly braces {} and square brackets [] are special globbing characters in curl. To turn this globbing off, use the "-g" option.

REST URL from curl command line

Rajeshwaran, Jeyabal, February 25, 2021 - 7:34 am UTC

Thanks, tried this but still doesn't work.
C:\Users\admin>curl -i -g http://localhost:8080/ords/peeps/emp/?q={"deptno":{"$lte":10}}
HTTP/1.1 400 Bad Request
Content-Type: text/html
Content-Length: 16131

<!DOCTYPE html>
<html>
<style type="text/css" media="screen">
footer,header{display:block;}

Chris Saxon
February 25, 2021 - 8:37 am UTC

And have you tried searching for what "HTTP/1.1 400 Bad Request" means?