Tuesday, September 25, 2018

Hibernate - @Lob with PostgreSQL


In Java, if you declare

@Lob
@Column(name = "Remark")
protected String Remark;

Hibernate will generate the column as Text data type.

CREATE TABLE public.sometable
(
    id integer NOT NULL DEFAULT ,
    remark text COLLATE pg_catalog."default",
 )

Everything is good so far, however, when you query the database with select statement (e.g select * from sometable), it will show



In my test case, all remark column are empty string. So, what are those numbers? These numbers are the UID of the LOB for each remark. In order to view the original text, one may need to run


select id, convert_from(loread(
      lo_open(remark::int, x'40000'::int), x'40000'::int), 'UTF-8')
  AS remark from mission


Oracle - ORACLE error 959 encountered when importing with imp

If the dump is exported with imp, you cannot use impdp and you may face issue such as IMP-00003: ORACLE error 959 encountered ORA-009...