Showing results for 
Search instead for 
Did you mean: 

The getClob error : com.edb.util.PSQLException: Bad value for type long

The getClob issue




When Migration Tool Kit (MTK) migrates CLOB data from Oracle to Postgres, the data gets stored in Postgres tables as TEXT in Clob columns. The getClob() method in JDBC driver expects CLOB data to be stored as OID in Clob column and actual Clob to be stored in pg_largeobject system table. While running a java program to query the Clob data using getClob() method, if it finds TEXT data in Clob column of the table being queried, following error is thrown:


com.edb.util.PSQLException: Bad value for type long :


Here is a discussion in postgres community forum on this issue and there were recommendations to add a connection parameter to treat Clob as Text:


It doesn’t look like it has been implemented yet.




As name implies, CLOB is Character Large Object and stored in Oracle as unicode Character:


When we query CLOB column in SQL*Plus, it returns text data. 


MTK migrates CLOB form Oracle to EPAS as text data and stores in CLOB column as text data. 


Here is the source table in Oracle:


SQL> desc test_clob;

 Name    Null?    Type

 ----------------------------------------- -------- ----------------------------




SQL> select substr(description,1,60) from test_clob;








Here is the table that gets migrated in EPAS by MTK:


hrdb=# \d hr.test_clob;

       Table "hr.test_clob"

   Column    |  Type   | Modifiers 


 idcol       | numeric | 

 description | clob    | 


hrdb=# select substring(description, 1,60) from hr.test_clob;

-[ RECORD 1 ]-----------------------------------------------------------

substring | <root><xyz><abcList>xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

-[ RECORD 2 ]-----------------------------------------------------------

substring | <root><xyz><abcList>xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

-[ RECORD 3 ]-----------------------------------------------------------

substring | <root><xyz><abcList>xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



The following highlighted code in Java program returns error: com.edb.util.PSQLException: Bad value for type long .




Statement stmt = connection.createStatement();

ResultSet rs = stmt.executeQuery("select * from hr.test_clob");

Reader r;
                       char clobVal[];

StringWriter sw;

while (


description = rs.getClob("description");

r = description.getCharacterStream();




The workaround is to use getString() method instead of getClob() in your java code.


However, if it is too difficult to change to change the code and replace all getClob calls to getString, following script can be run in psql to dump all CLOB data from tables and reimport it using lo_import() so it gets stored:


== Script




            r1 record;

            r2 record;

           clob_oid oid;

            file_name text;

       _tbl text;

       _sql text;


   file_name := '/tmp/clob';

   FOR r1 IN SELECT schema_name,table_name,column_name from all_tab_columns wher

e data_type = 'CLOB'and schema_name not in ('SYS','DBO','PUBLIC') and table_name

not in (SELECT table_name FROM information_schema.tables where table_type != 'V



     _tbl := r1.schema_name||'.'||r1.table_name;

     FOR r2 IN EXECUTE 'select ctid,'||r1.column_name||' as clob_text from '||r

1.schema_name||'.'||r1.table_name FOR UPDATE


         _sql := format('COPY(select '||r1.column_name||' from '||r1.schema_nam

e||'.'||r1.table_name||' where ctid=''%s'') TO '''||file_name||'''',r2.ctid);


         RAISE NOTICE '%',_sql;

         EXECUTE _sql;

         clob_oid := lo_import(file_name);


     END LOOP;







After running the script, Clob data is replaced by OID:


localdb=# select * from test_clob;

 id | description 


  1 | 282100

  2 | 282101



The LOB data is stored in pg_largeobject table.

More information on Large Object functions can be found here:



In order to select the clob data in a query, we need to use lo_get, like below to retrieve the Clob data:


select convert_from(lo_get(<column_name>:Smiley Surprisedid),'utf8') from <table_name>;

Version history
Revision #:
1 of 1
Last update:
‎03-27-2018 11:38 AM
Updated by:
Labels (3)