oracle笔记2

来源：互联网发布：英伟达游戏优化扫描编辑：程序博客网时间：2024/06/12 21:14

--------oracle加载java包，创建java存储过程

http://www.cnblogs.com/Mayvar/archive/2011/03/30/wanghonghua_201103300200.html

----------oracle加载jar包失败：信息如下。次原因是oracle10g的默认j2re(即JRE)是1.4的，我写的这个jar包所使用的JRE是1.7的所以oracle的loadjava命令会失败。需要将使用

Myeclipse6.5创建java工程的时候使用j2se<1.4版本的。

F:\>loadjava -u sys/tiger@orcl -v -resolve EncDecryption.jar
arguments: '-u' 'sys/tiger@orcl' '-v' '-resolve' 'EncDecryption.jar'
identical: META-INF/MANIFEST.MF
identical: com/autonavi/CEncrptor
identical: .classpath
identical: .project
skipping : resource META-INF/MANIFEST.MF
resolving: class com/autonavi/CEncrptor
errors : class com/autonavi/CEncrptor
ORA-29521: 引用名称java/lang/StringBuilder无法找到
skipping : resource .classpath
skipping : resource .project
The following operations failed
class com/autonavi/CEncrptor: resolution
exiting : Failures occurred during processing

--------oracle调用webservice

oracle的utl_dbws包我试了一下，使用中会有很多问题，所以抛弃了。（但后来经过改进可使用，具体参见webservice笔记）

改用oracle调用java---然后-->java再调用webservice这条路。

http://www.docin.com/p-57405269.html（可以不用soap/axis《apache的axis是soap的升级版》进行访问webservice）。

------------使用servlet做一个http服务，使用oracle进行调用，如下：

如果想让oracle通过通过utl_http带一句简单的url中带有参数的方式来进行访问服务，然后就能获取到返回值，则服务端可以使用java的servlet,调用如下：

-------------------server端：

先使用myeclipse6.5构建一个web工程，然后在此web工程上添加一个servlet，其中CEncrptor类在此不可公布，这个servlet具体代码如下：

package com.autonavi;import java.io.IOException;import java.io.PrintWriter;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;public class decrptor extends HttpServlet {/** * Constructor of the object. */public decrptor() {super();}/** * Destruction of the servlet. <br> */public void destroy() {super.destroy(); // Just puts "destroy" string in log// Put your code here}/** * The doGet method of the servlet. <br> * * This method is called when a form has its tag value method equals to get. *  * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {/*response.setContentType("text/html");PrintWriter out = response.getWriter();out.println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">");out.println("<HTML>");out.println("  <HEAD><TITLE>A Servlet</TITLE></HEAD>");out.println("  <BODY>");out.print("    This is ");out.print(this.getClass());out.println(", using the GET method");out.println("  </BODY>");out.println("</HTML>");out.flush();out.close();*/doPost(request,response);}/** * The doPost method of the servlet. <br> * * This method is called when a form has its tag value method equals to post. *  * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {/*response.setContentType("text/html");PrintWriter out = response.getWriter();out.println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">");out.println("<HTML>");out.println("  <HEAD><TITLE>A Servlet</TITLE></HEAD>");out.println("  <BODY>");out.print("    This is ");out.print(this.getClass());out.println(", using the POST method");out.println("  </BODY>");out.println("</HTML>");out.flush();out.close();*/request.setCharacterEncoding("GBK");String RESULT=null;String xcoord = request.getParameter("xcoord").toString();String ycoord = request.getParameter("ycoord").toString();RESULT=CEncrptor.getDecrptor(xcoord)+","+CEncrptor.getDecrptor(ycoord);response.setContentType("text/html;charset=GBK"); PrintWriter out = response.getWriter();out.print(RESULT);out.flush();out.close();}/** * Initialization of the servlet. <br> * * @throws ServletException if an error occurs */public void init() throws ServletException {// Put your code here}}

--------------oracle客户端client：

FUNCTION ENCRYPTION(i_x_coord IN NUMBER,i_y_coord IN NUMBER) RETURN json AS    url           VARCHAR2(2000);    req           utl_http.req;    resp          utl_http.resp;    returnValue   VARCHAR2(400);    obj           json;  BEGIN    url:='http://' || '10.2.10.43:8000' || '/?opt=Secret2Public' || CHR(38) || 'x=' || i_x_coord || CHR(38) || 'y=' || i_y_coord;    req:= utl_http.begin_request(url);    resp:= utl_http.get_response(req);    utl_http.READ_TEXT(resp,returnValue);    utl_http.end_response(resp);    obj := json(returnValue);    RETURN obj;    EXCEPTION      WHEN utl_http.end_of_body THEN      utl_http.end_response(resp);      raise_application_error( coordinate_conversion_error,'encryption error of pint:( ' || i_x_coord || ',' || i_y_coord || ').', TRUE );      WHEN OTHERS THEN      utl_http.end_response(resp);      raise_application_error( coordinate_conversion_error,'encryption error of pint:( ' || i_x_coord || ',' || i_y_coord || ').', TRUE );  end ENCRYPTION;

----------具体如何在oracle10G中调用java存储过程，然后java存储过程再去调用webservice如下：

1：了解WSDL：http://www.ibm.com/developerworks/cn/webservices/ws-soapacc/

2：构建webservice：http://blog.csdn.net/kenshenz/article/details/6533177（我使用的是这个）或者http://www.cnblogs.com/hellojava/archive/2012/12/05/2803531.html

3：查看构建好webservice后myeclipse6.5自动生成的wsdl文件，先开启tomcat6，然后点击下图红色标记处，如下：

然后会出现下方所示（下图：我是将窗体扩展了的，方便展示）。。。。。。。。。。注意：在下方的“Status”这一栏中可能出现的只是一个返回值5（即2+3得到的。），要想看到soap报文消息，则需要点击“Status”一栏的右上角的“Form”才行（具体可以双击下图，然后查看完整图像即可）。

然后，如上图所示，出现的soap的Request和Response消息结构出来了。在调用的时候直接使用即可。

4：在oracle中写javasource包如下：

create or replace and compile java source named test asimport java.io.*;import java.net.*;  public class test  {      public static String caService() throws Exception  {         String SOAPUrl      = "http://10.2.28.31:9090/WebServiceProject/CalculatorPort?WSDL";         // Create the connection where we're going to send the file.         URL url = new URL(SOAPUrl);         URLConnection connection = url.openConnection();         HttpURLConnection httpConn = (HttpURLConnection) connection;         // Open the input file. After we copy it to a byte array, we can see         // how big it is so that we can set the HTTP Cotent-Length         // property. (See complete e-mail below for more on this.)         StringBuffer soapMessage = new StringBuffer();         //soapMessage.append("<?xml version=\"1.0\" encoding=\"utf-8\"?>");         soapMessage.append("<soapenv:Envelope xmlns:soapenv=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:q0=\"http://ws.myeclipseide.com/\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">");         soapMessage.append("<soapenv:Body>");         soapMessage.append("<q0:add>");         soapMessage.append("<arg0>2</arg0> ");         soapMessage.append("<arg1>3</arg1>");         soapMessage.append("</q0:add>");         soapMessage.append("</soapenv:Body>");         soapMessage.append("</soapenv:Envelope>");         byte[] b = soapMessage.toString().getBytes();         // Set the appropriate HTTP parameters.         httpConn.setRequestProperty( "Content-Length", String.valueOf(b.length) );         httpConn.setRequestProperty("Content-Type","text/xml; charset=utf-8");         //httpConn.setRequestProperty("SOAPAction","add");         httpConn.setRequestMethod( "POST" );         httpConn.setDoOutput(true);         httpConn.setDoInput(true);         // Everything's set up; send the XML that was read in to b.         OutputStream out=null;         out = httpConn.getOutputStream();         out.write( b );             out.close();         try{         // Read the response and write it to standard out.         InputStreamReader isr =             new InputStreamReader(httpConn.getInputStream());         BufferedReader in = new BufferedReader(isr);         String inputLine,inputLine2;         while ((inputLine = in.readLine()) != null)               break;         in.close();         return inputLine;}         catch(Exception e){return "no-getOutputStream:"+e.getMessage();}     } }

5：创建oracle函数，进行调用此存储过程，如下：

CREATE OR REPLACE FUNCTION teyy RETURN VARCHAR2 AS    LANGUAGE JAVA NAME 'test.caService() return java.lang.String';

6：调用oracle函数，展示webservice的结果：

  select teyy() from dual

调用结果如下：

<?xml version="1.0" ?><S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/"><S:Body>  <ns2:addResponse xmlns:ns2="http://ws.myeclipseide.com/">    <return>5</return>  </ns2:addResponse></S:Body></S:Envelope>

--------------如果想使用socket进行和oracle通信进行数据传递也可以，如下：

--------服务器端代码：（C#作为服务端）

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Net;using System.Net.Sockets;using System.Threading;using System.IO;namespace TCPServer{    class Program    {        static void Main(string[] args)        {            string strline = "", dir = "";            if (!File.Exists("cfg.txt"))            {                dir = Environment.CurrentDirectory+"\\dunk";                Directory.CreateDirectory(dir);            }            else            {                StreamReader sr = new StreamReader("cfg.txt");                while ("" != (strline = sr.ReadLine().Trim()))                {                    string[] arr = strline.Split(new char[] { ',' });                    string tag = arr[0].Trim().ToUpper();                    if ("PATH" == tag)                    {                        dir = tag;                        if (!Directory.Exists(dir))                        {                            continue;                        }                        break;                    }                }                sr.Close();                if (!Directory.Exists(dir))                {                    Console.WriteLine(dir + "输出目录不存在");                    return;                }            }            IPAddress local = IPAddress.Any;            IPEndPoint iep = new IPEndPoint(local, 13000);            Socket server = new Socket(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp);            server.Bind(iep);            server.Listen(20);            while (true)            {                Socket client = server.Accept();                ClientThread newClient = new ClientThread(client,dir);                Thread newThread = new Thread(new ThreadStart(newClient.ClientService));                newThread.Start();            }        }    }    class ClientThread    {        public Socket client = null;        public string dir = null;        int i;        public ClientThread(Socket k, string d)        {            client = k;            dir = d;        }        public void ClientService()        {            string data = null;            byte[] bytes = new byte[1024];            char[] ches = new char[1024];            Console.WriteLine("新用户的连接IP:{0}", (client.RemoteEndPoint as IPEndPoint).Address.ToString());             try            {                /*NetworkStream ns = new NetworkStream(client);                //Encoding gbk = Encoding.GetEncoding("GBK");                Encoding gbk = Encoding.GetEncoding("utf-8");                StreamReader sr = new StreamReader(ns, gbk);                data = sr.ReadLine();                bytes = System.Text.Encoding.GetEncoding("utf-8").GetBytes("服务器端已经收到数据:");                client.Send(bytes);*/                string path = string.Format("{0}\\{1}.xml",dir,DateTime.Now.ToString("yyyyMMddhhmmssfff"));                using (StreamWriter sw = new StreamWriter(path, true, Encoding.UTF8))                {                    while ((i = client.Receive(bytes)) != 0)                    {                        data = Encoding.GetEncoding("utf-8").GetString(bytes);                        sw.Write(data);                        if (data.IndexOf("<EOF>") > -1)                        {                            bytes = System.Text.Encoding.GetEncoding("utf-8").GetBytes("1");                            client.Send(bytes);                            break;                        }                    }                    sw.Flush();                    sw.Close();                }                                /*while ((i = client.Receive(bytes)) != 0)                {d                    //将bytes写入到本地文件中                    data=Encoding.GetEncoding("utf-8").GetString(bytes);                    //data = System.Text.Encoding.ASCII.GetString(bytes, 0, i);                    Console.WriteLine("收到客户端数据:{0}", data);                    bytes = System.Text.Encoding.GetEncoding("utf-8").GetBytes("服务器端已经收到数据:");                    client.Send(bytes);                }*/            }             catch (System.Exception exp)            {                Console.WriteLine(exp.ToString());            }            Console.WriteLine("用户IP:{0}断开连接", (client.RemoteEndPoint as IPEndPoint).Address.ToString());            client.Close();        }    } }

-------客户端代码：（oracle进行访问）

DECLARE  c  utl_tcp.connection;  -- TCP/IP connection to the Web server  ret_val pls_integer;   len integer:=0;  ix2 integer;  response varchar2(4000);  param1 varchar2(300);BEGIN  c := utl_tcp.open_connection(remote_host => '10.2.11.96',                               remote_port =>  13000,                               charset => 'AL32UTF8');  -- open connection      /*ix2:=utl_tcp.write_raw(c,                          utl_raw.cast_to_raw(convert('Subject:' ||                                                      'Lot有效期过期提示' ||                                                      utl_tcp.CRLF,                                                      'ZHS16GBK')));*/      --param1:=CONVERT('的说法发', 'ZHS16GBK', 'AL32UTF8');      /*param1:=convert('大哥哦了kluayf','ZHS16GBK');      len := utl_tcp.write_line(c,param1);*/      --len := utl_tcp.write_line(c,'的说法发');      --ix2:=utl_tcp.write_text(c,'的说法发',length('的说法发'));      len := utl_tcp.write_line(c,'打算干啥贡嘎温柔');      len := utl_tcp.write_line(c,'打算干啥贡嘎温柔');      len := utl_tcp.write_line(c,'打算干啥贡嘎温柔<EOF>');      response:=utl_tcp.get_line(c);      dbms_output.put_line(response);      utl_tcp.close_connection(c);END;

-------其次还有其他oracle调用webservice的方法：参见文章“webservice笔记”。