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.

We have been using the method above of HTTP from the DB for many years; very successfully and for several apps. However, the architect is demanding we move away from that and use GoldenGate to call Kafka to call our intended app on a server.

GG has its own issues (latency, etc..) with this install and it just puts a lot of potential points of failure.
Is there a better way?

I hope I am not hijacking this thread, if so Sorry.

Team,

got a ORDS for my local database and it works fine

demo@PDB1> $ curl -i -H "Content-Type: application/json" -X GET "http://localhost:8080/ords/demo/emp/7499"
HTTP/1.1 200 OK
Content-Type: application/json
ETag: "aWDriUtpnlD9PUd7wBZx8OK5Kx64nKyYfQcN0wZIdar5yG1Tg+StiGjqMZ1vv1v9mkZpdEl917goRqGoqdGApg=="
Transfer-Encoding: chunked

{"empno":7499,"ename":"ALLEN","job":"SALESMAN","mgr":7698,"hiredate":"1981-02-20T00:00:00Z","sal":1600,"comm":300,"deptno":30,"x":55,"links":[{"rel":"self","href":"http://localhost:8080/ords/demo/emp/7499"},{"rel":"edit","href":"http://localhost:8080/ords/demo/emp/7499"},{"rel":"describedby","href":"http://localhost:8080/ords/demo/metadata-catalog/emp/item"},{"rel":"collection","href":"http://localhost:8080/ords/demo/emp/"}]}
demo@PDB1>

got a Restfull service implemented like this

-- Generated by Oracle SQL Developer REST Data Services 22.2.1.234.1810
-- Exported REST Definitions from ORDS Schema Version 22.3.1.r2901647
-- Schema: DEMO   Date: Mon Dec 12 15:02:44 IST 2022
--
BEGIN
  ORDS.ENABLE_SCHEMA(
      p_enabled             => TRUE,
      p_schema              => 'DEMO',
      p_url_mapping_type    => 'BASE_PATH',
      p_url_mapping_pattern => 'demo',
      p_auto_rest_auth      => FALSE);    

  ORDS.DEFINE_MODULE(
      p_module_name    => 'demo',
      p_base_path      => '/demo/',
      p_items_per_page =>  25,
      p_status         => 'NOT_PUBLISHED',
      p_comments       => NULL);      
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'demo',
      p_pattern        => 'test',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'demo',
      p_pattern        => 'test',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_items_per_page =>  0,
      p_mimes_allowed  => '',
      p_comments       => 'my_demo_post',
      p_source         => 
'declare
 l_json blob;
begin 
 l_json := :body;

 insert into PurchaseOrder(pono,Requestor,CostCenter,AddressStreet,
  AddressCity,AddressState,AddressZip,AddressCountry )
 select *
 from json_table( l_json format json ,''$''
  columns(
   PONumber number path ''$.PONumber'',
   Requestor varchar2(20) path ''$.Requestor'',
   CostCenter varchar2(10) path ''$.CostCenter'',
   Address_street varchar2(30) path ''$.Address.street'',
   Address_city varchar2(30) path ''$.Address.city'',
   Address_state varchar2(30) path ''$.Address.state'',
   Address_zipCode varchar2(30) path ''$.Address.zipCode'',
   Address_country varchar2(30) path ''$.Address.country'' ) );

 insert into lineitem( PONo,ItemNumber,PartDescription,PartUnitPrice,
  PartUPCCODE,Quantity ) 
 select *
 from json_table( l_json format json ,''$''
  columns(
   PONumber number path ''$.PONumber'',
   nested path ''$.LineItems[*]''
   columns(
    ItemNumber number path ''$.ItemNumber'',
    part_descr varchar2(30) path ''$.Part.Description'',
    part_up number(10,2) path ''$.Part.UnitPrice'',
    part_upcode number path ''$.Part.UPCCode'',
    qty number path ''$.Quantity'') 
   ) ) ;
 commit;
end;'
      );


  COMMIT; 
END;

when i tried to run it from CURL it got errored like this

demo@PDB1> $ curl -i -H "Content-Type: application/json" -X POST -d @po1.json "http://localhost:8080/ords/demo/demo/test/"
HTTP/1.1 404 Not Found
Content-Type: application/problem+json
Content-Length: 172


{
    "code": "NotFound",
    "message": "Not Found",
    "type": "tag:oracle.com,2020:error/NotFound",
    "instance": "tag:oracle.com,2020:ecid/QaWbxy4Tn5CvYuN-jIqt0w"
}
demo@PDB1> $ type po1.json
{
  "PONumber": 1608,
  "Requestor": "Alexis Bull",
  "CostCenter": "A50",
  "Address": {
    "street": "200 Sporting Green",
    "city": "South San Francisco",
    "state": "CA",
    "zipCode": 99236,
    "country": "United States of America"
  },
  "LineItems": [
    {
      "ItemNumber": 1,
      "Part": {
        "Description": "One Magic Christmas",
        "UnitPrice": 19.95,
        "UPCCode": 1313109289
      },
      "Quantity": 9
    },
    {
      "ItemNumber": 2,
      "Part": {
        "Description": "Lethal Weapon",
        "UnitPrice": 19.95,
        "UPCCode": 8539162892
      },
      "Quantity": 5
    }
  ]
}

demo@PDB1>

Can you please help me to understand what i am missing here?

Team,

please ignore the above request, was able to identify and fix the issue

 ORDS.DEFINE_MODULE(
      p_module_name    => 'demo',
      p_base_path      => '/demo/',
      p_items_per_page =>  25,
      p_status         => 'NOT_PUBLISHED', <<=== that was the problem for me
      p_comments       => NULL); 

once it got published, it worked fine.

demo@PDB1> select * from PurchaseOrder;

no rows selected
demo@PDB1> select * from lineitem;

no rows selected
demo@PDB1> $ curl -i -H "Content-Type: application/json" -X POST -d @po1.json "http://localhost:8080/ords/demo/mime/jsontab"
HTTP/1.1 200 OK
Transfer-Encoding: chunked


demo@PDB1> select * from PurchaseOrder;

   PONO REQUESTOR      COSTCENTER    ADDRESSSTREET         ADDRESSCITY            ADDRESSSTATE    ADDRESSZIP    ADDRESSCOUNTRY
_______ ______________ _____________ _____________________ ______________________ _______________ _____________ ___________________________
   1608 Alexis Bull    A50           200 Sporting Green    South San Francisco    CA              99236         United States of America

demo@PDB1> select * from lineitem;

   PONO    ITEMNUMBER PARTDESCRIPTION           PARTUNITPRICE    PARTUPCCODE    QUANTITY
_______ _____________ ______________________ ________________ ______________ ___________
   1608             1 One Magic Christmas                  20     1313109289           9
   1608             2 Lethal Weapon                        20     8539162892           5

demo@PDB1>

Team,

I was reading this from docs

https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/23.2/orddg/developing-REST-applications.html#GUID-4A7F4425-61DF-4290-AED0-05DC2EF77158

when i apply the query filters to the http url's it doesn't filter the data, can you help me to understand what i am missing here?

$ curl http://localhost:8080/ords/peeps/new_test/emp/SALESMAN/30
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   850    0   850    0     0  30744      0 --:--:-- --:--:-- --:--:-- 31481{"items":[{"empno":7499,"ename":"ALLEN","job":"SALESMAN","mgr":7698,"hiredate":"1981-02-20T00:00:00Z","sal":1600,"comm":300,"deptno":30},{"empno":7521,"ename":"WARD","job":"SALESMAN","mgr":7698,"hiredate":"1981-02-22T00:00:00Z","sal":1250,"comm":500,"deptno":30},{"empno":7654,"ename":"MARTIN","job":"SALESMAN","mgr":7698,"hiredate":"1981-09-28T00:00:00Z","sal":1250,"comm":1400,"deptno":30},{"empno":7844,"ename":"TURNER","job":"SALESMAN","mgr":7698,"hiredate":"1981-09-08T00:00:00Z","sal":1500,"comm":0,"deptno":30}],"hasMore":false,"limit":25,"offset":0,"count":4,"links":[{"rel":"self","href":"http://localhost:8080/ords/peeps/new_test/emp/SALESMAN/30"},{"rel":"describedby","href":"http://localhost:8080/ords/peeps/metadata-catalog/new_test/emp/SALESMAN/item"},{"rel":"first","href":"http://localhost:8080/ords/peeps/new_test/emp/SALESMAN/30"}]}

Rajeshwaran@rajeyaba-3WH3DK3 MINGW64 
$ curl http://localhost:8080/ords/peeps/new_test/emp/SALESMAN/30?empno=7499
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   872    0   872    0     0  17851      0 --:--:-- --:--:-- --:--:-- 18166{"items":[{"empno":7499,"ename":"ALLEN","job":"SALESMAN","mgr":7698,"hiredate":"1981-02-20T00:00:00Z","sal":1600,"comm":300,"deptno":30},{"empno":7521,"ename":"WARD","job":"SALESMAN","mgr":7698,"hiredate":"1981-02-22T00:00:00Z","sal":1250,"comm":500,"deptno":30},{"empno":7654,"ename":"MARTIN","job":"SALESMAN","mgr":7698,"hiredate":"1981-09-28T00:00:00Z","sal":1250,"comm":1400,"deptno":30},{"empno":7844,"ename":"TURNER","job":"SALESMAN","mgr":7698,"hiredate":"1981-09-08T00:00:00Z","sal":1500,"comm":0,"deptno":30}],"hasMore":false,"limit":25,"offset":0,"count":4,"links":[{"rel":"self","href":"http://localhost:8080/ords/peeps/new_test/emp/SALESMAN/30?empno=7499"},{"rel":"describedby","href":"http://localhost:8080/ords/peeps/metadata-catalog/new_test/emp/SALESMAN/item"},{"rel":"first","href":"http://localhost:8080/ords/peeps/new_test/emp/SALESMAN/30?empno=7499"}]}

Rajeshwaran@rajeyaba-3WH3DK3 MINGW64 
$ curl http://localhost:8080/ords/peeps/new_test/emp/SALESMAN/30?empno=7499&&mgr=7698
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   872    0   872    0     0  16664      0 --:--:-- --:--:-- --:--:-- 16769{"items":[{"empno":7499,"ename":"ALLEN","job":"SALESMAN","mgr":7698,"hiredate":"1981-02-20T00:00:00Z","sal":1600,"comm":300,"deptno":30},{"empno":7521,"ename":"WARD","job":"SALESMAN","mgr":7698,"hiredate":"1981-02-22T00:00:00Z","sal":1250,"comm":500,"deptno":30},{"empno":7654,"ename":"MARTIN","job":"SALESMAN","mgr":7698,"hiredate":"1981-09-28T00:00:00Z","sal":1250,"comm":1400,"deptno":30},{"empno":7844,"ename":"TURNER","job":"SALESMAN","mgr":7698,"hiredate":"1981-09-08T00:00:00Z","sal":1500,"comm":0,"deptno":30}],"hasMore":false,"limit":25,"offset":0,"count":4,"links":[{"rel":"self","href":"http://localhost:8080/ords/peeps/new_test/emp/SALESMAN/30?empno=7499"},{"rel":"describedby","href":"http://localhost:8080/ords/peeps/metadata-catalog/new_test/emp/SALESMAN/item"},{"rel":"first","href":"http://localhost:8080/ords/peeps/new_test/emp/SALESMAN/30?empno=7499"}]}

Rajeshwaran@rajeyaba-3WH3DK3 MINGW64