gPLSQLWeb服务（精品）.pdf

Introduction to PL SQL Introduction to PL SQL Web ServicesWeb Services James Hudson Wisconsin Department of Natural Resources ODTUG Kaleidoscope 2008 Hudson 6 1 2008 2 2 Web ServicesWeb Services A software system designed to support A software system designed to support interoperable machine to machine interoperable machine to machine interaction over a network interaction over a network W3C W3C definitiondefinition StandardsStandards based using XML HTTP SOAP based using XML HTTP SOAP etc to make it relatively easyetc to make it relatively easy Basis for ServiceBasis for Service Oriented ArchitectureOriented Architecture ODTUG Kaleidoscope 2008 Hudson 6 1 2008 3 3 Database Web ServicesDatabase Web Services Can do Web Services using Most PlatformsCan do Web Services using Most Platforms Including PL SQLIncluding PL SQL Publish PL SQL Functions as Web ServicesPublish PL SQL Functions as Web Services Use Use consumeconsume other Web Services in other Web Services in your PL SQL programsyour PL SQL programs ODTUG Kaleidoscope 2008 Hudson 6 1 2008 4 4 Why Database Web Services Why Database Web Services Reduced ComplexityReduced Complexity Thick Database PhilosophyThick Database Philosophy Improved Performance in some cases Improved Performance in some cases Better ControlBetter Control ODTUG Kaleidoscope 2008 Hudson 6 1 2008 5 5 Why Not Why Not Security exposes database server to web Security exposes database server to web service usersservice users Limitations in the ToolsLimitations in the Tools REST not directly supportedREST not directly supported WSWS I etc missing nowI etc missing now Too much codingToo much coding No automatic stub generation yetNo automatic stub generation yet ODTUG Kaleidoscope 2008 Hudson 6 1 2008 6 6 What Tools To UseWhat Tools To Use Oracle 11gOracle 11g orawsvorawsvservletservlet for publishingfor publishing UTL DBWS and UTL HTTP for consumingUTL DBWS and UTL HTTP for consuming Oracle 9i and 10gOracle 9i and 10g UTL DBWS and UTL HTTP with some setup UTL DBWS and UTL HTTP with some setup JpublisherJpublisher Our focus 11gOur focus 11g ODTUG Kaleidoscope 2008 Hudson 6 1 2008 7 7 Database SetupDatabase Setup Installing Installing orawsvorawsv Enabling HTTPEnabling HTTP Setting up SSL HTTPS Setting up SSL HTTPS Roles and Access ControlRoles and Access Control TestingTesting ODTUG Kaleidoscope 2008 Hudson 6 1 2008 8 8 Enabling Enabling orawsvorawsv DECLARE SERVLET NAME VARCHAR2 32 DECLARE SERVLET NAME VARCHAR2 32 orawsvorawsv BEGINBEGIN DBMS XDB deleteServletMapping SERVLET NAMEDBMS XDB deleteServletMapping SERVLET NAME DBMS XDB deleteServlet SERVLET NAMEDBMS XDB deleteServlet SERVLET NAME DBMS XDB addServlet NAMEDBMS XDB addServlet NAME SERVLET NAME SERVLET NAME LANGUAGE C LANGUAGE C DISPNAME Oracle Query Web Service DISPNAME Oracle Query Web Service DESCRIPT DESCRIPT ServletServlet for issuing queries as a Web Service for issuing queries as a Web Service SCHEMA XDB SCHEMA XDB DBMS XDB addServletSecRole SERVNAMEDBMS XDB addServletSecRole SERVNAME SERVLET NAME SERVLET NAME ROLENAME XDB WEBSERVICES ROLENAME XDB WEBSERVICES ROLELINK XDB WEBSERVICES ROLELINK XDB WEBSERVICES DBMS XDB addServletMapping PATTERNDBMS XDB addServletMapping PATTERN orawsvorawsv NAME SERVLET NAME NAME SERVLET NAME END END ODTUG Kaleidoscope 2008 Hudson 6 1 2008 9 9 Enabling HTTPEnabling HTTP ODTUG Kaleidoscope 2008 Hudson 6 1 2008 1010 Enabling HTTPSEnabling HTTPS Same Administration Same Administration exceptexcept Requires Advanced SecurityRequires Advanced Security SeparatelySeparately priced optionpriced option DBA will administer security certificates and DBA will administer security certificates and Oracle WalletOracle Wallet ODTUG Kaleidoscope 2008 Hudson 6 1 2008 1111 Security for Web ServicesSecurity for Web Services Traditional security for publishing Traditional security for publishing database web servicesdatabase web services Consumer of those web services must connect Consumer of those web services must connect to databaseto database Database user must have execute on the Database user must have execute on the function to be consumedfunction to be consumed OrawsvOrawsv requires requires basicbasic authenticationauthentication ODTUG Kaleidoscope 2008 Hudson 6 1 2008 1212 Security for Web ServicesSecurity for Web Services RolesRoles To connect to the database and invoke web To connect to the database and invoke web services must have XDB WEBSERVICES roleservices must have XDB WEBSERVICES role If over http also need If over http also need XDB WEBSERVICES OVER HTTPXDB WEBSERVICES OVER HTTP If need to see public objects not explicitly If need to see public objects not explicitly granted to the user granted to the user XDB WEBSERVICES WITH PUBLICXDB WEBSERVICES WITH PUBLIC ODTUG Kaleidoscope 2008 Hudson 6 1 2008 1313 Security for Web ServicesSecurity for Web Services The New One Access Control ListsThe New One Access Control Lists Control what Control what URIsURIs database users can seedatabase users can see Affect UTL MAIL UTL HTTP etc Affect UTL MAIL UTL HTTP etc Example enable SCOTT to see web Example enable SCOTT to see web services at http localhost 8080 services at http localhost 8080 ODTUG Kaleidoscope 2008 Hudson 6 1 2008 1414 Building an ACLBuilding an ACL dbms network acl admin create acldbms network acl admin create acl aclacl webservices xmlwebservices xml description Local description Local WebservicesWebservices principal CONNECT principal CONNECT is grantis grant TRUE TRUE privilege connect privilege connect dbms network acl admin add privilegedbms network acl admin add privilege aclacl webservices xmlwebservices xml principal principal SCOTT SCOTT is grantis grant TRUE TRUE privilege privilege connect connect dbms network acl admin assign acldbms network acl admin assign acl aclacl webservices xmlwebservices xml host host localhostlocalhost ODTUG Kaleidoscope 2008 Hudson 6 1 2008 1515 Database SetupDatabase Setup Installing Installing orawsvorawsv Enabling HTTPEnabling HTTP Setting up SSL HTTPS Setting up SSL HTTPS Roles and Access ControlRoles and Access Control TestingTesting ODTUG Kaleidoscope 2008 Hudson 6 1 2008 1616 Building a Web ServiceBuilding a Web Service Test Test orawsvorawsv Create your PL SQLCreate your PL SQL create or replace PACKAGE PK HR WEBSERVICE AScreate or replace PACKAGE PK HR WEBSERVICE AS function function f get idf get id i last namei last name in varchar2 return number in varchar2 return number function function f change salaryf change salary id in number amount in number id in number amount in number return varchar2 return varchar2 function function f get listf get list dept in number return dept in number return XMLTypeXMLType END PK HR WEBSERVICE END PK HR WEBSERVICE Test your PL SQL of course Test your PL SQL of course ODTUG Kaleidoscope 2008 Hudson 6 1 2008 1717 Test ExternallyTest Externally Can a standard SOAP tool get to the Can a standard SOAP tool get to the service service SOAP UI for Windows SOAP UI for Windows www soapui orgwww soapui org Create a projectCreate a project Set up to do basic authenticationSet up to do basic authentication For each function enter a test and see the For each function enter a test and see the resultsresults ODTUG Kaleidoscope 2008 Hudson 6 1 2008 1818 SOAP UI SetupSOAP UI Setup ODTUG Kaleidoscope 2008 Hudson 6 1 2008 1919 SOAP UI ExampleSOAP UI Example ODTUG Kaleidoscope 2008 Hudson 6 1 2008 2020 Test All FunctionsTest All Functions F change salaryF change salary salary for 203 Susan salary for 203 Susan MavrisMavris changed from 6500 to 7500changed from 6500 to 7500 F get listF get list 108108 NancyNancy GreenbergGreenberg NGREENBENGREENBE 515 124 4569515 124 4569 1717 AUGAUG 9494 FI MGRFI MGR Etc Etc ODTUG Kaleidoscope 2008 Hudson 6 1 2008 2121 Publishing PL SQL WorksPublishing PL SQL Works Be careful what you wish for do you Be careful what you wish for do you really want to change salary via a web really want to change salary via a web service service But the process is easyBut the process is easy Create your PL SQLCreate your PL SQL Grant it to a userGrant it to a user Allow web service connections to that userAllow web service connections to that user Connect to the service from anywhere using Connect to the service from anywhere using basic authenticationbasic authentication ODTUG Kaleidoscope 2008 Hudson 6 1 2008 2222 Consuming Web ServicesConsuming Web Services Two PL SQL ModelsTwo PL SQL Models UTL DBWS Easy but limitedUTL DBWS Easy but limited UTL HTTP More functional but more UTL HTTP More functional but more work work Both should require Both should require ACLsACLs in 11gin 11g Or use Apex which hides some of this Or use Apex which hides some of this but is beyond the scope here but is beyond the scope here ODTUG Kaleidoscope 2008 Hudson 6 1 2008 2323 UTL DBWSUTL DBWS Create a Create a serviceservice Create a Create a callcall Invoke the callInvoke the call Release the callRelease the call Release the serviceRelease the service Return the results as an XML fragmentReturn the results as an XML fragment ODTUG Kaleidoscope 2008 Hudson 6 1 2008 2424 Web Services StubWeb Services Stub Code that looks at a WSDL and generates Code that looks at a WSDL and generates the logic necessary to consume the the logic necessary to consume the servicesservices UTL DBWS does this but not automated UTL DBWS does this but not automated write pieces yourselfwrite pieces yourself ExampleExample ODTUG Kaleidoscope 2008 Hudson 6 1 2008 2525 Stub Using UTL DBWSStub Using UTL DBWS l servicel service UTL DBWS create serviceUTL DBWS create service wsdl document locationwsdl document location URIFACTORY getURI l wsdl urlURIFACTORY getURI l wsdl url service nameservice name l service qnamel service qname l calll call UTL DBWS create callUTL DBWS create call service handleservice handle l servicel service port nameport name l port qnamel port qname operation nameoperation name l operation qnamel operation qname l input params 0 ANYDATA ConvertVarchar2 p zipcode l input params 0 ANYDATA ConvertVarchar2 p zipcode l resultl result UTL DBWS invokeUTL DBWS invoke call handlecall handle l calll call input paramsinput params l input paramsl input params UTL DBWS release callUTL DBWS release call call handlecall handle l calll call UTL DBWS release serviceUTL DBWS release service service handleservice handle l servicel service RETURNRETURN ANYDATA AccessVarchar2 l result ANYDATA AccessVarchar2 l result ODTUG Kaleidoscope 2008 Hudson 6 1 2008 2626 Consuming Your Own ServiceConsuming Your Own Service UTL DBWS failsUTL DBWS fails Can not do basic authentication yetCan not do basic authentication yet Use UTL HTTP insteadUse UTL HTTP instead Build your own SOAP messageBuild your own SOAP message ODTUG Kaleidoscope 2008 Hudson 6 1 2008 2727 UTL HTTP Code Page 1UTL HTTP Code Page 1 soap requestsoap request xml version 1 0 encoding UTF 8 pk I LAST NAME IN i namei name pk I LAST NAME IN ODTUG Kaleidoscope 2008 Hudson 6 1 2008 2828 UTL HTTP Code Page 2UTL HTTP Code Page 2 http reqhttp req utl http begin requestutl http begin request http jim 8080 orawsv HR PK HR WEBSERVICE F GET ID http jim 8080 orawsv HR PK HR WEBSERVICE F GET ID POST POST HTTP 1 1 HTTP 1 1 UTL HTTP SET AUTHENTICATION http reqUTL HTTP SET AUTHENTICATION http req SCOTT tiger SCOTT tiger Use HTTP Basic Use HTTP Basic AuthenAuthen Scheme Scheme ODTUG Kaleidoscope 2008 Hudson 6 1 2008 2929 UTL HTTP Code Page 3UTL HTTP Code Page 3 utl http set header http requtl http set header http req Content Content Type text xml Type text xml since we are dealing with plain text in XML documentssince we are dealing with plain text in XML documents utl http set header http requtl http set header http req Content Content Length Length length soap requestlength soap request utl http set header http requtl http set header http req SOAPActionSOAPAction required to specify this is a SOAP communicationrequired to specify this is a SOAP communication utl http write text http requtl http write text http req soap requestsoap request http resphttp resp utl http get response http requtl http get response http req utl http read text http resputl http read text http resp soap respondsoap respond utl http end response http resputl http end response http resp respresp XMLType createXML soap respondXMLType createXML soap respond return return respresp ODTUG Kaleidoscope 2008 Hudson 6 1 2008 3030 ConclusionsConclusions Within a databa