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 1000+ 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)

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

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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

APEX

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