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.

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;

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;}

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}}
                                                ^

curl: (3) nested brace in URL position

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;}

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 ?

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.