oracle char_used,ORA-01877：将oracle to_char用于从Java传递的Date值时的异常

有人知道如何解决这个问题吗？

Note: 模拟步骤位于本主题的最后一部分 .

目前我在plsql程序的这行代码中收到异常 ORA-01877: string is too long for internal buffer

v_old_val := TO_CHAR (p_val);

p_val声明为TIMESTAMP，当表从Java应用程序插入或更新新记录时，将从触发器传递值 .

经过一些调试后，我发现只有在满足以下条件时才会发生此问题：

应用程序使用 date under year 1978 将记录插入该表

客户端的

时区是 Asia/Shanghai

app server和db server中的

时区是 Asia/Singapore

应用程序日志中捕获的示例数据

客户端>> Thu Jul 27 00:00:00 CST 1978(用户密钥在日期)

服务器端>> Wed Jul 26 23:30:00 SGT 1978(日期插入数据库)

PLSQL function

PROCEDURE writelog_t (

p_val IN TIMESTAMP

)

IS

BEGIN

v_old_val := TO_CHAR (p_val);

END

Table

CREATE TABLE birthday(

KEY NUMBER(12) NOT NULL,

birth_dt TIMESTAMP(6)

)

Trigger

CREATE OR REPLACE TRIGGER "BIRTHDAY_TRG"

AFTER DELETE OR INSERT OR UPDATE

ON BIRTHDAY

REFERENCING NEW AS NEW OLD AS OLD

FOR EACH ROW

BEGIN

IF NOT DELETING THEN

plsql_prod.Writelog_t(:NEW.BIRTH_DT);

END IF;

END

Update: 我曾试图将导致问题的行注释掉并将数据提交到工作表中 . 但是当我尝试使用Toad(版本8.6.1.0)查询插入的数据时，Toad显示相同的错误并崩溃 .

SELECT TO_CHAR(birth_dt) FROM working_table

SIMULATION

我找到了一种模拟这个问题的方法 .

CREATE TABLE birthday(

KEY NUMBER(12) NOT NULL,

birth_dt TIMESTAMP(6)

)

示例Java程序

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.sql.Statement;

import java.sql.Timestamp;

import java.text.SimpleDateFormat;

import java.util.Calendar;

import java.util.Date;

import java.util.GregorianCalendar;

import java.util.TimeZone;

public class TimeZoneTestCase {

private static final String DATE_FORMAT = "dd-M-yyyy hh:mm:ss a";

public static void main(String[] args) {

String dateInString = "27-07-1978 12:00:00 AM";

SimpleDateFormat formatter = new SimpleDateFormat(DATE_FORMAT);

try {

// connection is the data source we used to fetch the data from

Connection connection = establishConnection();

Statement statement = connection.createStatement();

PreparedStatement updateTotal = connection

.prepareStatement("INSERT INTO birthday (key, birth_dt) VALUES (0,?)");

statement

.executeUpdate("INSERT INTO birthday (key, birth_dt) VALUES (0, TIMESTAMP '1978-07-27 00:00:00 Asia/Singapore')");

TimeZone.setDefault(TimeZone.getTimeZone("Asia/Shanghai"));

Date date = formatter.parse(dateInString);

System.out.println("Date (CST) : " + date);

TimeZone.setDefault(TimeZone.getTimeZone("Asia/Singapore"));

Calendar calendar = new GregorianCalendar();

calendar.setTime(date);

System.out.println("Date (SGT) : " + calendar.getTime());

updateTotal.setTimestamp(1, new Timestamp(calendar.getTimeInMillis()));

updateTotal.executeUpdate();

updateTotal.close();

connection.close();

} catch (Exception e) {

e.printStackTrace();

}

}

public static Connection establishConnection() {

Connection conn = null;

try {

Class.forName("oracle.jdbc.OracleDriver");

String db2URL = "Please fill in your db url here";

String userName = "Please fill in your db login username";

String password = "Please fill in your db login password";

conn = DriverManager.getConnection(db2URL, userName, password);

conn.setAutoCommit(false);

} catch (SQLException e) {

e.printStackTrace();

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

return conn;

}

}

然后在db中运行它然后你会得到问题

select * from birhtday