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: June 14, 2021 - 11:48 am UTC

Version: 12c

Viewed 50K+ 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

  (7 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?

manual creating of RESTFul Services using SQL/PLSQL

Rajeshwaran Jeyabal, June 14, 2021 - 11:13 am UTC

Team:

was reading this section from docs

https://docs.oracle.com/en/database/oracleoracle-rest-data-services/21.1/aelig/developing-REST-applications.html#GUID-4B53E1A7-ABF3-4A1D-9B1E-98D321E53F16

and got a REST module created on my local PC running 18cXE

http://localhost:8080/ords/demo/emp_module/emp_pattern
which in turn invokes this standalone procedure

demo@XEPDB1> create or replace procedure my_proc( p_empno in number,
  2     p_sal in number,
  3     p_sal_diff out number )
  4  as
  5     l_old_sal number;
  6  begin
  7     select sal into l_old_sal
  8     from emp
  9     where empno = p_empno;
 10
 11     update emp
 12     set sal = p_sal
 13     where empno = p_empno ;
 14
 15     p_sal_diff := abs(l_old_sal - p_sal);
 16  end;
 17  /

Procedure created.


but when tried to invoke the REST Endpoints from CURL got this error.

D:\>curl -i -H "Content-Type: application/json" -X PUT -d "{\"p_empno\" : 7499, \"p_sal\" : 1000}" http://localhost:8080/ords/demo/emp_module/emp_pattern
HTTP/1.1 555 User Defined Resource Error
Content-Type: application/problem+json
Content-Length: 714


{
    "code": "UserDefinedResourceError",
    "title": "User Defined Resource Error",
    "message": "The request could not be processed due to an error in a user defined resource",
    "o:errorCode": "ORDS-25001",
    "cause": "An error occurred when evaluating the SQL statement associated with this resource. 
  SQL Error Code 6550, Error Message: ORA-06550: line 4, column 1:\nPLS-00103: 
  Encountered the symbol \"/\" The symbol \"/\" was ignored.\n",
    "action": "Ask the user defined resource author to check the SQL statement is correctly formed and executes without error",
    "type": "tag:oracle.com,2020:error/UserDefinedResourceError",
    "instance": "tag:oracle.com,2020:ecid/-vUzQ2DeEAhYgZlM0CuvQw"
}


Could you please help us to understand what was missing here and returning this error ?

manual creating of RESTFul Services using SQL/PLSQL

Rajeshwaran Jeyabal, June 14, 2021 - 11:24 am UTC

Sorry please ignore the above request.

within the REST handlers got the PL/SQL call like this

begin
    my_proc(:p_empno,:p_sal,:p_sal_diff);
end;
/


the error log from the webserver shows this

2021-06-14T11:05:36.702Z INFO        <-vUzQ2DeEAhYgZlM0CuvQw> PUT localhost /ords/demo/emp_module/emp_pattern 555
ResourceGeneratorEvaluationException [statusCode=555, logLevel=INFO, errorCode=ORDS-25001: The request could not be processed due to an error in a user defined resource C
ause: An error occurred when evaluating the SQL statement associated with this resource. SQL Error Code 6550, Error Message: ORA-06550: line 4, column 1:
PLS-00103: Encountered the symbol "/" The symbol "/" was ignored.


so replaced the plsql call like this (without the blackslash in the last)
begin
    my_proc(:p_empno,:p_sal,:p_sal_diff);
end;


and post that invoking REST Endpoints using CURL got sucess.

D:\>curl -i -H "Content-Type: application/json" -X PUT -d "{\"p_empno\" : 7499, \"p_sal\" : 1100}" http://localhost:8080/ords/demo/emp_module/emp_pattern
HTTP/1.1 200 OK
Date: Mon, 14 Jun 2021 11:17:30 GMT
Content-Type: application/json
Transfer-Encoding: chunked

{"SalDiff":100}
D:\>


Thanks again.
Chris Saxon
June 14, 2021 - 11:48 am UTC

Glad you got it sorted