cancel
Showing results for 
Search instead for 
Did you mean: 

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

The getClob issue

 

Problem:

 

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:

 

https://www.postgresql.org/message-id/flat/201102070001.22259.andreak%40officenet.no#201102070001.22...

 

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

 

Analysis:

 

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

https://docs.oracle.com/javadb/10.6.2.1/ref/rrefclob.html

 

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

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

 IDCOL     NUMBER(38)

 DESCRIPTION     CLOB

 

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

 

SUBSTR(DESCRIPTION,1,60)

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

<root><xyz><abcList>xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

<root><xyz><abcList>xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

<root><xyz><abcList>xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

 

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 .

 

 

connection.setAutoCommit(false);
                      

Statement stmt = connection.createStatement();
                      

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

Reader r;
                       char clobVal[];
                      

StringWriter sw;
                      

while (rs.next())
                      

{
                              

description = rs.getClob("description");
                              

r = description.getCharacterStream();

 

Solution:

 

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

 

DO

$$DECLARE

            r1 record;

            r2 record;

           clob_oid oid;

            file_name text;

       _tbl text;

       _sql text;

BEGIN

   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

IEW')

   LOOP

     _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

            LOOP

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

   END LOOP;

 

END$$;

 

==end

 

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:

https://www.postgresql.org/docs/10/static/lo-funcs.html

 

 

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)
Contributors