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

I'm trying to write an Oracle stored procedure to return multiple rows. The catch is the stored procedure needs to be exposed as a web service and match a pre-defined WSDL. I'm using Jdeveloper to build the web service, but its generated WSDL doesn't match my defined one and so far my ideas to return the set of data to the service aren't supported by Jdeveloper (REF CURSOR, and I created a TYPE TABLE of RECORDS defined in the package). Anyway, I'd like to avoid any schema changes if possible, but if its necessary it might be a possibilty.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Are you familiar with table functions.
  FROM TABLE ( pxportal.fgetmatches ( 'HORSERACE', 271473750, 'ENGLISH-USA' ) );

any sql client can give the SELECT statement just like any other select statement. The parameters are passed in to the fgetmatches function and the function returns a table of objects which the FROM TABLE function then exposes as a normal SELECT statement result.

SujithData ArchitectCommented:
You can use a pipelined function instead of the procedure. Pipelined functions can be queried directly as if querying data from a table. It needs two TYPEs to be created in your schema.
See the example below.
SQL> drop type tbl_typ
  2  /
Type dropped.
SQL> drop type t1
  2  /
Type dropped.
SQL> create type t1 as object(
  2  eno number,
  3  name varchar2(40),
  4  dname varchar2(40))
  5  /
Type created.
SQL> create type tbl_typ as table of t1
  2  /
Type created.
SQL> create or replace function test_func
  2  return tbl_typ
  3  pipelined
  4  as
  5  begin
  6   for rec in ( select empno, ename, dname
  7                from emp, dept
  8                where emp.deptno = dept.deptno ) loop
  9    pipe row(t1(rec.empno, rec.ename, rec.dname));
 10   end loop;
 11  end;
 12  /
Function created.
SQL> select * 
  2  from table(test_func)
  3  /
       ENO NAME                           DNAME
---------- ------------------------------ ---------------------
      7369 SMITH                          RESEARCH
      7499 ALLEN                          SALES
      7521 WARD                           SALES
      7566 JONES                          RESEARCH
      7654 MARTIN                         SALES
      7698 BLAKE                          SALES
      7782 CLARK                          ACCOUNTING
      7788 SCOTT                          RESEARCH
      7839 KING                           ACCOUNTING
      7844 TURNER                         SALES
      7876 ADAMS                          RESEARCH
      7900 JAMES                          SALES
      7902 FORD                           RESEARCH
      7934 MILLER                         ACCOUNTING
14 rows selected.

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stevengmoreauAuthor Commented:
Your solution is very close to what I think I'm looking for, however since I'm trying to actually call this PL/SQL from a web service, I get the following error when attempting to do so:

PLS-00653: aggregate/table functions are not allowed in PL/SQL scope

I read that you can only call PIPELINED functions from SQL, any further suggestions?
SujithData ArchitectCommented:
>> call this PL/SQL from a web service,
How is the call made from web service, show the piece of code.
Have you created a function as I have shown in the example and tested it from sqlplus?
stevengmoreauAuthor Commented:
Asking me to show the piece of code on how to expose a PL/SQL function as a web service was a complete waste of time. I had already indicated I was using JDeveloper to expose the packaged code. However, your suggestion did get me started on a train of thought to start to solve the problem.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.