oracle笔记2

最新推荐文章于 2021-01-19 19:28:02 发布

weixin_30485291

最新推荐文章于 2021-01-19 19:28:02 发布

阅读量76

点赞数

文章标签：数据库 java 开发工具

原文链接：http://www.cnblogs.com/xxvv/archive/2013/04/03/3648577.html

版权

--------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 as
import 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笔记”。

转载于:https://www.cnblogs.com/xxvv/archive/2013/04/03/3648577.html

weixin_30485291

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle笔记2

--------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...
复制链接

扫一扫