データベース認識Webサービス

Size: px

Start display at page:

Download "データベース認識Webサービス"

れんかおおかわち
5 years ago
Views:

1 Olivier Le Diouris, Oracle Corporation PL/SQL PL/SQL SOAP SOAP SOAP Web Java Java SOAP Perl Perl PL/SQL SOAP PL/SQL 1. URL 2. SOAP

2 JSR 109 J2EE JSR 109 J2EE J2EE PL/SQL Java 2 3 JPublisher PL/SQL Java JPublisher Thin Java Java Java 100% VBScript PL/SQL create or replace package greetings as function sayhello(name in varchar2) return varchar2; function saygoodbye(name in varchar2) return varchar2; function gettime return varchar2; end greetings; / show errors create or replace package body greetings as function sayhello(name in varchar2) return varchar2 is val varchar2(48); begin val := 'Hello ' name '!'; return val; end sayhello; function saygoodbye(name in varchar2) return varchar2 is val varchar2(48); begin val := 'Bye-bye ' name '!'; return val; end saygoodbye; function gettime return varchar2 is begin return to_char(sysdate, 'dd-mon-yyyy hh24:mi:ss'); end gettime; 2

3 end greetings; / show errors Oracle JDeveloper 3 VB Dim x, h rem Set the values in the following three lines endpoint = " PLSQLGreetings" WSName = "PLSQLGreetings" methodname = "sayhello" rem Set x = CreateObject("MSXML2.DOMDocument") s = "<s:envelope xmlns:s=" + chr(34) + " + chr(34) s = s + " xmlns:xsi=" + chr(34) + " + chr(34) s = s + " xmlns:xsd=" + chr(34) + " + chr(34) + "> " s = s + " <s:body> " s = s + " <ns1:" + methodname + " xmlns:ns1=" + chr(34) + WSName + chr(34) s = s + " s:encodingstyle=" + chr(34) + " + chr(34) + ">" rem Make sure type and value are set correctly here s = s + " <name xsi:type=" + chr(34) + "xsd:string" + chr(34) + ">Oracle Database</name>" s = s + " </ns1:" + methodname + ">" s = s + " </s:body>" s = s + "</s:envelope>" x.loadxml s msgbox x.xml,, "Input SOAP Message" Set h = CreateObject("Microsoft.XMLHTTP") h.open "POST", endpoint h.send(x) msgbox "In progress...",,"click OK to go on" while h.readystate <> 4 wend msgbox h.responsetext,,"output SOAP Message" Microsoft Windows

4 XML Oracle9i Database Release 2 (9.2.0) Java PL/SQL Java PL/SQL PL/SQL SQL Java Oracle JDeveloper 4

5 1. 2. Java 3. Java Stored Procedure Deployment Profile 4. SOAP Client WSDL JDeveloper package wsclient; public class TemperatureWS public static String gettemp(string zip, String proxyhost, String proxyport) String ret = "Don't know"; try if (proxyhost!= null && proxyhost.length() > 0) System.setProperty("http.useProxy", "true"); System.setProperty("http.proxyHost", proxyhost); System.setProperty("http.proxyPort", proxyport); else System.setProperty("http.useProxy", "false"); TemperatureServiceStub stub = new TemperatureServiceStub(); // Add your own code here. ret = stub.gettemp(zip).tostring(); catch(exception ex) ret = ex.tostring(); return ret; 5

6 1 off Installing the SOAP Stack in the ========================================= rem rem If required, modify the 4 next lines: set JDEV_LOCATION=D: JDev set WIRELESS_LOCATION=D: iaswv20 set SYS_PASSWD=manager set SID=ORCL rem set SOAP_JAR=%JDEV_LOCATION% soap lib soap.jar set DMS_JAR=%WIRELESS_LOCATION% lib dms.jar set JAVAX_SSL=%JDEV_LOCATION% jlib javax-ssl-1_1.jar set SERVLET_JAR=%JDEV_LOCATION% j2ee home lib servlet.jar set MAIL_JAR=%WIRELESS_LOCATION% jdk jre lib ext mail.jar Executing loadjava call loadjava -thin -user sys/%sys_passwd%@localhost:1521:%sid% -resolve -synonym - verbose -grant public %SOAP_JAR% %DMS_JAR% %JAVAX_SSL% %SERVLET_JAR% %MAIL_JAR% :: :sqlplus sqlplus "sys/%sys_passwd% as pause :finish endlocal loadjava 5 jar SYS PUBLIC SQL*Plus grant.sql 1 execute dbms_java.grant_permission( 'SCOTT', 'SYS:java.net.SocketPermission', 'localhost:80', 'connect,resolve'); 6

7 SCOTT localhost:80 TCP PropertyPermission accept machine prompt 'host:port to grant > ' accept user2grant prompt 'for user > ' prompt Granting access on &machine to &user2grant... execute dbms_java.grant_permission(upper('&user2grant'), 'SYS:java.net.SocketPermission', '&machine', 'connect,resolve'); execute dbms_java.grant_permission(upper('&user2grant'), 'SYS:java.util.PropertyPermission', '*', 'read,write'); execute dbms_java.grant_permission(upper('&user2grant'), 'SYS:java.lang.RuntimePermission', 'getclassloader', '' ); prompt [Hit return] pause quit SYS SQL*Plus username machine:port 7

8 SCOTT 2 CREATE OR REPLACE FUNCTION gettemp (zip IN VARCHAR2, host IN VARCHAR2, port IN VARCHAR2) RETURN VARCHAR2 AUTHID CURRENT_USER AS LANGUAGE JAVA NAME 'wsclient.temperaturews.gettemp (java.lang.string, java.lang.string, java.lang.string) return java.lang.string'; set serveroutput on declare zip varchar2(5) := '94065'; begin dbms_output.put_line('temp at ' zip ' is ' gettemp(zip, 'www-proxy.us.oracle.com', '80') ' F.'); end; / Temp at is 61.0 F. PL/SQL procedure successfully completed. select select gettemp('94065','www-proxy.us.oracle.com','80') WSTemp from dual; WSTEMP REF-CURSOR PL/SQL SMS 8

Microsoft Word - 430_15_Developing_Stored_Procedure.doc

Microsoft Word - 430_15_Developing_Stored_Procedure.doc Java Oracle 1998 11 Java 3 Java Web GUI Java Java Java Oracle Java Oracle8i Oracle / Oracle Java Virtual Machine VM CORBA Enterprise JavaBeans Oracle Java Java Java Oracle Oracle Java Virtual Machine Oracle