Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, PoJen.

Asked: January 20, 2023 - 10:10 pm UTC

Last updated: January 25, 2023 - 2:49 am UTC

Version: 21.9

Viewed 100+ times

You Asked

Hi, I would like to know how to read data from rest API. For example, I have a splunk rest endpoint:

curl -k -u admin:oracle_4U https://localhost:8089/services/search/v2/jobs/1674187705.76/events --get -d output_mode=json

Sample: (content below is irrelevant, it just show end port works and I got a json output)
[oracle@down2 admin]$ curl -k -u admin:oracle_4U https://localhost:8089/services/search/v2/jobs/1674187705.76/events --get  -d output_mode=json
{"preview":false,"init_offset":0,"messages":[],"fields":[{"name":"_bkt"},{"name":"_cd"},{"name":"_indextime"},{"name":"_raw"},{"name":"_serial"},{"name":"_si"},{"name":"_sourcetype"},{"name":"_subsecond"},{"name":"_time"},{"name":"host"},{"name":"index"},{"name":"linecount"},{"name":"source"},{"name":"sourcetype"},{"name":"splunk_server"}],"results":[{"_bkt":"main~0~2DA1E607-D0B1-4118-8518-6599FD111AAC","_cd":"0:23","_indextime":"1669941506","_raw":"12/2/2022 11:45:00.000 AM,6,world","_serial":"0","_si":["down2","main"],"_sourcetype":"csv","_subsecond":".000","_time":"2022-12-02T11:45:00.000-05:00","host":"down2","index":"main","linecount":"1","source":"xx.csv","sourcetype":"csv","splunk_server":"down2"},{"_bkt":"main~0~2DA1E607-D0B1-4118-8518-6599FD111AAC","_cd":"0:17","_indextime":"1669941506","_raw":"12/2/2022 11:35:00.000 AM,4,bar","_serial":"1","_si":["down2","main"],"_sourcetype":"csv","_subsecond":".000","_time":"2022-12-02T11:35:00.000-05:00","host":"down2","index":"main","linecount":"1","source":"xx.csv","sourcetype":"csv","splunk_server":"down2"},{"_bkt":"main~0~2DA1E607-D0B1-4118-8518-6599FD111AAC","_cd":"0:20","_indextime":"1669941506","_raw":"12/2/2022 11:15:00.000 AM,5,hello","_serial":"2","_si":["down2","main"],"_sourcetype":"csv","_subsecond":".000","_time":"2022-12-02T11:15:00.000-05:00","host":"down2","index":"main","linecount":"1","source":"xx.csv","sourcetype":"csv","splunk_server":"down2"},{"_bkt":"main~0~2DA1E607-D0B1-4118-8518-6599FD111AAC","_cd":"0:14","_indextime":"1669941506","_raw":"12/2/2022 11:15:00.000 AM,3,foo","_serial":"3","_si":["down2","main"],"_sourcetype":"csv","_subsecond":".000","_time":"2022-12-02T11:15:00.000-05:00","host":"down2","index":"main","linecount":"1","source":"xx.csv","sourcetype":"csv","splunk_server":"down2"},{"_bkt":"main~0~2DA1E607-D0B1-4118-8518-6599FD111AAC","_cd":"0:11","_indextime":"1669941506","_raw":"12/1/2022 11:25:00.000 
AM,2,jen","_serial":"4","_si":["down2","main"],"_sourcetype":"csv","_subsecond":".000","_time":"2022-12-01T11:25:00.000-05:00","host":"down2","index":"main","linecount":"1","source":"xx.csv","sourcetype":"csv","splunk_server":"down2"},{"_bkt":"main~0~2DA1E607-D0B1-4118-8518-6599FD111AAC","_cd":"0:8","_indextime":"1669941506","_raw":"12/1/2022 11:15:00.000 AM,1,po","_serial":"5","_si":["down2","main"],"_sourcetype":"csv","_subsecond":".000","_time":"2022-12-01T11:15:00.000-05:00","host":"down2","index":"main","linecount":"1","source":"xx.csv","sourcetype":"csv","splunk_server":"down2"}], "highlighted":{}}[oracle@down2 admin]$
[oracle@down2 admin]$


I leaned that we have utl_http that can perform this type of task, but is it not clear to me:

1.) Do I need the wallet to import the self-signed certificate to get the above https to work.
2.) How do I pass the extra argunment, i.e., " --get -d output_mode=json" using utl_http.

Thank you.

and Connor said...

I would never use UTL_HTTP to go after a REST service, because that is like re-inventing the wheel. If you install APEX (even if you never use it) you get access to the APEX_WEB_SERVICE package which can do all of the heavy lifting for you.

One the best coverages I've found of this topic is Carten's article a while back in ORAWORLD magazine.

Here's a link

https://www.oraworld.org/fileadmin/documents/24-ORAWORLD.pdf

which covers the basics plus has numerous code examples.

Carsten also has numerous web service handling examples at the APEX blog

https://blogs.oracle.com/apex/search.html?contentType=Blog-Post&default=apex_web_service*

Rating

  (2 ratings)

Comments

A reader, January 23, 2023 - 10:07 pm UTC

Thank you Conner, I use APEX/ORDS to host restAPI but I didn't know I can make rest call with APEX. I successfully making simple rest call with apex_web_service.make_rest_request_b, i.e.,

select apex_web_service.make_rest_request_b (p_url => 'http://localhost:8089/services/search/v2/jobs/1674216456.2/events'
                                                      ,p_http_method => 'GET'
                                                      ,p_username             =>'admin'
                                                      ,p_password             =>'oracle_4U'
                                                      ) from dual;


The above works, but I still have difficulty passing the extra argunment, i.e.,

curl -u admin:oracle_4U http://localhost:8089/services/search/v2/jobs/1674216456.2/events --get  -d output_mode=json

(still have hard time append "--get -d output_mode=json", from the test, the make_rest_request_b seems ignore anything after white space)

From the doc it seems it currently doesn't support such request. Am I missing anything?

https://docs.oracle.com/en/database/oracle/application-express/21.1/aeapi/MAKE_REST_REQUEST_B-Function.html#GUID-1FCD717A-A515-4394-832C-980D2990564F

Thanks again for your prompt response.
Connor McDonald
January 24, 2023 - 7:01 am UTC

localhost:8089/services/search/v2/jobs/1674216456.2/events -d output_mode=json

should by synonymous with

localhost:8089/services/search/v2/jobs/1674216456.2/events?output_mode=json

A reader, January 24, 2023 - 1:49 pm UTC

Thank you Conner, I got the output expected.

SQL> with po as (select apex_web_service.make_rest_request_b (p_url         => 'http://localhost:8089/services/search/jobs/1674216456.2/results?output_mode=json '
  2                                                        ,p_http_method => 'GET'
  3                                                        ,p_username             =>'admin'
  4                                                        ,p_password             =>'oracle_4U'
  5                                                        ) xx from dual
  6                                                        )
  7                                                        select to_clob(xx) from po;

TO_CLOB(XX)                                                                     
--------------------------------------------------------------------------------
{"messages":[{"type":"FATAL","text":"Unknown sid."}]} 


Nice formatted Json. :)
Connor McDonald
January 25, 2023 - 2:49 am UTC

Glad it worked out

More to Explore

APEX

Keep your APEX skills fresh by attending their regular Office Hours sessions.