Solved

How do I return multiple rows from an Oracle stored procedure/function to a web service?

Posted on 2010-09-07
9
3,790 Views
Last Modified: 2013-12-19
I have successfully built an Oracle stored function which takes a single input parameter and returns a single value.  I've then used JDeveloper to create a web service to call this function and successfully got this to run through a browser.

I now need to enhance this simple function to return a record set.  Essentially the function will be passed a couple of values, which it will use to identify one or more rows in a table and I need to return these rows.  I understand there are different ways of coding an Oracle function to do this (ref cursors, arrays etc), but what I need to know is what method to use to ensure the rows are retrieved correctly when called from a web service.  For example, I don't believe ref cursors are a supported data type in a web service.

The web service itself returns its values in xml format, but how do I build the function to return the individual rows from the function such that the web service can return these rows as xml?

I'm new to JDeveloper and web services so please make things simple for me to understand ;-)

Hopefully there is a way to achieve this.
0
Comment
Question by:Milleniumaire
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 8

Expert Comment

by:POracle
ID: 33616901
You can get output of table data in xml form, from procedure.

See this:-
http://www.oracle-base.com/articles/9i/XMLSEQUENCE.phphttp://www.oracle-base.com/articles/9i/XMLSEQUENCE.php

This is just select statement so you can use this in your prosedure.

I don't know JDeveloper. but there may be object or cursor type available to hold rowset from procedure.


0
 
LVL 7

Expert Comment

by:sumit2906
ID: 33617051
you can use ref cursors, I have earlier written procedures which was used by .NET, I am not sure how they used to handle that, however you can explore further.
0
 
LVL 5

Expert Comment

by:Sanjeev Labh
ID: 33625167
Hi,

As @POracle has already mentioned you can achieve it by directly returning the result set in xml format from the procedure. XMLTYPE is a type provided by Oracle which returns XML format data type. The ways of retrieving data in XML format can be done as POracle has highlighted.

However, as you are familiar of using refcursors in Jdeveloper. So by using refcursor you need to use a third party API like CASTOR which converts java objects to XML and vice versa. So, for implementing this you need to follow the steps:
1. Return data through PLSQL procedure by refcursor.
2. Store this data from refcursor into a java object
3. Pass this java object as parameter to CASTOR (or any such) API

you will get back result in XML format. This you can send as XML back to the web service.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 16

Author Comment

by:Milleniumaire
ID: 33625205
Experts, thanks for your suggestions, however, I have managed to do this by using collections.

I define an object type, then use this to define a table type which I then return in my stored database function.

When JDeveloper is then used to create a web service wrapper around this database function it correctly handles the native collection return type and everything works perfectly without the need for any additional code.
0
 
LVL 16

Author Comment

by:Milleniumaire
ID: 33625216
Just to clarify, my database function is written in pure pl/sql, there isn't a line of java code in sight.  JDeveloper generates the necessary code to turn this pl/sql into a web service.
0
 
LVL 5

Expert Comment

by:Sanjeev Labh
ID: 33625301
ok thanks for the information. Was thinking otherwise.
0
 
LVL 8

Expert Comment

by:POracle
ID: 33625482
Thats good that you get solution.

As I always say, there are multiple way to sort out problems.

still I suggest to select some of answer as assisted solution and if possible post some information about solution you got . It will help to evaluate answer in feature by some one else who facing similar problem..
0
 
LVL 16

Accepted Solution

by:
Milleniumaire earned 0 total points
ID: 33638023
Here's an example of the pl/sql code that I wrote to enable the stored plsql package function to return multiple rows to the web service.  No special processing was required to expose this package using JDeveloper.
CREATE OR REPLACE TYPE my_rec as object (
  tablespace_name    varchar2(30),
  extent_management  varchar2(10));


CREATE OR REPLACE TYPE my_array AS TABLE OF my_rec;

CREATE OR REPLACE PACKAGE test AS
  function get_tablespaces(i_status in user_tables.status%type) return my_array;
END;
/

CREATE OR REPLACE PACKAGE BODY test AS
  function get_tablespaces(i_status in user_tables.status%type) return my_array is
    v_rtn   my_array := my_array(null);
    v_first boolean := true;

    cursor c_get_tablespaces is
      select tablespace_name,
             extent_management
      from user_tablespaces
      where status = i_status;

  begin
    
    for rec in c_get_tablespaces loop
      if v_first then
        v_first := false;
      else
        v_rtn.extend;
      end if;
    
    v_rtn(v_rtn.last) := my_rec(rec.tablespace_name,
                                rec.extent_management);
    end loop;    

    return v_rtn;
  end;
END;
/

select * from table (test.get_tablespaces('ONLINE'));

Open in new window

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Use of Exception to end a Loop 3 45
Detailed steps to upload 6 51
Field name with special character (Ñ) in Oracle 11 82
Oracle function to insert records? 15 47
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This guide will walk you through the essential considerations and tech stack for building scalable websites. Know how to grow your business the smart way!
The purpose of this video is to demonstrate how to add AdSense Ads to a WordPress Website, and how to set up WordPress to automatically place Ads in Sidebars. This will be demonstrated using a Windows 8 PC. Log into your AdSense account. : Cli…
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question