Thursday, December 27, 2007

11g database installation and native webservices

Downloaded and installed an 11g database on a spare Windows XP machine that was floating around, while our local DBA finishes up installing our real playground on a Solaris box.

The first thing I noticed was the size of the download - Whoa! 1.8 Gb for the 11g database setup, expanding out to 2.9 Gb for the real installed database. Compare this to the download size of a 10g setup which was a smidgeon under 700Mb, expanding out to 1.6 Gb for a final installed database. I don't know what is included in the new features to take up that much more space, but *insert expletive here*, it seems to have taken quite a leap.

Once the Windows installation completed (after re-starting a couple of times due to a very invasive piece of currently installed software on the machine that had taken over the JVM - thanks Mercury!), I ensured the standard SCOTT/TIGER schema was there (Ah, EMP and DEPT, will I never forget thee), and then used the Enterprise Manager to setup a new schema. Oh, and I notice that the EM address is now accessed over https - good to see, although I kept getting certificate errors - probably an IE setting gone awry. Yes, yes, I should be using Firefox, move on...

One of the driving forces behind getting the database up and running was to test out a new capability of 11g - native Web Services for database PL/SQL code.
Ok, so new schema gets the XDB permissions granted (XDB_WEBSERVICES, XDB_WEBSERVICES_OVER_HTTP, XDB_WEBSERVICES_WITH PUBLIC) which are required for various methods of accessing web services, I assume. Then a new package is created. Something simple first - GET_TEXT_F: a function that returns some text; should we use the tried and tested "Hello World", or try something new and exciting?

create or replace
function get_text_f return varchar2 is
begin
return ('Marc was here');
end;

Spectacular! So, in theory with all of that in place, we should be able to call that little function as a web service. First, determine the WSDL:

http://<myuser>:<mypassword>@<mymachine>:<myport>/orawsv/<MYSCHEMA>/GET_TEXT_F?wsdl

Have to ensure schema and function name are in UPPERCASE! Also had a bit of a stumble with the first run, because the port for webservices had not been set! To fix that, it can simply be set manually, although I'm not sure if you are restricted to certain ports (topic for investigation later...).

execute dbms_xdb.setHttpPort(8080);

Trying the wsdl retrieval again after that is set gives a nice auto-generated WSDL document based on the function.

Use SOAP UI (www.soapui.org) to do some quick tests of the simple function (you must also remember to enter your database credentials in the SOAP request attributes) before trying something a bit more meaty!

Complex data types.
OK, say I have a database table called customers, structured something like

CREATE TABLE customers (
ID NUMBER(10),
NAME VARCHAR2(50),
DOB DATE);

Fairly simple, yes? Could I expect a table ROWTYPE return to be automatically translated by the native 11g webservice to WSDL without any extra coding? Lets see....

create or replace function get_customer_f (p_customer_id in number) return customers%ROWTYPE is
lvt_customer_rec customers%ROWTYPE;
begin
select *
into lvt_customer_rec
from customers
where customer_id = p_customer_id;
return (lvt_customer_rec);
end;

Yes, I know, I don't have any exception handling, move on...
Meanwhile, lets test to see what the WSDL comes out as

http://<myuser>:<mypassword>@<mymachine>:<myport>/orawsv/<MYSCHEMA>/GET_CUSTOMER_F?wsdl

Bzzzzzzzzzzzz, you basically get a 'Error processing input' error.
What we need to do is transform the return data into a native database data type, which means you are going to have to create TYPEs based on what your functions are going to accept and return. So, in this instance, we would need to create a customer_type

CREATE OR REPLACE TYPE CUSTOMER_TYPE AS OBJECT (
ID number(10),
NAME varchar2(50)
)

then, we need to change our function to cope with the new type:

create or replace function get_customer_f (p_customer_id in number) return customer_type is
lvt_customer_type customer_type;
begin
lvt_customer_type := customer_type(null,null);
select id, name
into lvt_customer_type.id, lvt_customer_type.name
from customers
where customer_id = p_customer_id;
return (lvt_customer_type);
end;

Now, since the database can trace the CUSTOMER_TYPE back down to primitive database types, the WSDL can be auto-generated. I would like to see if Oracle could manage to make this just a little bit easier for us kids who don't want to go around making up database types left, right and center.
Time for a post to mix.oracle.com
That's all for now...

4 comments:

Anonymous said...

What if i want the function to return a collection type? CREATE OR REPLACE TYPE MY_COLLECTION AS TABLE OF INT INDEX BY INT won't do :(

Marc Thompson said...

Unfortunately, you are correct.
For some reason, TABLEs of Objects do not feed through as web service outputs.
However, there is a way around this limitation, and that is to encapsulate your TABLE inside another Object.
For instance:

CREATE OR REPLACE TYPE CUSTOMER_TYPE AS OBJECT (
ID number(10),
NAME varchar2(50)
)
/
CREATE OR REPLACE TYPE cust_tab AS TABLE OF customer_type
/
CREATE OR REPLACE TYPE cust_tab_rec AS OBJECT (
cust_list cust_tab)
/

create or replace function get_emps3_f return cust_tab is
custs cust_tab;
begin
/* NOTE: This will not work as a webservice */
SELECT customer_type(empno, ename)
BULK COLLECT INTO custs
FROM emp;
return custs;
end;
/
create or replace function get_emps2_f return cust_tab_rec is
custs cust_tab := cust_tab();
lvt_rec cust_Tab_rec;
begin
/* NOTE: This WILL work as a webservice */
select customer_type(empno, ename)
BULK COLLECT INTO custs
FROM emp;
lvt_rec := cust_tab_rec(custs);
return lvt_rec;
end;
/

Anonymous said...

I like your example. I would like to pass in a "cust_tab_rec" to the Function, and then insert the values into a table. Can you help with the syntax as to how i would extract the id and name from a "cust_tab_rec" object that is passed into the function?

Marc Thompson said...

Sure.
For the object types created as per my comment above, the following procedure
would insert the entries into the 'CUSTOMERS' table, if it existed.

PROCEDURE insert_custs_p(p_cust_tab_rec IN cust_tab_rec)
IS
BEGIN
IF p_cust_tab_rec.cust_list.COUNT > 0 THEN -- check for entries
FOR i in 1..p_cust_tab_rec.cust_list.COUNT
LOOP
INSERT INTO customers
(cust_id,
cust_name)
VALUES
(p_cust_tab_rec.cust_list(i).ID,
p_cust_tab_rec.cust_list(i).NAME);
END LOOP;
END IF;
END;
/