日期:2014-05-17  浏览次数:20763 次

java调用pl/sql报表报错,求指教啊 !
java代码如下

package com.oracle.www.produce;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Package2 {
public static void main(String args[]) {
int i = 0;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@192.168.0.210:1522:TEST", "apps",
"appstest");
Statement stmt = conn.createStatement();
ResultSet rset = stmt
.executeQuery("select BANNER from SYS.V_$VERSION ");
while (rset.next())
System.out.println(rset.getString(1)); // Print col 1
CallableStatement cs = conn
.prepareCall("{call CUX_YI_ORG_PKG.main(?,?,?,?,?)} ");
// cs.setString(1,null);
// cs.setString(2,null);
cs.registerOutParameter(1, oracle.jdbc.OracleTypes.VARCHAR);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);// 输出参数
cs.setInt(3, 83);
cs.setString(4, "2012/01/05 13:53:41");
cs.setString(5, "2012/10/05 13:53:41");

// System.out.println("i="+i);
cs.execute();
ResultSet rs = (ResultSet) cs.getObject(5);
System.out.println("rs=" + rs);
while (rs.next())
System.out.println(rs.getString(1)); // Print col 1
stmt.close();
} catch (Exception e) {
e.printStackTrace();

} finally {

}
}
}


pl/sql报表如下:::


create or replace package body CUX_YI_TEST_PKG is
 
  lerrormessage VARCHAR2(2000);
  ldebug VARCHAR2(2000);

  PROCEDURE outlog(i_chr_message IN VARCHAR2) IS
  BEGIN
  fnd_file.put_line(fnd_file.log, i_chr_message);
   
  END;

  PROCEDURE output(i_chr_message IN VARCHAR2) IS
  BEGIN
  fnd_file.put_line(fnd_file.output, i_chr_message);
  DBMS_OUTPUT.put_line(i_chr_message); --输出日志
  END;

  --函数1
  --用于返回一个varchar2类型的变量
  function get_OM_Comfirm_Date(p_line_id number) return varchar2 is
  v_OM_Comfirm_Date varchar2(20);
  begin
  select
  to_char(nvl(to_date(tp_attribute15, 'YYYY-MM-DD'),
  mmt.transaction_date),
  'YYYY-MM-DD') ship_date --发运确认时间(Shiping Date)  
  into v_OM_Comfirm_Date
  from mtl_material_transactions mmt, --事物处理表
  wsh_delivery_assignments wda, --出货作业表
  wsh_delivery_details wdd --出货明细
  where mmt.picking_line_id = wda.delivery_detail_id --对应 发运事物处理界面的"详细信息" 
  and mmt.source_line_id = wdd.source_line_id --物流行号
  and wda.delivery_detail_id = wdd.delivery_detail_id --货号编码
  --???
  and nvl(wdd.line_direction, 'O') IN ('O', 'IO')
  and NVL(wda.type, 'S') IN ('S', 'C')
  and mmt.transaction_type_id = 33 --事物处理类型(.销售发运)
  and mmt.transaction_source_type_id = 2 --事物来源类型(销售订单)
  and rownum = 1
  and mmt.source_line_id = p_line_id; --订单行line_id
  
  return v_OM_Comfirm_Date;
  exception
  when others then
  return null;
  end;
  --函数2
  function get_arrivel_date(p_date_time varchar2) return varchar2 is
  v_get_arrivel_date varchar2(10);