Thursday, February 7, 2008

Flexing Oracle

Attempting to create a Data Access Descriptor so that I can access pl/sql via a web browser, or other simple http access. This will come in handy if/when I need to access XML data when using another UI framework (such as Adobe Flex)
First, login as SYS into my 11g database and run the following:

BEGIN
DBMS_EPG.create_dad (
dad_name => 'dataAccess',
path => '/dataAccess/*');
END;
/

BEGIN
DBMS_EPG.authorize_dad (
dad_name => 'dataAccess',
user => '<databaseuser>');
END;
/

Then I create a simple test procedure in my schema

CREATE OR REPLACE get_test_xml_p
AS
lvc_cust_id VARCHAR2(10) := '1';
BEGIN
htp.p('<?xml version="1.0" ?>
<CUSTOMERS>
<CUSTOMER>
<ID>'||
lvc_cust_id||
'</ID>
<NAME>'||
'John Doe||
'</NAME>
</CUSTOMER>
</CUSTOMERS>');
END GET_TEST_XML_P;
/

I can alter that to grab dynamic data later. Meanwhile, test access to that procedure in my favourite browser.

http://<machinename>:<port>/dataAccess/<databaseuser>.get_test_xml_p

It brings the expected results, so let's go from there...
In Adobe Flex, I can then create a request to call the procedure:

...
<mx:Application xmlns:mx="http://www.adobe.com/2006/mxml" layout="absolute" width="682" height="426" creationComplete="getTestXML.send()" >
<mx:HTTPService
id="getTestXML"
url="http://<machinename>:8080/dataAccess/<databaseuser>.get_test_xml_p"
useProxy="false" />
...
<mx:TextInput width="100" id="custID" text="{getTestXML.lastResult.CUSTOMERS.CUSTOMER.ID}"/>
<mx:TextInput id="custName" text="{getTestXML.lastResult.CUSTOMERS.CUSTOMER.NAME}"/>
...


And, hey presto, the data is returned into the expected fields...
Interesting points to note:
1. The XML nodes are case sensitive, so when you are retrieving data, make sure you code it correctly.
getTestXML.lastResult.CUSTOMERS.CUSTOMER.ID is not equal to getTestXML.lastResult.customers.customer.id
2. You will be prompted for an XDB login, which equates to the login. I am fairly sure this can be setup programmatically somewhere along the line (and can of course be added as a parameter to the Data Access Descriptor through EnterpriseManager. Putting the username and password into the URL does not seem to want to work.

2 comments:

marco.gralike said...

Have a look at "anonymous" access via XMLDB (and or you also will find it easily when googling on "apex" + "anonymous").

There are easier ways via XMLDB to access the plsql gateway and or use a better one (especially in Oracle 11g). Have a look at XMLDB Native Database Web Service (aka NDWS; btw this is C based...)

Otherwise have a look at my personal post querying "listener" / sqlnet / apex stuff. ;-)

Patrick Wolf said...

Hi Marc,

Oracle11g also offers the possibility to execute XQuery statement through a standard web-service. Or you can also publish your PL/SQL packages as web-service. That might save additional programming. See Using Native Oracle XML DB Web Services

Greetings
Patrick