Forum Stats

  • 3,770,714 Users
  • 2,253,158 Discussions
  • 7,875,556 Comments

Discussions

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

Soukaina IDRISSI
Soukaina IDRISSI Member Posts: 848 Blue Ribbon
edited Feb 24, 2018 3:08PM in APEX Discussions

Hi everyone,

I created a PL/SQL procedure named getInfoUser(id<span class="pln" style="font-style: inherit; font-size: inherit; font-family: inherit; color: #303336;"> IN NUMBER</span><span class="kwd" style="font-style: inherit; font-size: inherit; font-family: inherit; color: #101094;"></span>), that call a GET method( which return 10 variables) of one particular RESTful Web Service, and i want to know how can i do, to return this 10 variables in my own procedure.

Thank you in advance for help.

Tagged:
Soukaina IDRISSIPavel_p

Best Answer

  • Pavel_p
    Pavel_p Member Posts: 2,311 Gold Trophy
    edited Feb 21, 2018 9:53AM Accepted Answer

    I don't even see it in threaded view, so let's start from the beginning.

    Your current service is something different than the previous one. While the previous service returned a single JSON object, this modified one returns an array of items, so paths are different and also you need to loop over array elements. Let's suppose you have a service defined like

    select * from dept

    that returns an array of JSONs, you could process it this way

    declare  l_resp_clob  clob := '  {    "next": {        "$ref": "http://server_addr/apex/testing/emprest/getAllDepts?page=1"    },    "items": [{            "deptno": 10,            "dname": "ACCOUNTING",            "loc": "NEW YORK"        }, {            "deptno": 20,            "dname": "RESEARCH",            "loc": "DALLAS"        }, {            "deptno": 30,            "dname": "SALES",            "loc": "CHICAGO"        }, {            "deptno": 40,            "dname": "OPERATIONS",            "loc": "BOSTON"        }    ]}';  l_values    apex_json.t_values;  l_js_deptno  dept.deptno % type;  l_js_dname  dept.dname % type;  l_js_loc    dept.loc % type;  l_item_count number;begin  --parse the returned JSON     apex_json.parse(  p_values => l_values,  p_source => l_resp_clob);  l_item_count := apex_json.get_count(p_path => 'items', p_values => l_values);  dbms_output.put_line('Item count: ' || l_item_count);  dbms_output.put_line('-----------looping over all items------------------');  for i in 1 .. l_item_count loop    dbms_output.put_line('Item no: ' || i);    l_js_deptno := apex_json.get_number(    p_values => l_values,    p_path => 'items[' || i || '].deptno');    l_js_dname := apex_json.get_varchar2(    p_values => l_values,    p_path => 'items[' || i || '].dname');    l_js_loc := apex_json.get_varchar2(    p_values => l_values,    p_path => 'items[' || i || '].loc');    dbms_output.put_line('Dept no: ' || l_js_deptno || ', Dname: ' || l_js_dname || ', Loc: ' || l_js_loc);  end loop;end;

    Please, modify your code accordingly.

    Soukaina IDRISSISoukaina IDRISSI
«13

Answers

  • Mike Kutz
    Mike Kutz Member Posts: 5,828 Silver Crown
    edited Feb 15, 2018 12:27PM

    What are you going to do with the results?

    Are all the variables for the "same column"?

    Or is the result the equivalent to "one row with 10 columns"?

    MK

  • Soukaina IDRISSI
    Soukaina IDRISSI Member Posts: 848 Blue Ribbon
    edited Feb 15, 2018 12:31PM

    Thank you for reply,
    Actually,my result is the equivalent to one row with 10 columns

  • Mike Kutz
    Mike Kutz Member Posts: 5,828 Silver Crown
    edited Feb 15, 2018 1:20PM

    Calling a REST service from a SELECT statement for each row in the SELECT statement is a good way to get yourself BLACKLISTED.

    Quick setup for using it in PL/SQL

    create or replacepackage test_pkgas  -- make this match your data  type rest_result_t is record (    col1  varchar2(10),    col2  varchar2(6),    col3  int );      function getInfoUser(id IN NUMBER) return rest_result_t;end;/create or replacepackage body test_pkgas      function getInfoUser(id IN NUMBER) return rest_result_t  as    l_buffer rest_result_t;  begin    -- make call to REST here        -- extract the results here    l_buffer.col1 := 'Hello';    l_buffer.col2 := 'World';    l_buffer.col3 := id;        return l_buffer;  end;end;/-- example usageset serveroutput on;declare  l_buffer  test_pkg.rest_result_t;begin  l_buffer := test_pkg.getInfoUser(round( dbms_random.value(1,10)) );    dbms_output.put_line( 'First Name = ' || l_buffer.col1 );  dbms_output.put_line( 'Last Name = ' || l_buffer.col2 );  dbms_output.put_line( 'Person''s ID =' || l_buffer.col3 );end;/
  • Tom Nagle
    Tom Nagle Member Posts: 35
    edited Feb 15, 2018 4:36PM

    Hi Soukaina,

    Sorry, I am don't quite understand your question; do you need help extracting the data from the structure (XML or JSON) returned by your REST call so that you can assign the values to variables?

    -Tom

  • Pavel_p
    Pavel_p Member Posts: 2,311 Gold Trophy
    edited Feb 16, 2018 5:52AM

    Hi,

    I think with the level of detail you provided so far the best possible advice you could be given is "do it right".

    If you want to get more reasonable responses that could be eventually helpful, you'll need to reveal some more secrets of that "particular service" and ideally your getInfoUser procedure as well.

    Regards,

    Pavel

  • Soukaina IDRISSI
    Soukaina IDRISSI Member Posts: 848 Blue Ribbon
    edited Feb 16, 2018 5:54AM

    Hi Nagle,

    Yes its exactly what i need to do.

  • Soukaina IDRISSI
    Soukaina IDRISSI Member Posts: 848 Blue Ribbon
    edited Feb 16, 2018 6:17AM

    Hello,

    To be more clearfull, i have a restservice with the method "GET" with 1 input and 10 output, its about customers details.

    So to consum this restservice i need to call it with plsql procedure, and inside this procedure i would store the output into variables.

  • Pavel_p
    Pavel_p Member Posts: 2,311 Gold Trophy
    edited Feb 16, 2018 8:10AM

    Unfortunately it's still not clear at all. The service can expect the parameter as a name-value pair (my_url?param_name=param_value), in URL (my_url/param_value), it can even take the parameter in the request header (and also other request headers might be needed). It can return your ten output values in one XML/JSON object, in the response body and even in response headers. So either you'll need to provide some more details about the service, or you can't be helped.

  • Tom Nagle
    Tom Nagle Member Posts: 35
    edited Feb 16, 2018 8:20AM

    Ok, that's a start, but not quite enough. You will get your best answer by:

    1. Providing an example of the data package returned by the GET. You can always substitute dummy data for real data, but getting the structure and data types correct is essential. You can use cURL to do this or something like Postman (https://www.getpostman.com/). Postman is a little more involved, but once you understand it, it's great for tinkering around with services.

    2. If the web service you are using is public, and it doesn't violate a confidentiality agreement to do so, post a link to its API documentation and the specific endpoint you are using. For example, here is a link to the Stripe API documentation: https://stripe.com/docs/api. And, here is a link to the "Create a Plan" endpoint: https://stripe.com/docs/api#create_plan.

    Edit to add the following:

    Have you successfully accessed the web service from PL/SQL? Or, do you need to know, from the beginning, how to make a REST call and handle the result? I think the assumption by everyone so far is that you are already able to call the service but are not sure how to parse the XML or JSON.

    -Tom

  • InoL
    InoL Member Posts: 9,351 Gold Crown
    edited Feb 16, 2018 8:46AM

    This article explains how to consume a REST service in Apex (I assume to want to consume the service in an Apex application, since this is the Apex forum):

    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/apex/r50/Restful%20Services/restful_services.html#section3

    Of course it depends on what is returned exactly. In the example the output is in csv format.

This discussion has been closed.