- 爱易网页
-
Java教程
- java调用pl/sql表格报错,求指教啊
日期:2014-05-17 浏览次数:20787 次
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);