1

I am trying to extract content from cells with type LONG from an old Oracle database (version 9.2). Trouble is, some of those cells have content that is over 30,000 characters and get truncated upon select. My DBA skills are lacking, so I have two questions:

  1. Is it possible for content larger than 30,000 characters (32kb?) to be inserted into a cell of type LONG? If the answer is no, disregard my second question as this would mean the data must have been truncated upon insertion and is now corrupted.

  2. If it is possible to insert more than 32kb, then the question is how do I read back all of it? I have tried to convert the LONG into a CLOB as I have read the limit on CLOBs is higher. I tried a few things, one of which was to create a temp table in Oracle and populate it with the data from the LONG cells converted to CLOB using the TO_LOB function:

Firstly, I created the temp table and insert some test cells I know are over 32kb:

CREATE GLOBAL TEMPORARY TABLE temp_table(
time TIMESTAMP WITH LOCAL TIME ZONE,
text CLOB
)
ON COMMIT DELETE ROWS;


INSERT INTO temp_table (
 time ,
 text
) 
SELECT 
sysdate ,
TO_LOB(DOC_CONTENT)

FROM DOCUMENTS 

WHERE DOC_ID = '123456'
AND ROWNUM <= 1;

I then read out the length of the resulting CLOB and always get 30,000

SELECT 
 gt.text
INTO
 v_clob
FROM temp_table gt;

dbms_output.put_line('Size: '||dbms_lob.getlength(v_clob));

COMMIT;

END;

So even though I try to convert the LONGs into CLOBs, the content is still truncated to 30,000 characters. Is there another way to get the 'whole' content out of a LONG, or has it been lost when it was initially inserted? Thanks

Community
  • 1
  • 1
SoMuchNas
  • 121
  • 1
  • 4
  • 1
    http://stackoverflow.com/questions/5430055/how-to-find-length-of-a-long-column-in-a-table may give you information about the length of you long column – Rene May 14 '14 at 12:59
  • [`LONG` can hold up to 2GB](http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements001.htm#SQLRF30020) but is deprecated; CLOB can be much bigger. Converting isn't avoiding a 32KB limit. If your code is reporting the `LONG` and `CLOB` length as 30,000 then it looks like it must have been truncated on insert, unfortunately. Do you know how it was inserted - was it held in or treated as a `varchar2` temporarily, which would explain the 32KB as that's the maximum size for that data type in PL/SQL? – Alex Poole May 14 '14 at 13:46
  • Thank you all for your replies. I tried many things only to find out the initial DB owner inserted these 'doc contents' with 30k character limit on each but split them into several rows when the doc was larger than 30k. All I had to do was 'stitch' them back together. No idea why they had to limit it to 30k. Resolved anyway. – SoMuchNas May 28 '14 at 14:36

0 Answers0