[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7456
  • Last Modified:

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.
  • 2
  • 2
1 Solution
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.

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

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?
>> 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.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now