ORA-01877：将oracle to_char用于从Java传递的Date值时的异常-Java 学习之路

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

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

ORA-01877: string is too long for internal buffer

Call Stack

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

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

相关问题