/*
Apex 5.0.3
Oracle XE 11.2g
*/
My API request return respose file (api.txt) content file with json code like: {"request_id":"2fb22a1cb04945aab51f361d0a34790a","status":"0"}
My Request:
I want read value of request_id only
And return this request_id value as out function .. How can i do that ..?
My Function is:
create or replace function
iVerify(p_api_key VARCHAR2,
p_api_secret VARCHAR2,
p_mobile VARCHAR2,
p_msg VARCHAR2)
return varchar2
is
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
out_value VARCHAR2(1024);
R_RESPONSE VARCHAR2(1024);
l_values apex_json.t_values;
BEGIN
req := UTL_HTTP.BEGIN_REQUEST('http://smavip-api.nexmo.maxapex.ws/verify/json?'||
'api_key=' || p_api_key ||
'&api_secret=' || p_api_secret ||
'&number=' || p_mobile ||
'&brand=' || p_msg );
--UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0');
resp := UTL_HTTP.GET_RESPONSE(req);
--UTL_HTTP.read_text(resp, R_RESPONSE);
loop
UTL_HTTP.READ_LINE(resp, R_RESPONSE, TRUE);
apex_json.parse(
p_values => l_values,
p_source => R_RESPONSE);
out_value := out_value || apex_json.get_varchar2(
p_values => l_values,
p_path => 'request_id' );
end loop;
UTL_HTTP.END_RESPONSE(resp);
if out_value is not null
then return out_value;
else return 'No data returned!';
end if;
EXCEPTION
WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE(resp);
END;
I call it by :
select iVerify(p_api_key => '45501eafe',
p_api_secret => 'e7f50dljn197e054c',
p_mobile => '201009920170',
p_msg => 'Worked..') as xxxx from dual;
Error returned:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "IVERIFY", line 44
06503. 00000 - "PL/SQL: Function returned without value"
*Cause: A call to PL/SQL function completed, but no RETURN statement was
executed.
*Action: Rewrite PL/SQL function, making sure that it always returns
a value of a proper type.
Please Help me
Thanks..
@"Mahmoud_Rabie"