Solved

Oracle Non-Spatial Reader 8000 character limit

  • 24 October 2019
  • 6 replies
  • 2 views
adrian_farrell
Rank
Badge +5

Hi

 

Trying to read an Oracle table containing one (Oracle limit) "Long" datatype field

 

The field only brings through the first 8000 characters into FME (these are typically upwards of 9800 characters in this DB)

 

As an aside into Access (ODBC) it reads more (8135) but not all of the field

 

Does anyone have any idea how to bypass this limit and bring in the huge field?

 

Thanks

 

Adrian

icon

Best answer by markatsafe 28 October 2019, 22:07

View original

6 replies

M
Badge +2

@adrian_farrell It seems the LONG is no longer recommended: https://stackoverflow.com/questions/3599211/oracle-long-or-clob

I would suggest trying the SQLExecutor

M
Badge +2

@adrian_farrell Try the JDBC reader in the SQLExecutor. I think this will return the LONG strings.

adrian_farrell
Rank
Badge +5

@adrian_farrell It seems the LONG is no longer recommended: https://stackoverflow.com/questions/3599211/oracle-long-or-clob

I would suggest trying the SQLExecutor

I have been finding the large number of posts dotted around the net warning that LONG datatype are very antiquated and inflexible (this is another example i guess)

 

Unfortunately i'm working with a propriety system and therefore cannot change the data structures

adrian_farrell
Rank
Badge +5

@adrian_farrell Try the JDBC reader in the SQLExecutor. I think this will return the LONG strings.

Hi Mark

Thanks for the info relating to JDBC however i cannot get it to read Oracle, each time i get the error that there is no suitable driver available. do you happen to know a way around this error please?

Thanks

adrian_farrell
Rank
Badge +5

Hi Mark

Thanks for the info relating to JDBC however i cannot get it to read Oracle, each time i get the error that there is no suitable driver available. do you happen to know a way around this error please?

Thanks

Hi again

I managed to get it to extract the data (adding the JDBC for Oracle helps!) but it still truncates the output

Adrian

adrian_farrell
Rank
Badge +5

Hi again

I managed to get it to extract the data (adding the JDBC for Oracle helps!) but it still truncates the output

Adrian

Thanks Mark, turns out the system we are using has implemented a method to self truncate the field and store it in a hidden overflow table in 8135 character segments. It was a red herring the way they represented this in the main table and the fact that they still rely heavily on the LONG data type

The JDBC reader has been a learning curve but one very worth while following, i'll make some good use of that!

Thanks again

Adrian

Reply


Community Stats

28,731
Posts
109,530
Replies
36,565
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.