Skip to Main Content
Forums

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to call API key value pair ?

anju abhiFeb 17 2025

password : Developer123

workspace : yearlate

Project name :API Call

Sir please check using the above credentials .. and tell me what the issue i am doing as soon as possible

Sir here i have to display the name according to the code .. what should i do ? i can able to call the api … but i need the value of Sname from the API according to this Code which i am giving in the 1st field ..

DECLARE
l_data CLOB;
sName VARCHAR2 (50);
l_url VARCHAR2 (1000) :=
'http://webservices.oorsprong.org/websamples.countryinfo/CountryInfoService.wso/ListOfContinentsByCode/JSON/debug?''';
BEGIN
l_data := apex_web_service.make_rest_request(
p_url => l_url,
p_http_method => 'GET'
);
WITH json_tab AS
(
SELECT l_data FROM dual
)
SELECT sName
INTO :P2_NAME
FROM json_tab;

EXCEPTION WHEN no_data_found THEN
:P2_NAME := '';

END;

If i give a code like this … it won't displaying the value of sName … what should i do to get the value of sName in the :P2_name field according to sCode…

Comments

PetarSimic

Hi, your variable sName is always empty, since you are not using it anywhere. Instaed try to select l_data variable.

anju abhi

thank you sir…..How to call a particular Variable from a json input ?

DECLARE
l_data CLOB;
sName VARCHAR2 (50);
l_url VARCHAR2 (1000) :=
'http://webservices.oorsprong.org/websamples.countryinfo/CountryInfoService.wso/ListOfContinentsByCode/JSON/debug?''';
BEGIN
l_data := apex_web_service.make_rest_request(
p_url => l_url,
p_http_method => 'GET'
);
WITH json_tab AS
(
SELECT l_data FROM dual
)
SELECT sName
INTO :P2_NAME
FROM json_tab;

EXCEPTION WHEN no_data_found THEN
:P2_NAME := '';

END;

Here Instead of l _data i tried with sName then also its not working …

jariola

Your API call seems to return array. Here is example query to get array values.

with json_data as(
 select
   treat( '[{"sCode":"AF","sName":"Africa"},{"sCode":"AM","sName":"The Americas"},{"sCode":"AN","sName":"Antarctica"},{"sCode":"AS","sName":"Asia"},{"sCode":"EU","sName":"Europe"},{"sCode":"OC","sName":"Ocenania"}]' as json) as doc
 from dual
)
select
  j.doc.sCode
, j.doc.sName
from json_data j
;

Another example, if you want array data to rows. But you can't use this select into your item, because it returns multiple rows

with json_data as(
 select
   '[{"sCode":"AF","sName":"Africa"},{"sCode":"AM","sName":"The Americas"},{"sCode":"AN","sName":"Antarctica"},{"sCode":"AS","sName":"Asia"},{"sCode":"EU","sName":"Europe"},{"sCode":"OC","sName":"Ocenania"}]' as doc
 from dual
)
select
  t.sCode
, t.sName
from json_data j,
 json_table( j.doc, '$[*]' 
   columns
     sCode,
     sName
 ) t
;
anju abhi

Thank you sir got it …

1 - 4

Post Details

Added on Feb 17 2025
4 comments
76 views