1. Run the following SQL in the classic SCOTT schema
drop type dept_type;2. Create a PLSQL Web Service called "DemoWS" using the defaults for the package WS_PACKAGE.
drop type dept_list_table;
create type dept_type as object
(deptno NUMBER,
dname VARCHAR2(14),
loc varchar2(13),
cr_date date)
/
create type dept_list_table is table of dept_type
/
purge recyclebin;
create or replace package ws_package as
function test_dept_table return dept_list_table;
end ws_package;
/
show errors;
create or replace package body ws_package as
function test_dept_table return dept_list_table is
all_depts dept_list_table := dept_list_table();
dRecType dept_type;
i number := 0;
begin
-- iterate through all depts
for r_list in (select * from dept) loop
i := i + 1;
dRecType := dept_type(null, null, null, null);
dRecType.deptno := r_list.deptno;
dRecType.dname := r_list.dname;
dRecType.loc := r_list.loc;
dRecType.cr_date := sysdate;
all_depts.extend;
all_depts(i) := dRecType;
end loop;
return all_depts;
end test_dept_table;
end ws_package;
/
show errors;
3. Right click on the Web Service "DemoWS" and select "Run". It may take a while to start but eventually you should see it's started as follows.
Run startup time: 10437 ms.
[Application WebServicesTest deployed to Server Instance DefaultServer]
Target URL -- http://localhost:7101/WebServicesTest-PLSQLTest-context-root/DemoWSPort
09/08/13 09:49:19 Web service DemoWS has been started on the embedded server
4. Copy the URL above into a browser.5. Click on "Test Page"
6. Click on the only function "testDeptTable"
7. Verify response as follows.
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
<env:Header />
<env:Body>
<m:testDeptTableResponse xmlns:m="http://au/support/ws/DemoWS.wsdl">
<result xmlns:typ="http://au/support/ws/DemoWS.wsdl/types/">
<typ:DeptTypeUser>
<typ:dname>ACCOUNTING</typ:dname>
<typ:loc>NEW YORK</typ:loc>
<typ:deptno>10</typ:deptno>
<typ:crDate>2009-08-13T08:36:53.000+10:00</typ:crDate>
</typ:DeptTypeUser>
<typ:DeptTypeUser>
<typ:dname>RESEARCH</typ:dname>
<typ:loc>DALLAS</typ:loc>
<typ:deptno>20</typ:deptno>
<typ:crDate>2009-08-13T08:36:53.000+10:00</typ:crDate>
</typ:DeptTypeUser>
<typ:DeptTypeUser>
<typ:dname>SALES</typ:dname>
<typ:loc>CHICAGO</typ:loc>
<typ:deptno>30</typ:deptno>
<typ:crDate>2009-08-13T08:36:53.000+10:00</typ:crDate>
</typ:DeptTypeUser>
<typ:DeptTypeUser>
<typ:dname>OPERATIONS</typ:dname>
<typ:loc>BOSTON</typ:loc>
<typ:deptno>40</typ:deptno>
<typ:crDate>2009-08-13T08:36:53.000+10:00</typ:crDate>
</typ:DeptTypeUser>
</result>
</m:testDeptTableResponse>
</env:Body>
</env:Envelope>
Note: If you wanted to test using the Table as an IN parameter simply create a method as follows in the PLSQL package which would enable you to test this as well.
function echo_dept_list_table (v1 in dept_list_table) return dept_list_table is
begin
return v1;
end echo_dept_list_table;
3 comments:
Hi there,
although not really on the topic of your post I could not help notice how you built up your nested table of objects and remembered a similar procedure I had written using the bulk collect feature of pl/sql.
I would have coded the function as follows - this approach would be better for performance and maintance;
function test_dept_table2
return dept_list_table
is
all_depts dept_list_table;
begin
select dept_type(deptno, dname, loc, sysdate)
bulk collect into all_depts
from dept;
return all_depts;
end test_dept_table2;
Yeah no doubt if your working with more then 4 DEPT records and for maintainability that's the way to go. In fact if your dealing with large tables ideally you should be limiting the amount of rows you return as well if possible using the LIMIT clause. That would only work if you can return a certain amount of records at a time.
So basically use a CURSOR as follows:
Eg:
open c_client;
fetch c_client bulk collect
into clientRet limit 1000;
close c_client;
Pas,
Excellent post. Your demo is very clean and you explained the process well. Thanks for sharing.
Post a Comment