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