char for Date value passing from Java

Anyone had idea on how to fix this issue?

Note: simulation step is at the last section of this topic.

Currently I receiving exception ORA-01877: string is too long for internal buffer at this line of code at plsql program

v_old_val := TO_CHAR (p_val);

p_val is declared as TIMESTAMP, value is passed from trigger when the table has new record inserted or updated from a java application.

After having some debugging, I found out this issue happened only when below conditions are met:

application insert record into that table with date under year 1978

timezone at client side is Asia/Shanghai

timezone in app server and db server is Asia/Singapore

Sample Data captured from application log:

client side >> Thu Jul 27 00:00:00 CST 1978 (User key in date)

server side >> Wed Jul 26 23:30:00 SGT 1978 (Date to be insert into db)

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: I had tried to commented out the line that causing the issue and commit the data into a working table. But when I try to query for the inserted data by using Toad(Version 8.6.1.0), Toad showing the same error and crashed.

SELECT TO_CHAR(birth_dt) FROM working_table

ORA-01877: string is too long for internal buffer

Call Stack

---

SIMULATION

I had found out a way to simulate this issue.

CREATE   TABLE birthday(
  KEY             NUMBER(12)                    NOT NULL,
  birth_dt        TIMESTAMP(6)
)

Sample Java Program

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;
    }

}

and then run this in db then you will get the issue

select * from birhtday

链接地址: http://www.djcxy.com/p/954.html

上一篇: Hibernate标准查询以匹配所有子集合元素

下一篇: 从Java传递日期值的字符