Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
How can i get the return variables of GET method in PL/SQL procedure

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.
Best 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.
Answers
-
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
-
Thank you for reply,
Actually,my result is the equivalent to one row with 10 columns -
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;/
-
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
-
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
-
Hi Nagle,
Yes its exactly what i need to do.
-
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.
-
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.
-
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
-
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):
Of course it depends on what is returned exactly. In the example the output is in csv format.