Forum Stats

  • 3,780,569 Users
  • 2,254,410 Discussions
  • 7,879,386 Comments

Discussions

How can i get the return variables of GET method in PL/SQL procedure

2

Answers

  • Soukaina IDRISSI
    Soukaina IDRISSI Member Posts: 855 Bronze Badge
    edited Feb 16, 2018 10:16AM

    I created same WS on apex.oracle.com, with :

    RESTFul module : oracle.example.hr

    URI Template: empinfo/

    Now, I want to create a procedure getInfoEmp(idEmp<span class="pln" style="font-weight: inherit; font-style: inherit; font-size: inherit; font-family: inherit; color: #303336;"> IN NUMBER</span>) , that call this WS, and return:

    ENAME, JOB, MGR, HIREDATE, SAL, COMM and DEPTNO of the specific employee(idEmp)

    You can see it with this credentials:

    Workspace: ws_formation

    Username: asoukaina

    Password: [email protected]

    Thank you for help.

  • Pavel_p
    Pavel_p Member Posts: 2,312 Gold Trophy
    edited Feb 16, 2018 4:29PM

    Hi,

    you did not define the URL parameter in your enpoint (please, see the fixed endpoint empinfopp/{id}). Then you can invoke the service and parse the response (JSON object) with the following code

    declare    --HTTP response    l_resp_clob clob;    --returned JSON      l_values       apex_json.t_values;     l_js_empno emp.empno%type;    l_js_ename emp.ename%type;    l_js_job  emp.job%type;    --collection to store returned values    k_collection_name constant varchar2(50) := 'WS_RETURNED_JSON';begin    l_resp_clob := apex_web_service.make_rest_request(            p_url => 'https://apex.oracle.com/pls/apex/ws_formation/hr/empinfopp/' || :p1_id ,            p_http_method => 'GET'        );    if apex_web_service.g_status_code = 200 then --ok       apex_debug.message('DEBUGMSG: service was invoked successfully');    else       raise_application_error(-20000,'WS call failed with response code ' || apex_web_service.g_status_code);    end if;        --parse the returned JSON    apex_json.parse(    p_values => l_values,    p_source => l_resp_clob);      --and access the parsed values    l_js_empno := apex_json.get_number(p_values => l_values,    p_path => 'empno');      l_js_ename := apex_json.get_varchar2(p_values => l_values,    p_path => 'ename');      l_js_job := apex_json.get_varchar2(p_values => l_values,    p_path => 'job');    apex_debug.message('DEBUGMSG: EMPNO = %s, ENAME = %s, JOB = %s', l_js_empno , l_js_ename , l_js_job);  --create a collection and insert parsed values into it  apex_collection.create_or_truncate_collection(k_collection_name);  apex_collection.add_member(    p_collection_name => k_collection_name ,    p_n001 => l_js_empno,    p_c001 => l_js_ename,    p_c002 => l_js_job);        end;

    Parsed values are stored to APEX collection. Please check the Application 140152 - InvokeWS_PP.

    Regards,

    Pavel

  • Soukaina IDRISSI
    Soukaina IDRISSI Member Posts: 855 Bronze Badge
    edited Feb 17, 2018 9:27AM

    yes it is done on purpose, since , in my case the id of an employee is not passed in the url, it is declared as an input variable. As below:

    ws_apex.png

    and the Source Type is not URI.

    So to pass the variable i click on Set Bind Variable button, and set the value of variable, after that i click on test button, like that:

    ws_test+result.png

    But as you see here i cant got the result.

  • Pavel_p
    Pavel_p Member Posts: 2,312 Gold Trophy
    edited Feb 17, 2018 11:58AM

    Oh, sorry, my bad. We can pass http headers like this (please, see the page 2 in your app)

    declare    --headers     l_param_names  apex_application_global.vc_arr2;     l_param_values apex_application_global.vc_arr2;     --HTTP response    l_resp_clob clob;    --returned JSON     l_values      apex_json.t_values;    l_js_empno emp.empno%type;    l_js_ename emp.ename%type;    l_js_job  emp.job%type;    --collection to store returned values    k_collection_name constant varchar2(50) := 'WS_RETURNED_JSON';   begin          --clear headers (probably unnecessary but just to be on the safe side)   apex_web_service.g_request_headers.delete();   --now HTTP header   apex_web_service.g_request_headers(1).name := 'id';   apex_web_service.g_request_headers(1).value := :p2_id ;        l_resp_clob := apex_web_service.make_rest_request(            p_url => 'https://apex.oracle.com/pls/apex/ws_formation/hr/empinfo/'  ,            p_http_method => 'GET'        );    if apex_web_service.g_status_code = 200 then --ok      apex_debug.message('DEBUGMSG: service was invoked successfully');    else      raise_application_error(-20000,'WS call failed with response code ' || apex_web_service.g_status_code);    end if;       --parse the returned JSON   apex_json.parse(   p_values => l_values,   p_source => l_resp_clob);   --and access the parsed values   l_js_empno := apex_json.get_number(p_values => l_values,   p_path => 'empno');   l_js_ename := apex_json.get_varchar2(p_values => l_values,   p_path => 'ename');   l_js_job := apex_json.get_varchar2(p_values => l_values,   p_path => 'job');   apex_debug.message('DEBUGMSG: EMPNO = %s, ENAME = %s, JOB = %s', l_js_empno , l_js_ename , l_js_job);  --create a collection and insert parsed values into it  apex_collection.create_or_truncate_collection(k_collection_name);  apex_collection.add_member(    p_collection_name => k_collection_name ,    p_n001 => l_js_empno,    p_c001 => l_js_ename,    p_c002 => l_js_job);       end;

    I just changed a bit the service definition (from Source Type Query to Query One Row) because it's not supposed to return an array but a single JSON object.

  • Soukaina IDRISSI
    Soukaina IDRISSI Member Posts: 855 Bronze Badge
    edited Feb 24, 2018 3:08PM

    i created a new procedure named 'TEST_SERVICE' , with 1 input (http header), that insert the resut into table 'TABLE_TEST", like below:

    CREATE OR REPLACE PROCEDURE test_service(v_id varchar2) ASl_param_names   apex_application_global.vc_arr2; l_param_values  apex_application_global.vc_arr2;l_resp_clob     varchar2(500);l_values        apex_json.t_values;v_cli           varchar2(500);v_nom           varchar2(500) ;v_entite        varchar2(500);beginapex_web_service.g_request_headers(1).name := 'v_id'; apex_web_service.g_request_headers(1).value := v_id ;l_resp_clob := apex_web_service.make_rest_request(p_url => 'http://XXX.XXX.X.XXX:8081/apex/WSNAME/COMPANYWS/ENGTEST/'  , p_http_method => 'GET');    if apex_web_service.g_status_code = 200 then --ok           apex_debug.message('DEBUGMSG: service was invoked successfully');    else          raise_application_error(-20000,'WS call failed with response code ' || apex_web_service.g_status_code);    end if;--parse the returned JSON apex_json.parse( p_values => l_values, p_source => l_resp_clob,p_strict => true); --and access the parsed values v_cli := apex_json.get_varchar2(p_values => l_values, p_path => 'entite'); v_entite := apex_json.get_varchar2(p_values => l_values, p_path => 'id_client1'); v_nom := apex_json.get_varchar2(p_values => l_values, p_path => 'nom_client');insert into table_test values (v_cli,v_entite,v_nom);COMMIT;END test_service;-- To test the procedureselect * from  table_test ; -- The result => i get one insered record with NULL VALUES (Empty Fields)
  • Pavel_p
    Pavel_p Member Posts: 2,312 Gold Trophy
    edited Feb 21, 2018 5:13AM

    So you're just saving returned values into some table instead of APEX collection (I'm not sure if your problem is solved or not).

    In any case I would recommend not to explicitly commit inside of the procedure. Leave the commit/rollback decision to the calling routine. One procedure call is often just a part of some more complex process (transaction) and we want to either commit or rollback the entire thing.

    Let's suppose you execute several processes after page submit, this one is in the middle and something goes wrong in the following steps - first half is commited while the rest is rolled back, which will very likely lead to data inconsistencies. APEX implicitly commits the entire transaction if no exceptions are raised, otherwise rollback is issued.

  • Soukaina IDRISSI
    Soukaina IDRISSI Member Posts: 855 Bronze Badge
    edited Feb 21, 2018 6:48AM

    My issue is: The returned values are insered in the my custom table but with NULL values.

  • Pavel_p
    Pavel_p Member Posts: 2,312 Gold Trophy
    edited Feb 21, 2018 6:53AM

    Yes. Something is probably wrong

  • Soukaina IDRISSI
    Soukaina IDRISSI Member Posts: 855 Bronze Badge
    edited Feb 21, 2018 6:57AM

    Thank you , but where exactlly ?

  • Pavel_p
    Pavel_p Member Posts: 2,312 Gold Trophy
    edited Feb 21, 2018 7:58AM

    Haha, you tell me. How could I possibly know what is wrong? You created a new service and I have absolutely no idea how it's defined and especially what it returns, so I can only assume that JSON paths are not correct. At least try to add this

    apex_debug.message('DEBUGMSG: Returned json %s', l_resp_clob);

    after the service call in order to know what exactly is being returned. Then run the page in Debug mode and eventually share the relevant part of the debug trace.

This discussion has been closed.