1、ERP封装数据成XML写入数据库服务器指定文件

--指定相关文件信息  
v\_file\_path    := '/u01/test/app/fs1/EBSapps/appl/cux/12.0.0/forms';  
vfilepath      := 'TO\_SSNC\_PATH9';  
vfilename      := 'voucher.xml';  
v\_sender       := '';  
--封装数据  
vclobpz\_result := '<?xml version=''' || '1.0' || ''' encoding=''' ||  
                                    'UTF-8' || '''?><ufinterface account=''' || 'SS' ||  
                                    ''' billtype=''' || 'vouchergl' ||  
                                    ''' businessunitcode=''' || 'develop' ||  
                                    ''' filename=''' || 'voucher.xml' || ''' groupcode=''' || 'SS' ||  
                                    ''' orgcode=''' || l\_orgcode || ''' receiver=''' ||  
                                    '0001A5100000000005F3' || ''' sender=''' || '' ||  
                                    '''><voucher><voucher\_head><pk\_voucher></pk\_voucher><pk\_vouchertype>01</pk\_vouchertype><year>' ||  
                                    l\_year ||  
                                    '</year><pk\_system>GL</pk\_system><voucherkind>0</voucherkind><pk\_accountingbook>' ||  
                                    l\_orgcode ||  
                                    '-0001</pk\_accountingbook><discardflag>N</discardflag><period>' ||  
                                    l\_period ||  
                                    '</period><no></no><attachment>1</attachment><prepareddate>' ||  
                                    to\_char(last\_day(SYSDATE) - 6,  
                                                    'YYYY-MM-DD') || '</prepareddate><pk\_prepared>' ||  
                                    l\_prepared ||  
                                    '</pk\_prepared><pk\_casher></pk\_casher><signflag>Y</signflag><pk\_checked></pk\_checked><tallydate></tallydate><pk\_manager></pk\_manager><memo1></memo1><memo2></memo2><reserve1></reserve1><reserve2>N</reserve2><siscardflag /><pk\_org>' ||  
                                    l\_orgcode || '</pk\_org><pk\_org\_v>' || l\_orgcode ||  
                                    '</pk\_org\_v><pk\_group>SS</pk\_group><details>';

--写入文件  
x\_msg\_data := NULL;  
BEGIN  
    v\_create\_file := 'A';  
    --打开文件  R读文本，W写文本  ，A附加文本  
    vmyfile       := utl\_file.fopen(vfilepath,  
                                                                    vfilename,  
                                                                    'W',  
                                                                    30000);  
    v\_create\_file := 'B';  
    --写入xml到指定文件  
    utl\_file.put\_line(vmyfile,  
                                        convert(vclobpz\_result,  
                                                        'ZHS16GBK')); --需要转换格式，否则可能会出现乱码

    v\_create\_file := 'C';  
    --关闭文本  
    utl\_file.fclose(vmyfile);  
EXCEPTION  
    WHEN OTHERS THEN  
        IF (v\_create\_file = 'A') THEN  
            x\_msg\_data := '打开XML文件异常：' || SQLERRM;  
        ELSIF (v\_create\_file = 'B') THEN  
            x\_msg\_data := '写入数据到XML文件异常：' || SQLERRM;  
        ELSIF (v\_create\_file = 'C') THEN  
            x\_msg\_data := '关闭XML文件异常：' || SQLERRM;  
        END IF;  
END;  

传入外围系统，获取返回结果  
SELECT cux\_nc\_ap\_voucher\_pkg.cux\_java\_sendfile(v\_addr,  
                                                                                             v\_file\_path,  
                                                                                             vfilename)  
INTO   v\_return  
FROM   dual;

2、读取文件，通过java流传给外围系统

create or replace and compile java source named APPS.cux_java_sendfile as
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
import java.io.Writer;
import java.io.*;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.zip.GZIPInputStream;
import java.util.zip.GZIPOutputStream;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.traversal.NodeIterator;
import javax.xml.parsers.*;
import org.w3c.dom.*;
import org.xml.sax.*;

public class cux_java_sendfile {

public static String xzSendFile(String addr,String filepath,String filename)throws IOException
{
System.out.println("Test1");
File vfile= new File(filepath+File.separator+filename);
String conn_flag1=null;
cux_java_sendfile sc = new cux_java_sendfile();
//验证是否已连接NC
System.out.println("Test2");
String conn_flag= sc.getConn_flag(addr);

if (conn_flag!="获取连接出错！")
{
System.out.println(filepath+File.separator+filename);
if (vfile.exists()){
//sc.sendMessage(vfile,false,sc.getConn(false,addr));
try{
conn_flag1= sc.receiveResponse(vfile,sc.getConn(addr));
} catch (Exception e) {

      }  
   }else{  
     return "文件不存在";  
   }  
}

return conn_flag1;
}

private boolean ret = false;

String message = "";

String fileMessage = "";
//String returnmsg ="";

/**
* 取得NC外部平台连接
*
* @param bcompress
* 是否启用压缩
* @return
* @throws IOException
*/

public HttpURLConnection getConn(String url)
throws IOException {
// 连接NC外部平台地址
try {
URL realURL = new URL(url);
HttpURLConnection connection = (HttpURLConnection) realURL
.openConnection();
connection.setDoOutput(true);
connection.setDoInput(true);
connection.setRequestProperty("Content-type", "text/xml");
connection.setRequestMethod("POST");
System.out.println("取得连接" + url);
return connection;

} catch (MalformedURLException e) {  
  System.out.println("获取连接出错！");  
  return  null;  
}  

}

public static String getConn_flag(String url)
throws IOException {
// 连接NC外部平台地址
try {

  URL realURL = new URL(url);  
  HttpURLConnection connection = (HttpURLConnection) realURL  
      .openConnection();

  connection.setDoOutput(true);  
  connection.setDoInput(true);  
  connection.setRequestProperty("Content-type", "text/xml");  
  return  "取得连接" + url;

} catch (MalformedURLException e) {
System.out.println("获取连接出错！");
//return NULL;
return "获取连接出错！";
}
}

public String receiveResponse(File file,HttpURLConnection connection//,String post,String post1
) throws Exception {

      try{ 

         String post = "";  
         System.out.println("==================BEGIN====================");  
          // 获取URLConnection对象对应的输出流

           connection.setRequestProperty("Charset", "UTF-8");  //设置编码为utf-8  
           PrintWriter printWriter = new PrintWriter(connection.getOutputStream());  
          // 发送请求参数

           BufferedInputStream input = new BufferedInputStream(  
              new FileInputStream(file));  
          int length;  
          int bufflength = 40960 \* 1;  
          byte\[\] buffer = new byte\[bufflength\];  
          while ((length = input.read(buffer)) != -1) {  
           post = new String(buffer,0,length);  
           printWriter.write(post);//post的参数 xx=xx&yy=yy

           //System.out.println(post);  
          }  
          // flush输出流的缓冲  
          printWriter.flush();  
          //开始获取数据  
          BufferedInputStream bis = new BufferedInputStream(connection.getInputStream());  
          ByteArrayOutputStream bos = new ByteArrayOutputStream();  
          int len;  
          byte\[\] arr = new byte\[1024\];  
          while((len=bis.read(arr))!= -1){  
              bos.write(arr,0,len);  
              bos.flush();  
          }

          bos.close();  
          System.out.println("=======bos======="+bos.toString("UTF-8"));

           //解析NC返回结果集的XML  
           System.out.println("解析NC返回结果集的XML");  
           Document doc = null;  
           String return\_msg = "";  
           String return\_Suc = "";  
           //从xml文档中获取DOM的解析器...  
           DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();   //这里调用DOM工厂...  
           try {   

            //从DOM工厂中获取DOM解析器...  
            DocumentBuilder db = dbf.newDocumentBuilder();

            InputSource is = new InputSource();   //定义一个xml文档的输入源..  
            is.setCharacterStream(new StringReader(bos.toString("UTF-8")));   //设置输入源的字符流...  
            doc = db.parse(is);     //指定输入源的内容解析成一个XML文档，并返回一个DOM对象...  
            System.out.println("doc:= "+ doc.getImplementation());  
            Element root =doc.getDocumentElement();  
            return\_Suc = root.getAttribute("successful");  
            System.out.println("resSuc:="+return\_Suc);

           //得到文档名称为sendresult的元素的节点列表  
             NodeList nList = doc.getElementsByTagName("sendresult");  
             for(int i = 0; i< nList.getLength() ; i ++){  
                Element node = (Element)nList.item(i);  
                return\_msg = node.getElementsByTagName("resultdescription").item(0).getFirstChild().getNodeValue();  
                //System.out.println("return\_msg:"+return\_msg);  
                //System.out.println("resultdescription: "+ node.getElementsByTagName("resultdescription").item(0).getFirstChild().getNodeValue());  
             }

            } catch (ParserConfigurationException e) {  
                e.getMessage();  
            } catch (SAXException e) {  
                e.getMessage();  
            } catch (IOException e) {  
                e.getMessage();  
            }  
            System.out.println("==================END====================");

            //System.out.println("return\_Suc1:"+return\_Suc);  
           // System.out.println("return\_msg1:"+return\_msg);  
             return "返回成功与否标记："+return\_Suc+",返回消息："+return\_msg;  
         }catch (IOException e) {  
            e.printStackTrace();  
         }  
    return "返回成功";

}

/**
*
**/
/**
*初始化一个DocumentBuilder
*@return a DocumentBuilder
*@throws ParserConfigurationException
**/
public static DocumentBuilder newDocumentBuilder()
throws ParserConfigurationException{
return newDocumentBuilderFactory().newDocumentBuilder();
}
/**
*初始化一个DocumentBuilderFactory
*@return a DocumentBuilderFactory
**/
public static DocumentBuilderFactory newDocumentBuilderFactory(){
DocumentBuilderFactory dbf=DocumentBuilderFactory.newInstance();
dbf.setNamespaceAware(true);
return dbf;
}

}

3、调用java程序 以文件流方式传输至用友NC系统指定地址,返回连接状态

FUNCTION cux\_java\_sendfile(p\_addr     VARCHAR2,  
                                                     p\_filepath IN VARCHAR2,  
                                                     p\_filename IN VARCHAR2) RETURN VARCHAR2

 AS  
    LANGUAGE JAVA NAME 'cux\_java\_sendfile.xzSendFile(java.lang.String,java.lang.String,java.lang.String) return java.lang.String';