ORDS, SODA & JSON in the Database

i have a problem in creating rest data source for get request of restful api in oracle apex ver 24.1.7 using oauth2 web credential

in discovery step gives me an error invalid character “<”, while i can successfully call it using

apex_web_service.make_rest_request

my api based on plsql is as following

url endpoint https://rac4-vip.gupco.net:8443/ords/cater_dev/cater/transget/

using querystring parameters

the binding parameters are

empno string

emptype string

tdate string

plsql code

declare
--arb_name housing_view.arb_name%type ;
meal_record daily_trans%rowtype ;
l_cursor SYS_REFCURSOR ;
l_date date ;
j_meal_kind varchar2(50) ;
j_meal_type varchar2(25) ;
j_meal_exception varchar2(50) ;
j_time varchar2(40) ;
j_payroll varchar2(10) ;
count_rec number ;
vempno varchar2(5);
vemptype varchar2(1) ;
vtdate varchar2(10);
l_json clob ;
TYPE emp_detail_type IS RECORD
(
arb_name housing_view.arb_name%type,
hour number(3,0),
min number(3,0),
meal_kind varchar2(1),
meal_type varchar2(3),
except_meal varchar2(1)
);
emp_detail_rec emp_detail_type ;

begin

    -- Open a cursor to fetch the transactions for the given employee  

--vempno := :empno;
--vemptype := :emptype ;
--vtdate := :tdate ;
-- vtdate := nvl(owa_util.get_cgi_env('tdate'),'2025-04-27') ;
-- vempno := nvl(owa_util.get_cgi_env('empno'),'09005') ;
-- vemptype := nvl(owa_util.get_cgi_env('emptype'),'G') ;
-- vtdate := nvl(:tdate,'2025-04-27') ;
-- vempno := nvl(:empno,'09005') ;
-- vemptype := nvl(:emptype,'G') ;

 l\_date := to\_date(:tdate,'yyyy-MM-dd')  ;  

OPEN l_cursor FOR
SELECT arb_name,hour,min,meal_kind,meal_type,except_meal
FROM daily_trans,housing_view where
g_payroll = nvl(:empno,'09005') and type = nvl(:emptype,'G') and to_char(meal_date,'yyyy-MM-dd') = nvl(:tdate,'2025-04-27') and housing_view.payno = g_payroll and daily_trans.type = housing_view.emp_type ;

-- Loop through the cursor and build the JSON response

   count\_rec := 0  ;   

LOOP
FETCH l_cursor INTO emp_detail_rec;
EXIT WHEN l_cursor%NOTFOUND;
count_rec := count_rec+1 ;
if emp_detail_rec.meal_kind = 'L' THEN
j_meal_kind :='غذاء';
END IF ;
if emp_detail_rec.meal_kind = 'T' THEN
j_meal_kind := 'وجبة إضافية';
END IF ;
if emp_detail_rec.meal_kind = 'B' THEN
j_meal_kind := 'إفطار';
END IF ;
if emp_detail_rec.meal_kind = 'D' THEN
j_meal_kind := 'عشاء';
END IF ;
if emp_detail_rec.meal_type = 'HOT' then
j_meal_type := 'ساخنة';
end if ;
if emp_detail_rec.meal_type = 'DRY' then
j_meal_type := 'جافة';
end if ;
if emp_detail_rec.except_meal <> 'Y' then
j_meal_exception := 'عادية' ;
end if ;
if emp_detail_rec.except_meal = 'Y' then
j_meal_exception := 'إستثنائية' ;
end if ;
j_time := ''||emp_detail_rec.hour||':'||emp_detail_rec.min ;
if count_rec = 1 then
APEX_JSON.initialize_output;
-- APEX_WEB_SERVICE.g_status_code :=200 ;
owa_util.status_line( 200,
'Fetching required records',
TRUE
) ;
owa_util.mime_header('application/json',FALSE) ;
owa_util.http_header_close ;

     --SET CONTENT-TYpe header to application/json; charset = UTF-8   
   --  owa\_util.mime\_header('application/json;charset= UTF-8',FALSE) ;  
    -- htp.p('Access-Control-Allow-Origin:\*');  
    -- owa\_util.http\_header\_close ;  
    -- APEX\_JSON.open\_object;  
       APEX\_JSON.open\_object ;  
       APEX\_JSON.open\_array('items');   
   end if ;    
    APEX\_JSON.open\_object;  
    j\_payroll  := :emptype||:empno ;  
    APEX\_JSON.WRITE('EMPID', j\_payroll);  
    APEX\_JSON.WRITE('Name', emp\_detail\_rec.arb\_name );  
    APEX\_JSON.WRITE('Meal\_Date',TO\_CHAR(l\_date,'DD/MM/YYYY')) ;  
    APEX\_JSON.WRITE('Meal', j\_meal\_kind ) ;  
    APEX\_JSON.WRITE('Meal\_Type', j\_meal\_type ) ;  
    APEX\_JSON.WRITE('Issued\_Time',  j\_time ) ;  
    APEX\_JSON.WRITE('Meal\_Status',   j\_meal\_exception ) ;  
    APEX\_JSON.close\_object;    

END LOOP ;
if count_rec > 0 then

     APEX\_JSON.close\_all;  
     APEX\_JSON.initialize\_clob\_output ;  
     l\_json := apex\_json.get\_clob\_output ;  
     apex\_json.free\_output ;  
     htp.p(l\_json) ;  
     :status\_code := 200 ;  
         
     else  
           
         owa\_util.status\_line(204,  
          'No Meals foubd',  
          TRUE  
          ) ;  
         owa\_util.mime\_header('application/json',FALSE) ;         
         owa\_util.http\_header\_close ;  
         APEX\_JSON.initialize\_clob\_output ;  
       --  APEX\_WEB\_SERVICE.g\_status\_code :=204 ;  
                   
      --  owa\_util.mime\_header('application/json;charset= UTF-8',FALSE) ;  
       -- htp.p('Access-Control-Allow-Origin:\*');  
       -- owa\_util.http\_header\_close ;  
         apex\_json.open\_object;        -- {  
         apex\_json.write('status', 204); --   "a":1   
         apex\_json.write('message', 'Employee has not meals in this date');  
         apex\_json.write('empno',:empno);  
         apex\_json.write('emptype', '\*\*\*\*' );  
         apex\_json.write('tdate','\*\*\*' );  
         apex\_json.close\_object;    
         owa\_util.status\_line(204,  
          'No Meals foubd',  
          TRUE   
          ) ;  
         owa\_util.mime\_header('application/json',FALSE) ;   
         l\_json := apex\_json.get\_clob\_output ;  
         htp.p(l\_json) ;  
         :status\_code := 204 ;  
     end if ;      

     -- Close the cursor  
    CLOSE l\_cursor;

exception
when NO_DATA_FOUND THEN
apex_json.close_all;

--APEX\_WEB\_SERVICE.g\_status\_code :=204 ;  
         owa\_util.status\_line( 204,  
         'No Meals found',  
          TRUE   
          ) ;  
 owa\_util.mime\_header('application/json',FALSE) ;         
 owa\_util.http\_header\_close ;  
 APEX\_JSON.initialize\_clob\_output;  

-- owa_util.mime_header('application/json;charset= UTF-8',FALSE) ;
-- htp.p('Access-Control-Allow-Origin:*');
-- owa_util.http_header_close ;
apex_json.open_object; -- {
apex_json.write('status', 204); -- "a":1
apex_json.write('message', 'Employee has not meals in this date');
apex_json.write('empno','****');
apex_json.write('emptype', '*' );
apex_json.write('tdate','****' );
apex_json.close_object;
l_json := apex_json.get_clob_output ;
apex_json.free_output ;
htp.p(l_json) ;
:status_code := 204;
when others then

IF SQLCODE = -1031 THEN   
       apex\_json.close\_all;   
     --  APEX\_WEB\_SERVICE.g\_status\_code :=403 ;  
       APEX\_JSON.initialize\_clob\_output;  
       APEX\_JSON.open\_object;  
       APEX\_JSON.write('status', 403);  
       APEX\_JSON.write('message', 'You are not authorithed ');  
       APEX\_JSON.close\_object;  
       l\_json := apex\_json.get\_clob\_output ;  
       apex\_json.free\_output ;  
       htp.p(l\_json) ;  
       :status\_code := 204 ;  
     else   
         APEX\_JSON.initialize\_clob\_output;  
       --  APEX\_WEB\_SERVICE.g\_status\_code   := 400;   
         APEX\_JSON.open\_object;  
         APEX\_JSON.write('status', 400);  
         APEX\_JSON.write('message',sqlerrm );  
         APEX\_JSON.close\_object;  
         l\_json := apex\_json.get\_clob\_output ;  
         apex\_json.free\_output ;  
         htp.p(l\_json) ;  
         :status\_code := 200 ;  
     end if ;  

end ;