hsql 2000 character download limit??

Document ID : KB000075103
Last Modified Date : 11/06/2018
Show Technical Document Details
Issue:
We are finding out that the hsql command is unable to download larger files than 2000 characters? Is this the case or perhaps we are missing some options that would make this work?
Environment:
CA Harvest SCM all versions and platforms
Oracle 11g or 12c DBMS
Cause:
I tested hsql with a table that has a 4000 character data field in HARRPTSQL, and for varchar2(4000) data fields it appears to work as it should.

However, I suspect where you’re hitting this limitation is with the BLOB data fields. I’ve seen the same thing. 

The limitation is not the “varchar2” character limit, but the “raw” character limit. Raw data type can only hold 2000 characters. Therefore, you will need to extract 2000 characters at a time and concatenate the result. 

Here’s where I found the limits for different data types: https://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#SQLRF30020 

Instead of using hsql, you might need to go directly to the database using SQL Plus or a pl/sql script. I am guessing that you’re using this to extract versions of text files from the HARVERSIONDATA table. If this is the case, you can use the HARVERSIONDATA.DATASIZE field to get the size of the file, then set up a loop to extract 2000 characters at a time and concatenate (or write) the results.
Resolution:
Instead of using hsql, you might need to go directly to the database using SQL Plus or a pl/sql script. If you are wanting to extract versions of text files from the HARVERSIONDATA table, or form type templates from the HARFORMTEMPLATE table, you can set up a loop to extract 2000 characters at a time and concatenate (or write) the results.