Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K 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.4K 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
- 468 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
Answers
-
The result of WS looks like;
but when i parse values , i get null like Column2, and column3
-
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.
-
Thank you so so much Pavel_p for your patience and understanding .
You saved my life, it was very useful your post, it's really touching to see people who take their time to assist others .Oracle apex is the best forum in the world, for ever.